Filtres sur des dates "à la SQL"

[avairet]

hello,

je reviens à nouveau vers vous pour voir quelle serait la meilleure approche pour filtrer mes résultats sur des dates "à la SQL".
en fait, mon implémentation concerne un système de location d'appartements.
lors d'une recherche de location, l'internaute décide d'une période : date de début, date de fin et je dois donc, parmi tous mes appartements, ne lui proposer que ceux qui sont disponibles entre ces deux dates.

en sql, je fais une requête à base de "no exists", pour vérifier pour chaque date déjà bloquées, si elles sont incluses, à cheval ou en dehors de la période considérée par la recherche de dispo :

SELECT Apartment.id
                    FROM apartments AS Apartment
                    WHERE Apartment.is_online = 1

AND NOT EXISTS (

                            SELECT 1
                            FROM periods as Period
                            WHERE Period.apartment_id = Apartment.id
                            AND (
                                Period.start_date BETWEEN '{$start_date}' AND '{$end_date}'
                                OR Period.end_date BETWEEN '{$start_date}' AND '{$end_date}'
                                OR (
                                    Period.start_date < '{$start_date}'
                                    AND Period.end_date > '{$end_date}'
                                )
                            )
                        )

dans mon index, j'ai stocké bien sûr les dates déjà bloquées pour chaque appartement, sous la forme d'objet avec start_date et end_date.

est-ce que je dois appliquer le même genre de requêtes qu'en sql, avec plusieurs filtres AND/OR ou bien y a-t-il une autre méthode plus propre ?
sachant bien entendu, que j'ai des facettes à afficher et que donc les dates ne sont pas les seuls filtres appliqués....
j'ai pensé à l'utilisation des scripts en ES, mais j'avoue que c'est très obscur pour moi...

Par avance, merci à ceux qui pourront m'aiguiller.

[David Pilato]

Une boolQuery avec du must_not à l'intérieur, ça irait ?

--

David ;-)

Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

--
--
---
Vous pouvez également poster et consulter les réponses en anglais sur le groupe Elasticsearch https://groups.google.com/group/elasticsearch
 
Si vous avez également posté votre question sur la mailing list elasti...@googlegroups.com, merci d'indiquer ici le lien vers cette discussion pour faciliter le suivi.
 
Twitter : @ElasticsearchFR https://twitter.com/#!/ElasticsearchFR
Site web (English) : http://www.elasticsearch.org/
 
---
Vous recevez ce message, car vous êtes abonné au groupe Google Groupes Elasticsearch FR.
Pour vous désabonner de ce groupe et ne plus recevoir d'e-mails le concernant, envoyez un e-mail à l'adresse elasticsearch-...@googlegroups.com.
Pour plus d'options, visitez le site https://groups.google.com/groups/opt_out .
 
 

[avairet]

Je ne sais pas, je vais tester, mais du coup je remplacerais ma term query filtrée, par une boolQuery + des filtres (et du coup mon city_id qui servait de term à ma query deviendrait un filtre) ?

[David Pilato]

Tu peux faire des boolFilter aussi.

--

David ;-)

Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

[avairet]

Oui j'y ai bien pensé, mais là franchement je n'arrive à rien au niveau des dates...

Voici les données de deux documents, si je fais une recherche de dispo entre le 15 mai et le 18 mai, il faudrait donc que le 2e ne ressorte pas dans mes résultats puisqu'il a une période bloquée qui va du 13 mai au 17 mai.

apart_id: 1
name: Chinois
sleepings: 5
rooms: 2
city_id: 2
district: Opéra
facilities: [
    Climatisation
    Internet
    Parking
]
elevator: 0
is_available_to_rent: 1
periods: [

    {
        start_date: 2013-09-04
        end_date: 2013-09-06
        apartment_id: 1
    }
    {
        start_date: 2013-09-07
        end_date: 2013-09-10
        apartment_id: 1
    }
]

apart_id: 2
name: Anglais
sleepings: 3
rooms: 2
city_id: 2
district: Opéra
facilities: [
    Climatisation
    Internet
    Parking
]
elevator: 0
is_available_to_rent: 1
periods: [

    {
        start_date: 2013-05-13
        end_date: 2013-05-17
        apartment_id: 1
    }
    {
        start_date: 2013-09-07
        end_date: 2013-09-10
        apartment_id: 1
    }
]

J'ai essayé avec un filtre "boolnot" en lui passant un filtre range sur la période considérée, mais cela ne fonctionne pas...

[David Pilato]

Je pense que tu as un problème de mapping ici.

Dans ton cas, tu devrais indexer avec des nested.

En effet, ES indexe tes dates ainsi :

start_date: 2013-09-04, 2013-09-07

end_date: 2013-09-06, 2013-09-10

Autrement dit, il a mis tout à plat ! Ce qui peut rendre des résultats non désirés.

--

David ;-)

Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

[avairet]

Ok, je vois, il y avait une mention de cela dans un autre post...
Pourtant, si je fais ma requête en utilisant les bornes exactes d'une période précise, alors l'appartement ne remonte pas dans les résultats.
Mon problème est de trouver une solution pour les périodes incluses et à cheval...
Je vais regarder du côté de nested, merci David :)

[avairet]

Voilà un gist qui montre mon mapping, le doc qui n'est pas sensé remonter dans les résultats et l'une des requêtes que je teste...
https://gist.github.com/avairet/5531362

[Jérôme Mainaud]

Non, même avec les bornes exactes, tu ne trouveras pas le bon résultat.
Effectivement, si tu demandes « start_date: 2013-09-04 & end_date: 2013-09-06 » tu ne trouveras que le premier enregistrement, mais tu le trouveras aussi si tu demande « start_date: 2013-09-07 & end_date: 2013-09-06 » alors que tu dois t'attendre à ne rien trouver.

Lorsque tu utilises des inégalités, les interactions inattendues sont plus importantes et la requête retrouve presque tout.

C'est pour ça que David t'as conseillé de regarder à définir les periods en nested ("type": "nested" au lieu de "type": "object" qui est implicite quand il y a un attribut « properties » dans la description du champ dans le mapping). Elles seront enregistrées dans un autre index qui est géré de façon quasi-transparente par ES. Il te faut aussi adapter tes requêtes avec la nested query http://www.elasticsearch.org/guide/reference/query-dsl/nested-query/ et tu devrais obtenir ce que tu cherches.

Note, il n'est pas nécessaire de transformer tes autres sous objets en nested tant qu'ils ne sont pas multivalués ou que tu ne fais pas de requête sur plusieurs champs liés. Les nested  ont un cout, c'est pour ça qu'ils ne sont pas mis en œuvre par défaut.

Jérôme Mainaud
jer...@mainaud.com

Le 06/05/13 18:54, avairet a écrit :

[Nicolas Labrot]

Serait il pertinent d'avoir associé à chaque appartement la liste de tous les jours non occupés sous la forme d'un champ multivalué mais non stocké ?

la requête d'un appartement ayant une plage libre devenant du coup simple : l'un des jours de la plage recherché présent dans le champ multi valué.

D'un point de vu cout cela me semble a priori faible dans la mesure ou il y aurait bcp de redondance des valeurs entre les appartements.

2013/5/8 Jérôme Mainaud <jer...@mainaud.com>

[Jérôme Mainaud]

Ça mérite réflexion. Pense à la mise à jour de ces informations et à leur évolution avec le temps. Réfléchi à la gestion des dates lointaines.

--
Jérôme Mainaud
jer...@mainaud.com

[Nicolas Labrot]

Même si les réservations se font à 5 ans, la volumétrie me semble encore acceptable. Les jours passés, devant eux être bien sur nettoyés. Le document est de toute façon mis à jour avec les dates réservées, la mise à jour du champ se faisant en post process. La logique peut être inversé, le champ contient les jours réservés.

A l'op de tester les différentes solutions

2013/5/8 Jérôme Mainaud <jer...@mainaud.com>

[avairet]

@jérôme : si tu regardes bienle gist, j'ai bien déclaré en nested et j'ai tenté de faire des requêtes nested, mais sans succès. Du coup, pour le moment, j'ai laissé en suspend et j'ai fait un autre système en PHP/MySQL uniquement pour mes recherches de date de disponibilité. C'est bête de ne pas profiter de la puissance d'ES pour se passer de PHP/MySQL, mais dans le temps qui m'était imparti, je ne pouvais pas faire plus de R&D sur ES...

[avairet]

@Nicolas : merci pour cette piste, mais elle impliquerait une refonte importante de mon code de base, car ces fameuses dates sont utilisées à plein d'autres endroits qui ne font pas appel à ES... ou alors je n'ai pas tout à fait saisi ta solution. Quoi qu'il en soit, je n'ai plus le temps sur ce projet là de faire des essais :)

[Jérôme Mainaud]

Bonjour,

Effectivement, ça m'a échappé. Ton exemple est tellement long et le type placé après la définitions des champs n'était pas visible.

Pour nous simplifier la tâche, ce serait gentil de réduire tes exemples à un cas minimal et de nous mettre des vrais JSON bien présentés. L'idéal étant le script shell avec des commandes curl qu'on peut rejouer facilement (donc sur localhost:9200). Ce serait cool.

S'il te restait un peu de temps, je te proposerais de demander l'explication du résultat, soit en précisant un "explain": true dans la requête, soit, pour le document particulier qui remonte alors qu'il ne devrait pas, avec l'API explain http://www.elasticsearch.org/guide/reference/api/explain/

Bonne soirée.

--
Jérôme Mainaud
jer...@mainaud.com

[avairet]

Merci encore de te pencher sur mes questions :)

J'ai fait ce que j'ai pu pour montrer du code, car en fait plusieurs problématiques m'empêchent de faire des jolies choses :
- je suis sous Windaube et lorsque je fais des requêtes Curl avec Cygwin, je ne peux rien indenté, donc c'est pas trivial d'écrire tout directement sans me planter
- j'essaie de générer parfois les requêtes via les formulaires du plugin Head depuis mon browser, mais là encore, bien que je puisse valider mon JSON, j'ai des résultats totalement faux (par exemple aucun filtre ne semble marcher puisque systématiquement cela me renvoie la totalité des enregistrements)
- j'utilise ElasticaPHP, couplé à mon framework MVC, du coup toute cette encapsulation m'empêche d'avoir facilement accès aux requêtes générées et de vous les montrer. Il y a bien un système de log dans Elastica que j'ai utilisé pour faire mon gist, mais le format est peu pratique.

Pour revenir aux questions sur les dates, je vais prendre le temps, pour mon expérience personnelle, de refaire des tests avec explain et nested.

[David Pilato]

Utilise les outils genre REST Console ou POSTMAN pour Chrome.

Sinon, il y a un outil spécial pour ES fait par Boaz mais le nom m'échappe soudain (sense ?)

--

David ;-)

Twitter : @dadoonet / @elasticsearchfr / @scrutmydocs

[jst...@gmail.com]

David,

Je pense tu parlais de : https://github.com/bleskes/sense

Cdlt
Jean

---

From: David Pilato <da...@pilato.fr>

Sender: elastics...@googlegroups.com

Date: Fri, 17 May 2013 15:19:53 +0200

To: elastics...@googlegroups.com<elastics...@googlegroups.com>

ReplyTo: elastics...@googlegroups.com

Subject: Re: [elasticsearch-fr] Filtres sur des dates "à la SQL"

[Nicolas Labrot]

2013/5/14 avairet <ava...@gmail.com>

@Nicolas : merci pour cette piste, mais elle impliquerait une refonte importante de mon code de base, car ces fameuses dates sont utilisées à plein d'autres endroits qui ne font pas appel à ES... ou alors je n'ai pas tout à fait saisi ta solution. Quoi qu'il en soit, je n'ai plus le temps sur ce projet là de faire des essais :)

La manière dont tu les gères nominalement reste inchangé. Ce qui changerait c'est qu'avant de sauvegarder le document tu déclenches une sorte de trigger qui va te générer/dénormaliser la table des dispos pour faciliter le requetage