0

I have 2 django models:

class Foo(models.Model):
    baz = models.CharField()

class Bar(models.Model);
    foo = models.ForeignKey(Foo)
    qux = models.CharField()

with the following data:

Foo
id baz
4  X
5  Y
6  Z

Bar
id foo_id qux
1  4      A
2  5      A
3  5      B
4  6      B

now I do 2 queries on Bar, filtered on qux:

resA = [1, 2] (actually bar instances; shown bar.id for convenience)
resB = [3, 4] (actually bar instances; shown bar.id for convenience)

What is now the fastest way to AND these lists together so that the result will be:

resAND = [5] (foo.id)

Right now I do:

ret = []
nr_sets = 2
foos = Foo.objects.all()
bars = list(resA + resB)
for foo in foos:
    test = filter(lambda bar : bar.foo_id == foo.id, bars)
    if test == nr_sets;
        ret.append(foo)

This is however horribly slow. Any ideas in speeding this up? I am particularly looking for post query solutions, but good ideas concerning the query('s) are also welcome.

RickyA
  • 15,465
  • 5
  • 71
  • 95
  • "What is now the fastest way to AND these lists together so that the result will be:"? Can you explain how this result comes from those two lists? The word "AND" doesn't seem to apply. – S.Lott Jan 09 '12 at 11:44
  • Wouldn't it be simpler (and faster) to just make a new query instead of trying to combine two queries? – Some programmer dude Jan 09 '12 at 11:47

2 Answers2

2

You could try

foo_ids = set(resA.values_list('foo_id', flat=True)) & \
    set(resB.values_list('foo_id', flat=True))

Then you can get the corresponding complete Foo objects with

ret = Foo.objects.in_bulk(foo_ids).values()

Edit: used set because apparently & does not work as expected on ValueQuerySet.

Jan Pöschko
  • 5,412
  • 1
  • 28
  • 28
  • nice method, but i did not got the & operator to work between the ValuesQuerySet's. Changed that one for a tuple intersection as in this question; http://stackoverflow.com/questions/642763/python-intersection-of-two-lists – RickyA Jan 09 '12 at 13:22
  • OK, thanks - honestly I didn't try it before. Updated my answer to use `set`s. – Jan Pöschko Jan 09 '12 at 13:27
  • you cant '&' sets like that. I used: `b1 = [1,2,3,4,5] b2 = [3,4,5,6] s2 = set(b2) b3 = [val for val in b1 if val in s2]` – RickyA Jan 09 '12 at 13:31
  • hey, you can :) sorry, did not try with two sets. – RickyA Jan 09 '12 at 13:52
0

Why not:

Bar.objects.filter(foo__id=5)

You can also add other filter parameters in there, they will are AND'ed by default.

Simon Kagwi
  • 1,636
  • 2
  • 19
  • 25