Login

Left Outer join Q object

Author:
karsu
Posted:
May 31, 2007
Language:
Python
Version:
.96
Score:
10 (after 10 ratings)

QLeftOuterJoin object allows you to create 'LEFT OUTER JOIN' sql query. It is very usefull if you have to define ForeignKey to 'null=True' (select_related will not work with null=True).

You are allowed to use QLeftOuterJoin like Q object.

Example: QLeftOuterJoin('thread_last_post', Post, 'pk', Thread, 'last_post')

It will generates SQL like:

LEFT OUTER JOIN appname_post AS thread_last_post ON thread_last_post.id = appname_thread.last_post_id

Table could be model or string. 

 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. Template tag - list punctuation for a list of items by shapiromatron 10 months, 1 week ago
  2. JSONRequestMiddleware adds a .json() method to your HttpRequests by cdcarter 10 months, 2 weeks ago
  3. Serializer factory with Django Rest Framework by julio 1 year, 5 months ago
  4. Image compression before saving the new model / work with JPG, PNG by Schleidens 1 year, 6 months ago
  5. Help text hyperlinks by sa2812 1 year, 6 months 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 "<console>", line 1, in <module> 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'

#

Please login first before commenting.