Django, haystack, elastic search and one-to-one relationship

Question

I have a problem with haystack - I don’t know how to look for models A whose foreign keys match the given condition .

My simplified models look like this:

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

Thus, a group can have many meetings, and users should be able to search for groups whose all meetings are in a 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 the search: “Monday from 8 to 11,” “Tuesday, from 12 to 14 (2 pm),” “Wednesday, from 6 to 17 (5 pm),” should return groups 1 and 3, because all the meetings from these groups contain in the ranges specified by the user, and group 2 is not returned because the second collection is not in the specified range (the first of them).

SQL, , , - " , → :

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);

haystack + , , . - ?

Answer 1

ElasticSearch

ElasticSearch . , ES. , .

PUT /myindex
{
  "mappings": {
    "groups": {
      "properties": {
        "meetings": {
          "type": "nested", 
          "properties": {
             "dayOfWeek": { "type": "integer"},
             "start": {"type": "integer"},
             "end": {"type": "integer"}
          }
        },
        "groupId": {"type":"integer"}
      }
    }
  }
}

POST /myindex/groups/_bulk
{"index": {}}
{"groupId": 1, "meetings": [{"dayOfWeek": 0, "start": 9,  "end": 11}]}
{"index": {}}
{"groupId": 2, "meetings": [{"dayOfWeek": 0, "start": 10, "end": 11}, { "dayOfWeek": 1, "start": 8,  "end": 10}]}
{"index": {}}
{"groupId": 3, "meetings": [{"dayOfWeek": 0, "start": 10, "end": 11}, {"dayOfWeek": 2, "start": 12, "end": 13}]}

, , .

, , , ... : , none .

GET /myindex/_search
{
  "query": {
    "bool": {
      "must_not" : {
       "nested": {
          "path": "meetings",
          "filter": {
              "bool": {
                "must_not": {
                  "bool": {
                    "should": [
                        {
                          "bool": {
                            "must": [
                              {"term" : { "dayOfWeek" : 0 }},
                              {"range": {"start": {"from":8, "to":11}}},
                              {"range": {"end": {"from":8, "to":11}}}
                            ]
                          }
                        },
                        {
                          "bool": {
                            "must": [
                              {"term" : { "dayOfWeek" : 1 }},
                              {"range": {"start": {"from":12, "to":14}}},
                              {"range": {"end": {"from":12, "to":14}}}
                            ]
                          }
                        },
                        {
                          "bool": {
                            "must": [
                              {"term" : { "dayOfWeek" : 2 }},
                              {"range": {"start": {"from":6, "to":17}}},
                              {"range": {"end": {"from":6, "to":17}}}
                            ]
                          }
                        }                        
                      ]
                    }
                }
              }
            }
          }
       }
      }
    }
  }

1 3. 2 , -.

Haystack

Django Haystack, , , ES. , , django, - .

Answer 2

, , , .

** ES 5 **

:

PUT /meetings
{
    "mappings": {
       "meeting": {
          "properties": {
             "groupId": {
                "type": "integer"
             },
             "dayOfWeek": {
                "type": "integer"
             },
             "hourRange": {
                "type": "integer_range"
             }
          }
       }
    }
}

:

POST /meetings/meeting/_bulk
{"index": {}}
{"groupId": 1, "dayOfWeek": 0, "hourRange": {"gte": 9, "lte": 11}}
{"index": {}}
{"groupId": 2, "dayOfWeek": 0, "hourRange": {"gte": 10, "lte": 11}}
{"index": {}}
{"groupId": 2, "dayOfWeek": 1, "hourRange": {"gte": 8, "lte": 10}}
{"index": {}}
{"groupId": 3, "dayOfWeek": 0, "hourRange": {"gte": 10, "lte": 11}}
{"index": {}}
{"groupId": 3, "dayOfWeek": 2, "hourRange": {"gte": 12, "lte": 13}}

, :

POST /meetings/meeting/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "dayOfWeek": 0
                }
              },
              {
                "range": {
                  "hourRange": {
                    "gte": "8",
                    "lte": "11",
                    "relation": "within"
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "dayOfWeek": 1
                }
              },
              {
                "range": {
                  "hourRange": {
                    "gte": "12",
                    "lte": "14",
                    "relation": "within"
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "dayOfWeek": 2
                }
              },
              {
                "range": {
                  "hourRange": {
                    "gte": "6",
                    "lte": "17",
                    "relation": "within"
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}

** ES < 5 **

PUT /meetings
{
    "mappings": {
       "meeting": {
          "properties": {
             "groupId": {
                "type": "integer"
             },
             "dayOfWeek": {
                "type": "integer"
             },
             "start": {
                "type": "integer"
             },
             "end": {
                "type": "integer"
             }
          }
       }
    }
}

:

POST /meetings/meeting/_bulk
{"index": {}}
{"groupId": 1, "dayOfWeek": 0, "start": 9,  "end": 11}
{"index": {}}
{"groupId": 2, "dayOfWeek": 0, "start": 10, "end": 11}
{"index": {}}
{"groupId": 2, "dayOfWeek": 1, "start": 8,  "end": 10}
{"index": {}}
{"groupId": 3, "dayOfWeek": 0, "start": 10, "end": 11}
{"index": {}}
{"groupId": 3, "dayOfWeek": 2, "start": 12, "end": 13}

, :

POST /meetings/meeting/_search
{
  "query": {
    "bool": {
      "should": [
        {
          "bool": {
            "must": [
              {
                "term": {
                  "dayOfWeek": 0
                }
              },
              {
                "range": {
                  "start": {
                    "gte": "8"
                  }
                }
              },
              {
                "range": {
                  "end": {
                    "lte": "11"
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "dayOfWeek": 1
                }
              },
              {
                "range": {
                  "start": {
                    "gte": "12"
                  }
                }
              },
              {
                "range": {
                  "end": {
                    "lte": "14"
                  }
                }
              }
            ]
          }
        },
        {
          "bool": {
            "must": [
              {
                "term": {
                  "dayOfWeek": 2
                }
              },
              {
                "range": {
                  "start": {
                    "gte": "6"
                  }
                }
              },
              {
                "range": {
                  "end": {
                    "lte": "17"
                  }
                }
              }
            ]
          }
        }
      ]
    }
  }
}