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?