python - query - IZQUIERDA ÚNETE a Django ORM

group by django

Question

Tengo los siguientes modelos:

class Volunteer(models.Model):
first_name = models.CharField(max_length=50L)
last_name = models.CharField(max_length=50L)
email = models.CharField(max_length=50L)
gender = models.CharField(max_length=1, choices=GENDER_CHOICES)
class Department(models.Model):
name = models.CharField(max_length=50L, unique=True)
overseer = models.ForeignKey(Volunteer, blank=True, null=True)
location = models.CharField(max_length=100L, null=True)
class DepartmentVolunteer(models.Model):
volunteer = models.ForeignKey(Volunteer)
department = models.ForeignKey(Department)
assistant = models.BooleanField(default=False)
keyman = models.BooleanField(default=False)
captain = models.BooleanField(default=False)
location = models.CharField(max_length=100L, blank=True, null=True)

Quiero consultar para todos los departamentos que no tienen voluntarios asignados a ellos. Puedo hacerlo usando la siguiente consulta:

SELECT
vsp_department.name
FROM
vsp_department
LEFT JOIN vsp_departmentvolunteer ON vsp_department.id = vsp_departmentvolunteer.department_id
WHERE
vsp_departmentvolunteer.department_id IS NULL;

¿Hay una manera más similar a django de hacer esto o debería ir con raw sql?

Answer 1

Esto parece estar funcionando:

Department.objects.filter(departmentvolunteer__department__isnull=True)

Ver documentos para más detalles.

Answer 2

Puede hacer esto siguiendo la relación hacia atrás en la búsqueda.

>>> qs = Department.objects.filter(departmentvolunteer__isnull=True).values_list(''name'', flat=True)
>>> print(qs.query)
SELECT "app_department"."name" FROM "app_department" LEFT OUTER JOIN
"app_departmentvolunteer" ON ( "app_department"."id" = "app_departmentvolunteer"."department_id" )
WHERE "app_epartmentvolunteer"."id" IS NULL

Aquí están los documentos sobre consultas "que abarcan relaciones multivaluadas": https://docs.djangoproject.com/en/stable/topics/db/queries/#spanning-multi-valued-relationships

Answer 3

Para mí fueron necesarios modelos de combinación personalizada, que tienen campos implícitos
me funciona en django 1.9.
pero parece más en la muleta
Si alguien tiene una solución más elegante, por favor comparte para las personas

from django.db.models.sql.datastructures import Join
from django.db.models.fields.related import ForeignObject
from django.db.models.options import Options
from myapp.models import Ace
from myapp.models import Subject
jf = ForeignObject(
to=Subject,
on_delete=lambda: x,
from_fields=[None],
to_fields=[None],
rel=None,
related_name=None
)
jf.opts = Options(Ace._meta)
jf.opts.model = Ace
jf.get_joining_columns = lambda: (("subj", "name"),)
j=Join(
Subject._meta.db_table, Ace._meta.db_table,
''T1'', "LEFT JOIN", jf, True)
q=Ace.objects.filter(version=296)
q.query.join(j)
print q.query

resultado:

SELECT
`ace`.`id`,
`ace`.`version_id`,
`ace`.`obj`,
`ace`.`subj`,
`ace`.`ACE_Type`,
`ace`.`ACE_Inheritance`,
`ace`.`ACE_Rights`
FROM `ace`
LEFT OUTER JOIN `core_subject`
ON (`ace`.`subj` = `core_subject`.`name`)
WHERE `ace`.`version_id` = 296

aquí ejemplo de uso con condición adicional y establecer alias de tabla (pero parece muleta)

def join_to(self, table1, table2, field1, field2, queryset, alias=''''):
"""
table1 base
"""
# here you can set complex clause for join
def extra_join_cond(where_class, alias, related_alias):
if (alias, related_alias) == (''[sys].[columns]'',
''[sys].[database_permissions]''):
where = ''[sys].[columns].[column_id] = '' /
''[sys].[database_permissions].[minor_id]''
children = [ExtraWhere([where], ())]
wh = where_class(children)
return wh
return None
dpj = ForeignObject(
to=table2,
on_delete=lambda: None,
from_fields=[None],
to_fields=[None],
rel=None,
related_name=None
)
dpj.opts = Options(table1._meta)
dpj.opts.model = table1
dpj.get_joining_columns = lambda: ((field1, field2),)
dpj.get_extra_restriction = extra_join_cond
dj = Join(
table2._meta.db_table, table1._meta.db_table,
''T'', "LEFT JOIN", dpj, True)
ac = queryset._clone()
ac.query.join(dj)
# hook for set alias
alias and setattr(dj, ''table_alias'', alias)
return ac

Lo uso por

# how it use:
from django.db.models.expressions import Col
q = Something.objects /
.filter(type__in=["''S''", "''U''", "''G''"]) /
.exclude(name__in=("''sys''", "''INFORMATION_SCHEMA''")) /
.annotate(
... some annotation fields
class_= Col(Permissions._meta.db_table,
Permissions._meta.get_field(''field_name''),
output_field=IntegerField()),
Grant=Col(
''T10'',
Principals._meta.get_field(''name''),
output_field=CharField()),
).values(''Grant'')
ac = self.join_to(Principals, ServerPrincipals, ''sid'', ''sid'', q)
# here invoke "extra_join_cond" of function "join_to"
ac = self.join_to(Permissions, Columns, ''major_id'', ''object_id'', ac)
# here use alias table
ac = self.join_to(Permissions, Principals, ''grantor_id'', ''principal_id'', ac, ''T10'') # T10 is alias

sql''ll ser

SELECT
T10.name AS Grant
FROM sys.principals
LEFT OUTER JOIN sys.server_principals
ON (sys.principals.sid = sys.server_principals.sid)
LEFT OUTER JOIN sys.columns
ON (sys.permissions.major_id = sys.columns.object_id
AND (
(sys.columns.column_id = sys.permissions.minor_id))
)
LEFT OUTER JOIN sys.principals T10
ON (sys.permissions.grantor_id = T10.principal_id)

Answer 4

para crear fácilmente unirse por OR

def get_queryset(self):
qs = super(AceViewSet, self).get_queryset()
qs = qs.select_related(''xxx'')
# construct all tables and the join dependence
qs.query.__str__()
qs.query.alias_map[''xx_subject''].join_cols = ((''xxx_id'', ''uid''), (''xxx_id'', ''ad_subject_id''))
qs.query.alias_map[''xx_subject''].as_sql = partial(self.as_sql, qs.query.alias_map[''xx_subject''])
return qs
@staticmethod
def as_sql(self, compiler, connection):
sql, params = Join.as_sql(self, compiler, connection)
or_sql = sql.replace("AND", "OR")
return or_sql, params

FROM "xx_ace"
LEFT OUTER JOIN "xx_subject"
ON ("xx_ace"."xxx_id" = "xx_subject"."uid" OR "xx_ace"."xxx_id" = "xx_subject"."ad_subject_id")