Discussion :

[Paul75]

Bonsoir,

J'ai un problème de conception de requetes SQL.

J'ai 2 tables comme cela (faite avec model TypeOrm) :

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
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
export class Table1 {
    @PrimaryGeneratedColumn()
    public id: number;
 
    @IsNotEmpty()
    @Column({ name: 'nom' })
    public nom: string;
 
    @OneToMany(
        type => Passage,
        passage => passage.domicilie,
        {
            nullable: true,
            cascade: true,
            onUpdate: 'RESTRICT',
            onDelete: 'RESTRICT',
        }
    )
    public passages: Passage[];
}
 
@Entity('passage')
export class Passage {
    @PrimaryGeneratedColumn()
    public id: number;
 
    @Column({
        type: 'timestamp',
        nullable: false,
        default: () => 'CURRENT_TIMESTAMP',
    })
    public timestamp: Date;
 
    @ManyToOne(
        type => Table1,
        table1 => table1.passages,
        {
            nullable: false,
            onUpdate: 'RESTRICT',
            onDelete: 'RESTRICT',
        }
    )
    @JoinColumn({ name: 'table1_id' })
    public table1: Table1;
}

La requête que je dois réalisé doit me retourner toutes les lignes de la table1 qui ont un passage < à 3 mois.

J'ai essayé comme cela mais je ne suis pas sur que cela soit la bonne façon.

Code :

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

1
2
3
4
5
6
7
8
9
10
SELECT `table1`.`id` AS `id`,
`table1`.`nom` AS `nom`,
`table1`.`prenom` AS `prenom`
FROM `table1` `table1`
WHERE `table1`.`id` NOT IN (
SELECT id
FROM `passage` `passage`
WHERE `table1`.`id` = `passage`.`id`
AND `passage`.`timestamp` < '2019-12-03'
)

une petite aide ?

Merci

[CinePhil]

1) Plutôt que le code de l'ORM, donnez nous directement la structure des tables ; ce sera plus compréhensible pour nous.

2) A première vue, il faut que vous fassiez une jointure entre les deux tables.

[Paul75]

1) Plutôt que le code de l'ORM, donnez nous directement la structure des tables ; ce sera plus compréhensible pour nous.

2) A première vue, il faut que vous fassiez une jointure entre les deux tables.

Bonjour,

Désolé, voila le schéma :



Merci

[CinePhil]

Donc essayez effectivement de faire une jointure entre les deux tables.

[Paul75]

Donc essayez effectivement de faire une jointure entre les deux tables.

Bonjour,

Merci mais dans la jointure en SQL j'aimerais avoir la ligne correspondant que avec la date la plus proche (derniere donc).

Il y a une opération en SQL ?

Merci

[Paul75]

Donc essayez effectivement de faire une jointure entre les deux tables.

Bonjour,

J'ai écrit comme cela :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT `domicilie`.`id` AS `domicilie_id`,
`domicilie`.`numero` AS `domicilie_numero`,
`domicilie`.`nom` AS `domicilie_nom`,
`domicilie`.`prenom` AS `domicilie_prenom`
FROM `domicilie` `domicilie`
INNER JOIN `passage` `passage`
ON `domicilie`.`id` = `passage`.`domicilie_id`
WHERE `passage`.`timestamp` <= '2019-12-03'
AND `domicilie`.`isDisabled` = false

Cela vous semble correct ? car le resultat je pens eque si dans la table passage pour cet id il y a plusieurs lignes il va les sortir ?

En fait je veux les lignes de domiciliés dont la date est < 3 mois mais si dans les passages il y a -3 mois et aussi +3mois je ne tiens pas compte dans les lignes affichées...

J'ai test, la requete semble bien donner le bon résultat mais seulement si dans passage il y a pas de date < 3mois et aussi > 3 mois....

Merci

[Paul75]

Bonjour,

En fait j'aimerais pas être obligé de faire comme cela :

rechercher toutes les lignes de domicilies, puis pour chaque ligne (boucle for) rechercher leur passages (dernière date) et comparé avec si date < 3 mois on prend dans le resultat, sinon non...

Cela me semble lourd comme traitement ? Je pensais plus simple en requête SQL mais si pas possible ...

Merci

[CinePhil]

En fait je veux les lignes de domiciliés dont la date est < 3 mois mais si dans les passages il y a -3 mois et aussi +3mois je ne tiens pas compte dans les lignes affichées...

Pouvez-vous expliquer ça plus clairement ?
Dans votre requête, la condition WHERE `passage`.`timestamp` <= '2019-12-03' va afficher les lignes dont la date de passage est inférieure au 3 décembre 2019, soit plus de 3 mois. N'est-ce pas le contraire de ce que vous souhaitiez au départ ?

Et pour que votre requête fonctionne avec n'importe quelle date, il vaut mieux faire ainsi (j'en profite pour simplifier l'écriture de votre requête grâce aux alias) :

Code :

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

1
2
3
4
5
6
7
8
9
SELECT d.id AS domicilie_id,
	d.numero AS domicilie_numero,
	d.nom AS domicilie_nom,
	d.prenom AS domicilie_prenom
FROM domicilie d
INNER JOIN passage p
	ON d.id = p.domicilie_id
WHERE p.`timestamp` < SUBDATE(CURRENT_DATE, INTERVAL 3 MONTH)
	AND d.isDisabled = FALSE

Vous remarquerez que j'ai supprimé presque toutes les apostrophes inversées qui sont inutiles, sauf autour de la colonne `timestamp` car c'est une colonne mal nommée avec un mot du langage SQL.

[Paul75]

Bonjour,

Oups désolé en effet c'est bien < car c'est les dates de plus de 3 mois me suis trompé je viens de voir.

Oui la requete semble bien mais comme je disais su jamais j'ai dans la table passage :

id date
1 2019-01-20
20 2020-03-04

le resulata va trouver une ligne que la date est bien de plus de 3 mois .... et me le renvoyé...

alors que dans ce cas non la ligne ne doit pas etre prus en compte des domicilies du coup...

Plus clair ?

Merci

[CinePhil]

Non, pas plus clair !

Ma requête ne devrait conserver que la seconde date : 2020-03-04. En effet, elle fait la différence entre aujourd'hui (CURRENT_DATE) et il y a trois mois (SUBDATE(CURRENT_DATE, INTERVAL 3 MONTH).
Si je soustrais d'aujourd'hui la date '2019-01-20', j'obtiens bien plus de 3 mois.

Code SQL :

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

SELECT SUBDATE(CURRENT_DATE, INTERVAL 3 MONTH)

=> 2019-12-04
Toutes les dates qui ne sont pas comprises entre le 4 décembre 2019 et aujourd'hui (04/03/2020) sont excluent du résultat de ma requête.

[Paul75]

Bonjour
En fait non si dans passage j'ai

Id	id_domicilie	timestamp
1	20000	2019-01-20
2	20000	2020-04-03
3	20000	2020-04-04

Je ne dois pas retrouver dans le résultat la ligne des domiciliés portant le numéro 20000 puisque les deux première ligne certes plus de 3 mois mais la dernières non du coup c'est inférieure à 3 mois. Si au moins une ligne à une date inférieure à 3 mois je prend pas compte...


C'est mieux ?

Merci

[CinePhil]

Toujours pas clair !

Id	id_domicilie	timestamp
1	20000	2019-01-20
2	20000	2020-04-03
3	20000	2020-04-04

...

les deux première ligne certes plus de 3 mois mais la dernières non du coup c'est inférieure à 3 mois.

Euh...
La première '2019-01-20' est située il y a plus de trois mois.
La deuxième '2020-04-03', c'est hier, donc il y a moins de trois mois.
La troisième '2020-04-04' est dans le futur !

Si au moins une ligne à une date inférieure à 3 mois je prend pas compte...

Donc vous voulez exclure du résultat de la requête tous les id_domicilie qui ont au moins un timestamp situé il y a plus de trois mois ?

Commençons par chercher tous les id_domicilie dans ce cas :

Code :

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

1
2
3
SELECT DISTINCT id_domicilie
FROM passage
WHERE `timestamp` < SUBDATE(CURRENT_DATE, INTERVAL 3 MONTH)

Cherchons maintenant les domicilie qui ne sont pas dans ce résultat :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT d.id AS domicilie_id,
	d.numero AS domicilie_numero,
	d.nom AS domicilie_nom,
	d.prenom AS domicilie_prenom
FROM domicilie d
LEFT JOIN 
(
	SELECT DISTINCT id_domicilie
	FROM passage
	WHERE `timestamp` < SUBDATE(CURRENT_DATE, INTERVAL 3 MONTH)
) t
	ON t.id_domicilie = d.id
WHERE t.id_domicilie IS NULL

[Paul75]

Bonjour,

Entre la requête d'avant qui me sortait 37 lignes cella la dans l'exemple me sort 6400 lignes ....

Merci

[CinePhil]

Et donc ? C'est bon ou pas ? ?

[Paul75]

Bonsoir

Je vais vérifier les résultats.

[Paul75]

Bonjour,

Non je pense pas que cela soit correct. un exemple dans la table domicilié
j'ai par exemple pour :
id = 4
nom = DUPONT

si je recherche maintenant dans la table passage avec comme domicilie_id = 4
j'obtiens :
id = 110
timestamp = 2020-02-04 12:15:45

hors 2020-02-04 c'est pas encore plus de 3 mois ?

Donc ne devrait pas être dans la liste de retour....

Merci

[Paul75]

re,

Par contre si je fait comme cela :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT d.id AS domicilie_id,
	d.numero AS domicilie_numero,
	d.nom AS domicilie_nom,
	d.prenom AS domicilie_prenom
FROM domicilie d
LEFT JOIN 
(
	SELECT DISTINCT id_domicilie
	FROM passage
	WHERE `timestamp` > SUBDATE(CURRENT_DATE, INTERVAL 3 MONTH)
) t
	ON t.id_domicilie = d.id
WHERE t.id_domicilie IS NULL

cela semble donner le résultat attendu....

J'ai pas tous testé

EDIT : Sauf que en essayant un peu plus loin je tombe sur des lignes qui n'ont aucun passage (dans la table passage pas d'id)...
Je pense pas que cela soit bon je réfléchi car cela voudrait dire que tous le monde qui n'a pas dans le passage sera considéré comme non passé et donc +3mois

Merci

[CinePhil]

Vous n'avez pas répondu à cette question :

Donc vous voulez exclure du résultat de la requête tous les id_domicilie qui ont au moins un timestamp situé il y a plus de trois mois ?

J'ai supposé que la réponse était oui mais...

si je recherche maintenant dans la table passage avec comme domicilie_id = 4
j'obtiens :
id = 110
timestamp = 2020-02-04 12:15:45

hors 2020-02-04 c'est pas encore plus de 3 mois ?

Donc ne devrait pas être dans la liste de retour....

Si le domicilie_id = 4 n'a pas de timestamp situé il y a plus de trois mois, il sera dans le résultat de la requête.

Une bonne fois pour toutes, clarifiez votre demande !
Quelles lignes voulez-vous obtenir ?

[Paul75]

Bonsoir,

Je pensais que j'était clair. Je vois pas mieux expliquer je pensais déjà avoir tout mis même avec l'exemple ????

2 tables : domicilies et passage.

un domicilié peut avoir 1 ou plusieurs passages.

Ma requête doit retourner les lignes des domiciliés dont la date de dernier passage doit être supérieur à 3 mois. Exemple si le domicilié 1 à pour dernière date de passage le 2020-03-01 et date du jour le 2020-03-04 : 2020-03-04 - 2020-03-01 = -3mois donc il ne s'affiche pas. J'ai bien dit la date de dernier passage dans le cas ou plusieurs dates ne pas tenir compte des autres pour ce meme domicilié.....

Prenon un exemple :

mes domiciliés :

Code :

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

1
2
3
4
5
| id |   numero    |    nom     |
|  1 |      1000   |    TOTO    |
|  2 |      1000   |    LULU    |
|  3 |      2000   |    PHIL    |
|  4 |      3000   |    TOTO2   |

Table des passages :

Code :

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

1
2
3
4
5
6
7
8
9
| id |    domicilie_id    |    timestamp      |
|  1 |               1    |    2020-02-20     |
|  2 |               1    |    2020-03-04     |
|  3 |               2    |    2018-04-04     |
|  4 |               2    |    2019-06-20     |
|  5 |               3    |    2019-01-20     |
|  6 |               3    |    2019-03-12     |
|  7 |               3    |    2019-04-11     |
|  8 |               3    |    2020-03-04     |

Résultat de la requête :

Code :

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

1
2
| id |   numero    |    nom     |
|  2 |      1000   |    LULU    |

La c'est plus clair ?

[CinePhil]

Ma requête doit retourner les lignes des domiciliés dont la date de dernier passage doit être supérieur à 3 mois.

Ben voilà ! Avec le mot "dernier", c'est plus clair !

Commençons par chercher le dernier passage de chaque domicilié :

Code SQL :

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

1
2
3
SELECT domicilie_id, MAX(`timestamp`) AS date_dernier_passage
FROM passage
GROUP BY domicilie_id

Ne retenons maintenant que les domiciliés dont la date de dernier passage est supérieure à 3 mois (donc inférieure à il y a trois mois) :

Code SQL :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT 
	d.id AS domicilie_id,
	d.numero AS domicilie_numero,
	d.nom AS domicilie_nom,
	d.prenom AS domicilie_prenom
FROM domicilie d
LEFT JOIN 
(
	SELECT domicilie_id, MAX(`timestamp`) AS date_dernier_passage
	FROM passage
	GROUP BY domicilie_id
) t
	ON t.domicile_id = d.id
WHERE t.date_dernier_passage < SUBDATE(CURRENT_DATE, INTERVAL 3 MONTH);