On Fri, Mar 12, 2010 at 5:54 PM, jrs <j...@framemedia.com> wrote:
> I'm trying, without a great deal of success, to perform a simple self
> join using the django orm. I'm pretty sure the F() function is not
> the answer. I believe this only allows you to compare two field
> within the same model instance, not to compare fields within 2
> instances of the same model. I want to do this -
>
> Model-
>
> class Account(models.Model):
> name = models.CharField(max_length=255)
> parent_account = models.ForeignKey('self', default=None, blank=True)
>
First, you are going to need a null=True on that parent_account field.
Without it an attempt to save an Account with parent_account=None is going
to raise an IntegrityError.
> SQL-
>
> SELECT account.*, parent.*
> FROM account
> LEFT JOIN account parent ON
> account.id = parent.parent_account
>
>
What are you trying to accomplish with this SQL? Given an account table
with:
mysql> select * from account;
+----+------+-------------------+
| id | name | parent_account_id |
+----+------+-------------------+
| 1 | A1 | 1 |
| 2 | A2 | NULL |
| 3 | A3 | 2 |
| 4 | A4 | 2 |
+----+------+-------------------+
4 rows in set (0.00 sec)
The query you show returns:
mysql> select account.*, parent.* from account left join account parent on
account.id = parent.parent_account_id;
+----+------+-------------------+------+------+-------------------+
| id | name | parent_account_id | id | name | parent_account_id |
+----+------+-------------------+------+------+-------------------+
| 1 | A1 | 1 | 1 | A1 | 1 |
| 2 | A2 | NULL | 3 | A3 | 2 |
| 2 | A2 | NULL | 4 | A4 | 2 |
| 3 | A3 | 2 | NULL | NULL | NULL |
| 4 | A4 | 2 | NULL | NULL | NULL |
+----+------+-------------------+------+------+-------------------+
5 rows in set (0.00 sec)
The right-side columns coming from the 'parent' table in that result would
more appropriately be labeled 'child', I think, so I'm not entirely sure
this query is doing what you wanted.
It is possible to get the Django ORM to issue a query that seems to match
the intent of how you named those tables in your query, via
Account.objects.select_related('parent_account'). You can check the sql in a
shell:
>>> from ttt.models import Account
>>> from django.db import connection
>>> Account.objects.select_related('parent_account')
[<Account: A1 (Parent: 1))>, <Account: A2 (No parent))>, <Account: A3
(Parent: 2))>, <Account: A4 (Parent: 2))>]
>>> len(connection.queries)
1
>>> connection.queries[-1]['sql']
u'SELECT `account`.`id`, `account`.`name`, `account`.`parent_account_id`,
T2.`id`, T2.`name`, T2.`parent_account_id` FROM `account` LEFT OUTER JOIN
`account` T2 ON (`account`.`parent_account_id` = T2.`id`) LIMIT 21'
>>>
The actual results of that query are:
mysql> select account.*, T2.* from account left outer join account T2 on
account.parent_account_id = T2.id;
+----+------+-------------------+------+------+-------------------+
| id | name | parent_account_id | id | name | parent_account_id |
+----+------+-------------------+------+------+-------------------+
| 1 | A1 | 1 | 1 | A1 | 1 |
| 2 | A2 | NULL | NULL | NULL | NULL |
| 3 | A3 | 2 | 2 | A2 | NULL |
| 4 | A4 | 2 | 2 | A2 | NULL |
+----+------+-------------------+------+------+-------------------+
4 rows in set (0.00 sec)
Those results seem to better match what your query was intended to produce
given how you named the alias, where the right-side columns have information
about the left-side's parent. Or were you really looking for the results of
the original query?
If you described what you were looking for in terms of the Account objects,
someone might be able to help more.
Karen
--
You received this message because you are subscribed to the Google Groups
"Django users" group.
To post to this group, send email to django-us...@googlegroups.com.
To unsubscribe from this group, send email to
django-users+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/django-users?hl=en.
