Discussion :

[avairet]

Bonjour à tous,

J'ai trouvé sur le forum différentes pistes pour créer une requête de recherche de disponibilité d'appartements sur une période donnée.

Voici la requête sous MySQL que j'ai fini par appliquer :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT `Apartment`.`id`, `Apartment`.`name`, `Apartment`.`sleepings`, `Apartment`.`rooms`, `Apartment`.`floorarea`
    FROM `apartments` AS `Apartment`
    WHERE `Apartment`.`is_online` = 1
        AND `Apartment`.`is_available_to_rent` = 1
        AND `Apartment`.`sleepings` >= 1
        AND `Apartment`.`city_id` = 2
        AND NOT EXISTS (
            SELECT id
            FROM periods as `Period`
            WHERE
               `Period`.`start_date` BETWEEN '2012-04-02' AND '2012-04-13'
                OR `Period`.`end_date` BETWEEN '2012-04-02' AND '2012-04-13'
                OR (
                    `Period`.`start_date` < '2012-04-02'
                    AND `Period`.`end_date` > '2012-04-13'
                )
        );

Ceci fonctionne très bien si dans ma table periods, je n'ai aucun appartement qui aurait une réservation dans la période considérée.

Par contre, dès que j'ai plusieurs appartements dans la table periods, dont l'un au moins a une réservation incluse dans ou chevauchant la période de réservation, alors forcément la requête me renvoie 0 résultat.

J'ai du mal à imaginer la requête qui me permettrait de ramener les appartements disponibles, tout en éliminant ceux qui ont une réservation

Merci par avance à ceux qui pourront m'orienter vers la bonne piste

[punkoff]

Bonjour,

une petite description des tables serai bienvenue.

en particulier : y a-t-il une relation entre la table période et appartement ?

[avairet]

Ah oui désolé, c'était évident pour moi

Donc, schémas simplifiés des 2 tables :

Apartments
ID
name
sleepings
floorarea
rooms
...

Periods
ID
apartment_id (FK vers Apartments.id)
start_date
end_date
...

[punkoff]

Dans ce cas il serai interessant de faire une jointure entre la table appartement et periods dans votre sous-requete du not exists

[avairet]

Ok, merci c'est une piste que je pensais explorer car je l'ai vu dans plusieurs discussions ici, mais à chaque fois, il y avait une troisième table en jeu et la jointure se faisait entre la table periods et cette 3e table...

Je vais faire quelques tests et reviendrai ici, soit pour donner ma solution, soit pour recueillir plus d'indices en cas d'échec

[avairet]

Bon en fait je ne trouvais pas la solution avec la jointure, mais j'ai pensé à une autre piste : faire un NOT IN au lieu d'un NOT EXISTS. Je ne sais pas si c'est propre et si cela peut couvrir tous les cas, merci de me donner votre avis.

Voici la requête modifiée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
 
SELECT `Apartment`.`id`, `Apartment`.`name`, `Apartment`.`sleepings`, `Apartment`.`rooms`, `Apartment`.`floorarea`
    FROM `apartments` AS `Apartment`
    WHERE `Apartment`.`is_online` = 1
        AND `Apartment`.`is_available_to_rent` = 1
        AND `Apartment`.`sleepings` >= 1
        AND `Apartment`.`city_id` = 2
        AND `Apartment`.`id` NOT IN (
            SELECT apartment_id
            FROM `periods` as `Period`
            WHERE
                `Period`.`start_date` BETWEEN '2012-04-02' AND '2012-04-13'
                OR `Period`.`end_date` BETWEEN '2012-04-02' AND '2012-04-13'
                OR (
                    `Period`.`start_date` < '2012-04-02'
                    AND `Period`.`end_date` > '2012-04-13'
                )
        );

[al1_24]

Ton SGBD connait-il l'opérateur OVERLAPS ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

(Period.start_date, Period.end_date) OVERLAPS (CAST('2012-04-02' AS DATE), CAST('2012-04-13' AS DATE))

[avairet]

Et non, comme je l'ai précisé je suis sous MySQL (5.1) donc pas d'OVERLAPS

[punkoff]

Salut,

pour la jointure :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
 
select *
from table_1 a
where not exists (select 1 from table_2 where a.id = b.fk)

[avairet]

@punkoff : merci pour l'exemple de jointure qui est bien celle que j'avais faite, mais cela ne me remonte pas les appartements disponibles dès lors que l'un au moins a une réservation incluse ou chevauchant la période de recherche...

[punkoff]

C'est étrange car la syntaxe avec le not exists et celle avec le not in doit retourner le même résultat (hormis la gestion des valeur null qui peut changer)

[vmolines]

On n'a pas vu passer la requête à base de NOT EXISTS et vu le commentaire de avairet, je penche pour l'oubli de la corrélation dans la sous requête.

La requête qu'il donne à base de NOT IN ne devrait pas présenter ce problème mais on a pas eu le résultat obtenu avec celle-ci.

Quand on a la moitié des infos, c'est plus dur ;à

[avairet]

@vmolines : oui j'aurais du préciser que la requête avec NOT IN fonctionne très bien.

En tout cas, tous mes tests sont bons, maintenant je voulais juste avoir votre avis sur la pertinence de celle-ci par rapport à la requête avec NOT EXITS (sous réserve de trouver la bonne jointure...)

[avairet]

Voilà la requête NOT EXISTS avec jointure testée sans succès :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
 
 
SELECT `Apartment`.`id`, `Apartment`.`name`, `Apartment`.`sleepings`, `Apartment`.`rooms`, `Apartment`.`floorarea`
    FROM `apartments` AS `Apartment`
    WHERE `Apartment`.`is_online` = 1
        AND `Apartment`.`is_available_to_rent` = 1
        AND `Apartment`.`sleepings` >= 1
        AND `Apartment`.`city_id` = 2
        AND NOT EXISTS (
            SELECT 1
            FROM periods AS `Period`
            LEFT JOIN apartments AS `Apartment`
            ON (`Period`.`apartment_id` = `Apartment`.`id`)
            WHERE
               `Period`.`start_date` BETWEEN '2012-04-02' AND '2012-04-13'
                OR `Period`.`end_date` BETWEEN '2012-04-02' AND '2012-04-13'
                OR (
                    `Period`.`start_date` < '2012-04-02'
                    AND `Period`.`end_date` > '2012-04-13'
                )
        );

[punkoff]

ah bah c'est normal que ca ne marche pas.

Le not exists s'utilise comme ceci :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
SELECT Apartment.id, Apartment.name, Apartment.sleepings, Apartment.rooms, Apartment.floorarea
    FROM apartments AS Apartment
    WHERE Apartment.is_online = 1
        AND Apartment.is_available_to_rent = 1
        AND Apartment.sleepings >= 1
        AND Apartment.city_id = 2
        AND NOT EXISTS (
            SELECT 1
            FROM periods AS Period
            WHERE
               Period.apartment_id = Apartment.id and (
               Period.start_date BETWEEN '2012-04-02' AND '2012-04-13'
                OR Period.end_date BETWEEN '2012-04-02' AND '2012-04-13'
                OR (
                    Period.start_date < '2012-04-02'
                    AND Period.end_date > '2012-04-13'
                ))
        );

[avairet]

Alors, effectivement, avec cette syntaxe cela semble marcher
J'avais juste pas fait attention à mettre l'égalité entre id et fk dans la clause WHERE :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

WHERE Period.apartment_id = Apartment.id AND

Donc maintenant question subsidiaire avant de passer ce post en résolu :
quelle requête est la plus efficace entre le NOT EXISTS et le NOT IN ?

[punkoff]

Ceci va dépendre du SGBD que vous utilisez et de son interpréteur.

Faites des tests !

[avairet]

En fait je me suis mal exprimé, je vais bien entendu faire des tests pour voir quelle requête est la plus "performante", mais existe-t-il des cas où l'une fonctionnerait et l'autre pas ou bien sont elles a priori "identiques" ?

[punkoff]

Oui il y a une petite différence.

Selon le SGBD, la gestion des valeurs null sera différente entre un not exists et un not in.

En particulier si on arrive à ce genre de cas :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
select * from ma_table where 5 not in (1, 2, null);