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")