Django, haystack, elastic search and one to many relation

Viewed 516

I have problem with haystack - I do not know how to search for models A whose all foreign keys meet given condition.

My simplified models look like:

Group:
    id
Meeting:
    group = models.ForeignKey(Group)
    day_of_week = models.IntegerField()
    hour = models.IntegerField()
    length = models.IntegerField()

So basically, a group can have many meetings and users should be able to search for those groups whose all meetings are in given time range. eg:

Group(1)
    Meeting(day_of_week=Monday, hour=9, length=2)
Group(2)
    Meeting(day_of_week=Monday, hour=10, length=1)
    Meeting(day_of_week=Tuesday, hour=8, length=2)
Group(3)
    Meeting(day_of_week=Monday, hour=10, length=1)
    Meeting(day_of_week=Wednesday, hour=12, length=1)

and search: "Monday from 8 to 11", "Tuesday, from 12 to 14 (2p.m.)", "Wednesday, from 6 to 17 (5p.m.)" should return group 1 and 3, because all meetings from those groups contains in user specified ranges and group 2 is not returned, because second meeting is not in given range (tho the first one is).

If I was to write a SQL, I would probably go for something like "select count of matching meetings and count of all meetings if those numbers are equal -> then all meetings meet:

SELECT g.id,
       count(m2.id)
FROM groups g
JOIN meetings m2 ON m2.group_id = g.id
AND ((m2.day_of_week = 0  -- monday
      AND m2.hour >= 8
      AND m2.length<=3)
     OR (m2.day_of_week=1  -- tuesday
         AND m2.hour >= 12
         AND m2.length<=2)
     OR (m2.day_of_week=2 -- wednesday
         AND m2.hour >= 6
         AND m2.length<=11))
GROUP BY g.id
HAVING count(m2.id) =
  (SELECT count(*)
   FROM meetings
   WHERE meetings.group_id=g.id);

But we are using haystack + elastic search for indexing and I have completely no idea how to flatten the model to index and write a query. Can anyone help me with it?

2 Answers
Related