help using django's ORM for UNION query

Question

i am making a history page for a website. The structure of my classes is something like this:

alt text

class Person(models.Model):
    name = models.CharField(max_length=100)
    type = models.CharField(max_length=30)

class History(models.Model):
    date = models.DateField(max_length=100)
    action = models.CharField(max_length=250)
    person = models.ForeignKey(Person)

class Parent(Person):
    #some attributes that are not relevant

class Son(Person)
    parent = models.ForeignKey(Parent)
    #other attributes that are not relevant

its quite simple... i have a Parent that has multiple Sons both can do actions on the website and they are all saved in the History table that has a reference to the Person that executed the action. The history table is something like:

date       | action        | person_id 
----------------------------------------
16-12-2010 | saved profile | 1
16-12-2010 | new child     | 2

for a Parent i need to display all his actions and the action of his sons Using sql it would be:

SELECT * FROM History where person_id=1 
UNION 
SELECT h.* FROM History h JOIN Son s ON s.person_ptr_id=h.person_id WHERE s.parent_id=1

but i have no idea how to do that using django's ORM. Myabe using two querys? a loop? Do you have any ideas? i'd really appreciate some help.. thanks in advance

BTW: i'm using django 1.1

EDIT: i added the attributes in the classes. These are just examples, my tables have more attributes, but this is how django translate the relations into tables

Can you post the source code of the models? The foreign key relations would suffice. — Manoj Govindan
– Manoj Govindan, Commented Sep 13, 2010 at 16:06
@pleasedontbelong: Are you modeling Person as an abstract class in Django? Also, are Parent and Son being modeled as separate classes? This would mean different tables. Does it make sense to model them using one class, with a foreign key pointing to the same class? — Manoj Govindan
– Manoj Govindan, Commented Sep 13, 2010 at 17:04
well this is just an exemple of the real tables that i'm using, where parent and sons have different attributes and also different relations to other tables.. so it's easier for me to separate them in different tables — pleasedontbelong
– pleasedontbelong, Commented Sep 13, 2010 at 17:25
OK. In that case you should first come out with suitable Django model classes I think. It would be easier to work out a query after that. — Manoj Govindan
– Manoj Govindan, Commented Sep 13, 2010 at 17:27
o_o .. but.. the django models are not different than the diagram, i mean, the heritance and the 1xN relations are there on my django models, and the query I need is the one I posted, but i dont know how to create the queryset.. it must be something like History.objects.filter(Q(person__id=1)|Q("history for all sons of Parent with id=1")) — pleasedontbelong
– pleasedontbelong, Commented Sep 13, 2010 at 17:47

diegueus9 · Accepted Answer · 2010-09-13 20:07:18Z

1

I think this is the sql:

p = Parent.objects.get(id=1)
history_qs = History.objects.all()
history_qs = history_qs.filter(Q(person=p)|Q(person__in=Son.objects.filter(parent=p)))

The union is not necesary.

answered Sep 13, 2010 at 20:07

diegueus9

32.1k16 gold badges65 silver badges75 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

help using django's ORM for UNION query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related