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 5 years, 11 months ago
  2. filtered ModelChoiceField queries by robharvey 5 years, 2 months ago
  3. Tastypie MongoDB Resource by fatiherikli 7 months, 2 weeks ago
  4. Friendly ID by willhardy 4 years, 5 months ago
  5. jstree integration to django admin by pawnhearts 3 years, 4 months ago

Comments

zeroK (on June 5, 2007):

Great :-)

I just have a small problem, though. For some reason if I apply this to my own code, the LEFT OUTER JOIN is executed, but for some reason non of the content from the JOINed table is actually queried :/

http://dpaste.com/hold/11692/

#

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', })

#

bsn (on August 14, 2007):

I just have necessity to add one more condition to my SQL(for example: LEFT OUTER JOIN appname_post AS thread_last_post ON thread_last_post.id = appname_thread.last_post_id AND thread_last_post.id1 = appname_thread.last_post_id1 ), so I have modified your snippet a little.

I have added one more attribute named 'advanced' for your function, where I can put RAW SQL with additional conditions.

View modified snipped on dpaste

#

dougvanhorn (on April 28, 2008):

Looks like the query set refactor (http://code.djangoproject.com/wiki/QuerysetRefactorBranch) has negated this.

This seems to work:

class Foo(Model):
    created_ts = DateTimeField(auto_now_add=True)

class Bar(Model):
    foo = ForeignKey(Foo, null=True, blank=True)

Bar.objects.select_related('foo').order_by('foo__created_ts')

#

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