Left Outer join Q object

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
class QLeftOuterJoin(models.Q):
    """
        Example:
        [alias, table to join, field, table, field]
        QLeftOuterJoin('mm_p', Post, 'pk', Category, 'last_post')
        
        class Thread(models.Model):
            category = models.ForeignKey(Category)
            subject = models.CharField(_('subject'), maxlength=255)
            last_post = models.ForeignKey("Post", editable=False, null=True, blank=True, default=None)
        
        class Post(models.Model):
            thread = models.ForeignKey(Thread, verbose_name=_('thread'))
            writer = models.ForeignKey(User, verbose_name=_('writer'), editable=False, null=True, blank=True)
            writer_verbose_name = models.CharField(_("writer's verbose name"), maxlength=255, editable=False)
            content = models.TextField(_('content'))
            created = models.DateTimeField(_('created'), editable=False)
            modified = models.DateTimeField(_('modified'), editable=False)
        
        threads = Thread.items.filter(
            Q(category=1) &
            QLeftOuterJoin('thread_last_post', Post, 'pk', Thread, 'last_post')
            ).select_related().extra(select={
                'last_post_modified': '`thread_last_post`.`modified`',
            })
    """
    def __init__(self, alias, table1, field1, table2, field2):
        if isinstance(table1, models.base.ModelBase):
            if field1 is None or field1 == 'pk':
                field1 = table1._meta.pk.name
            try:
                field1 = table1._meta.get_field(field1).column
            except AttributeError, e:
                raise AttributeError, "%s: %s" % (e, "table: %s, field: %s" % table1, field1)
            table1 = table1._meta.db_table            
        
        if isinstance(table2, models.base.ModelBase):
            if field2 is None or field2 == 'pk':
                field2 = table2._meta.pk.name
            try:
                field2 = table2._meta.get_field(field2).column
            except AttributeError, e:
                raise AttributeError, "%s: %s" % (e, "table: %s, field: %s" % table1, field1)
            table2 = table2._meta.db_table
        
        self.where = "%s.%s = %s.%s" % (alias, field1, table2, field2)
        self.alias, self.table = alias, table1
        
    def get_sql(self, opts):
        joins = SortedDict()
        joins[self.alias] = (self.table, 'LEFT OUTER JOIN', self.where)
        return (joins, [], [])

More like this

  1. QLeftOuterJoins by dottedmag 6 years, 10 months ago
  2. Get derived model instance by werehuman 6 months, 4 weeks ago
  3. CustomQueryManager by zvoase 5 years, 9 months ago
  4. Filter on Multiple M2M Objects Simultaneously by axiak 7 years ago
  5. Q marshaller by Spike^ekipS 6 years, 1 month ago

Comments

karsu (on June 13, 2007):

You have to put extra() method end of your query.

Example ..select_related().extra(select={ 'some_name1':'join_table_alias.column1', 'some_name2':'join_table_alias.column2', })

#

prajwala (on December 8, 2008):

Is this works in django 1.0

#

ebertti (on September 10, 2010):

i get this error in django 1.2.1

Class1.objects.filter(QLeftOuterJoin('ord', Class1, 'id', Class2, 'class1'))

Traceback (most recent call last): File "[HTML_REMOVED]", line 1, in [HTML_REMOVED] File "C:\Python26\lib\site-packages\django\db\models\query_utils.py", line 167, in and return self._combine(other, self.AND) File "C:\Python26\lib\site-packages\django\db\models\query_utils.py", line 160, in _combine obj.add(other, conn) File "C:\Python26\lib\site-packages\django\utils\tree.py", line 95, in add if isinstance(node, Node) and (node.connector == conn_type or AttributeError: 'QLeftOuterJoin' object has no attribute 'connector'

#

(Forgotten your password?)