UNIRSE IZQUIERDO Django ORM

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 a 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 forma más parecida a la de django de hacer esto o debería simplemente usar sql sin formato?

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 de las consultas “que abarcan relaciones de múltiples valores”: https://docs.djangoproject.com/en/stable/topics/db/queries/#spanning-multi-valued-relationships

Para mi fueron necesarios modelos de unión personalizados, que tienen campos implícitos.
me funcionó en el django 1.9.
Pero más parece en la muleta.
Si alguien tiene una solución más elegante, por favor, comparta 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 un alias de tabla (pero parece una 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

 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) 

Esto parece estar funcionando:

 Department.objects.filter(departmentvolunteer__department__isnull=True) 

Ver documentos para más detalles.

para crear una unión personalizada 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")