aide pour une requete multitable

SELECT *
FROM
Principale
LEFT JOIN
Liaison
ON
Principale.id=Liaison.refid
WHERE
(Liaison.propriete="Rouge" AND Liaison.propriete="Vert")

Liaison.propriete="Rouge" OR Liaison.propriete="Vert"

c'est pas mieux ?

Bonjour bob




Voilà la structure, si certains veulent essayer :



CREATE TABLE `eod_db`.`principale` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`nom` VARCHAR( 10 ) NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB

INSERT INTO `principale` (`id`, `nom`) VALUES
(1, 'voiture'),
(2, 'maison'),
(3, 'chaussure'),
(4, 'marteau');

CREATE TABLE `eod_db`.`liaison` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`refid` INT( 10 ) NULL ,
`propriete` VARCHAR( 10 ) NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB

INSERT INTO `liaison` (`id`, `refid`, `propriete`) VALUES
(1, 1, 'Rouge'),
(2, 1, 'Vert'),
(3, 2, 'Rouge');

SELECT *
FROM
principale
LEFT JOIN
liaison
ON
principale.id=liaison.refid
WHERE
(liaison.propriete="Rouge" AND liaison.propriete="Vert")

liaison.propriete="Rouge" OR liaison.propriete="Vert"

c'est pas mieux ?

Oui et non : ca me ressort les enregistrements rouge ou vert :

id nom id refid propriete
1 voiture 1 1 Rouge
1 voiture 2 1 Vert
2 maison 3 2 Rouge


Or, je recherche tous les enregistrements donc les couleurs sont "Rouge et
Vert".


@+
Pascal

Pascal G. a écrit :

CREATE TABLE `eod_db`.`principale` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`nom` VARCHAR( 10 ) NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB

INSERT INTO `principale` (`id`, `nom`) VALUES
(1, 'voiture'),
(2, 'maison'),
(3, 'chaussure'),
(4, 'marteau');

CREATE TABLE `eod_db`.`liaison` (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`refid` INT( 10 ) NULL ,
`propriete` VARCHAR( 10 ) NULL ,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB

INSERT INTO `liaison` (`id`, `refid`, `propriete`) VALUES
(1, 1, 'Rouge'),
(2, 1, 'Vert'),
(3, 2, 'Rouge');

à vue de nez, cela justifiera une jointure naturelle (pas un left join
qui autorise l'absence de correspondance).

SELECT *
FROM
principale
LEFT JOIN
liaison
ON
principale.id=liaison.refid
WHERE
(liaison.propriete="Rouge" AND liaison.propriete="Vert")

liaison.propriete="Rouge" OR liaison.propriete="Vert"
c'est pas mieux ?

Oui et non : ca me ressort les enregistrements rouge ou vert :
Or, je recherche tous les enregistrements donc les couleurs sont
"Rouge et Vert".

mon impression est que la question était mal posée parce que les
tables sont mal construites; il semblerait que chaque item de la
table principale puisque contenir différentes propriétés et que
vous cherchez les lignes de cette table qui respectent plusieurs
critères de la seconde.

si c'est le cas, les tables sont mal construites car un champ lié
de la table 2 ne servira jamais seul mais (comme pour ce problème)
vous voudrez (souvent, toujours, ...) récupérer toutes les props
appliqués à un record de la table 1, dès lors un champ propriétés
de type VARCHAR(255) ou TEXT contenant "Rouge, Vert" lorsque les
2 s'appliquent simplifierait tout.

ie:
principale
1 'voiture'
2 'maison'
...

liaison
1 1 'Rouge, Vert'
2 2 'Rouge'
...

pour rechercher dans la table secondaire les id contenant les
même refid et l'une ou l'autre des 2 propriétés recherchées
vous pouvez faire une jointure naturelle de la table sur elle-
même, cela nous donnent alors les refid qui servent à sélectionner
les bons id dans la table primaire (un champ lié, principale.id
et liaison.refid devraient évidemment porter le même nom pour
faciliter la lecture mais ce n'est pas le propos).

il vient donc:

SELECT * FROM principale p WHERE p.id IN (
SELECT p1.id FROM liaison p1
INNER JOIN liaison p2 ON p2.refid=p1.refid
WHERE (p1.propriete="Rouge" AND p2.propriete="Vert")
)

Sylvain.

Bonjour Sylvain SF

mon impression est que la question était mal posée parce que les
tables sont mal construites; il semblerait que chaque item de la
table principale puisque contenir différentes propriétés et que
vous cherchez les lignes de cette table qui respectent plusieurs
critères de la seconde.

si c'est le cas, les tables sont mal construites car un champ lié
de la table 2 ne servira jamais seul mais (comme pour ce problème)
vous voudrez (souvent, toujours, ...) récupérer toutes les props
appliqués à un record de la table 1, dès lors un champ propriétés
de type VARCHAR(255) ou TEXT contenant "Rouge, Vert" lorsque les
2 s'appliquent simplifierait tout.

ie:
principale
1 'voiture'
2 'maison'
...

liaison
1 1 'Rouge, Vert'
2 2 'Rouge'
...

Oui, ca simplifierai, mais je ne veux pas le faire de cette façon.

pour rechercher dans la table secondaire les id contenant les
même refid et l'une ou l'autre des 2 propriétés recherchées
vous pouvez faire une jointure naturelle de la table sur elle-
même, cela nous donnent alors les refid qui servent à sélectionner
les bons id dans la table primaire (un champ lié, principale.id
et liaison.refid devraient évidemment porter le même nom pour
faciliter la lecture mais ce n'est pas le propos).

il vient donc:

SELECT * FROM principale p WHERE p.id IN (
SELECT p1.id FROM liaison p1
INNER JOIN liaison p2 ON p2.refid=p1.refid
WHERE (p1.propriete="Rouge" AND p2.propriete="Vert")
)

Voilà, c'est impeccable. Ca fonctionne avec 1 critère, 2 ou x critères...
C'était la réponse que j'attendais.

Merci

@+
pascal

Sur fr.comp.applications.sgbd, Pascal G. disait :

Principale :
id,nom

Liaison : (le refid de cette table est lié à l'id de la table
principale) id,refid,propriete

Moi je ferais un truc du genre :

SELECT p.id, p.nom
FROM Principale p
JOIN (
SELECT l.refid
FROM Liaison l
WHERE l.propriete IN ('Vert','Rouge')
GROUP BY l.refid
HAVING count(*) = 2
) s ON p.id = s.refid

Je pense que c'est ce qui est le plus facilement optimisable par le
SGBDR. Mais bien sûr, il faut faire des benchmarks avec des données
réelles pour le confirmer.

L'autre avantage, c'est que la même requête fonctionne avec
autant de propriétés que tu veux, sans vraiment de perte de
performances. Si tu as un système de placeholder assez
évolué pour supporter un tableau, c'est encore plus intéressant.


Pour ce qui est de ton modèle de données, je trouve qu'il est bien
meilleur que celui que te propose Sylvain SF. Les SGBDR sont par
définition fait pour faire des jointures, et pas des analyses
textuelles.

D'ailleur je pense que dans un modèle normalisé, il faudrait au
contraire rajouter une table :

CREATE TABLE Propriete (
id SERIAL PRIMARY KEY,
nom TEXT UNIQUE
);

Du coup la table liaison devient une vrai table de liaison *,* :

CREATE TABLE Liaison (
refid INTEGER FOREIGN KEY Principale (id),
propriete INTEGER FOREIGN KEY Propriete (id),
CONSTRAINT PRIMARY KEY (propriete, refid)
)

Et la requête précédente devient :

SELECT p.id, p.nom
FROM Principale p
JOIN (
SELECT l.refid
FROM Liaison l
JOIN Proprite r ON l.propriete = r.id
WHERE r.nom IN ('Vert','Rouge')
GROUP BY l.refid
HAVING count(*) = 2
) s ON p.id = s.refid

Ça fait une jointure de plus, mais il est quand même possible que ça
soit plus rapide. Ça dépend pas mal des index et de leur utilisation.
Mais comme on gagne de l'espace, on peut espérer avoir moins de "sauts
de page" et donc au final gagner un peu de temps.

--
Si les procédures de création,
de gestion, étaient plus simple,
plus transparente, et non pas
la chasse gardée de petit potentats,
il n'aurait jamais fait cela.
-- Benoit 4x4 dans fufe --

Pascal G a écrit :

un champ propriétés de type VARCHAR(255) ou TEXT contenant
[toutes les propriétés]

Oui, ca simplifierai, mais je ne veux pas le faire de cette façon.

dire pourquoi aurait été en charte et sûrement intéressant.

si les N propriétés (N petit) résulte de choix indépendant
d'une interface (des listes de valeurs imposées avec une
possibilité "d'ajouter un critère"), le choix est peut être
le plus simple.

mais si ce champ peut être construit plus librement et (surtout)
s'il ne doit pas être "déconstruit" (décomposé pour en réextraire
chacune des propriétés individuelles), un champ TEXT reste le plus
efficace; vous disposez via MySQL d'outils de recherches multi-critères
sur de tels champs et ils sont très efficaces.

CREATE TABLE principale (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`nom` TEXT default NULL,
PRIMARY KEY ( `id` )
) ENGINE = MyISAM;

INSERT INTO `principale` (`id`, `nom`) VALUES
(1, 'voiture'),
(2, 'maison'),
(3, 'chaussure'),
(4, 'marteau');


CREATE TABLE liaison (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`refid` INT( 10 ) NOT NULL references principale(id),
`propriete` TEXT default NULL ,
PRIMARY KEY ( `id` ),
INDEX ( `refid` ),
FULLTEXT ( `propriete` )
) ENGINE = MyISAM;

INSERT INTO `liaison` (`id`, `refid`, `propriete`) VALUES
(1, 1, 'Rouge Vert'),
(2, 1, 'Rouge'),
(3, 1, 'Bleu'),
(4, 1, 'Noir'),
(5, 2, 'Bleu');


(j'utilise le moteur ISAM et non InnoDB car celui-ci ne supporte
pas les index full-text.
j'ai également ajouté qlq propriétés pour que la recherche ne risque
pas de retrouver plus de 50% de la table - sans quoi le mot est
considéré non pertinent et est exclu de la réponse).

on peut alors faire la recherche par:

SELECT * FROM principale p inner join liaison s on s.refid=p.id
WHERE MATCH (s.propriete) AGAINST ('+Rouge +Vert' IN BOOLEAN MODE);

en réponse à Jogo, je considère également que "Les SGBDR sont par
définition fait pour faire des jointures, et pas des analyses
textuelles." c'est pourquoi je fais une jointure et non une
recherche texte dans une liste ("in ('vert', 'rouge')") !!
son approche a néanmoins le même coût (le group by ayant le même
effet qu'une jointure naturelle cyclique).

son autre point est (plus) juste, si les valeurs de propriétés peuvent
être dénombrées, il est pertinent de leur associer un identifiant
numérique unique et de faire alors la recherche sur de tels nombres;

avec les corrections suivantes:

CREATE TABLE Principale (
`id` INTEGER NOT NULL AUTO_INCREMENT,
`nom` VARCHAR( 10 ) NULL,
PRIMARY KEY ( `id` )
) ENGINE = InnoDB;

INSERT INTO `principale` (`id`, `nom`) VALUES
(1, 'voiture'),
(2, 'maison'),
(3, 'chaussure'),
(4, 'marteau');

CREATE TABLE Propriete (
`id` SERIAL,
`nom` TEXT,
PRIMARY KEY (`id`)
) ENGINE = InnoDB;

-- UNIQUE n'est pas reconnu ici par MySQL

INSERT INTO `Propriete` (`id`, `nom`) VALUES
(1, 'Rouge'),
(2, 'Vert'),
(3, 'Bleu');


CREATE TABLE Liaison (
refid INTEGER NOT NULL,
prop BIGINT UNSIGNED NOT NULL,
INDEX (refid, prop),
CONSTRAINT FOREIGN KEY (refid) REFERENCES Principale (id),
CONSTRAINT FOREIGN KEY (prop) REFERENCES Propriete (id)
) ENGINE = InnoDB;

-- refid et prop ne peuvent être des clés primaires
-- car il existera des occurrences multiples.
-- les clés externes ne peuvent pas être définies en ligne.

INSERT INTO `Liaison` (`refid`, `prop`) VALUES
(1, 1),
(1, 2),
(2, 1);

on pourra faire:

SELECT p.id, p.nom
FROM Principale p
JOIN (
SELECT l.refid
FROM Liaison l
WHERE l.prop IN (1, 2)
GROUP BY l.refid
HAVING count(*) = 2
) s ON p.id = s.refid

ou encore

SELECT p.id,p.nom FROM Principale p
JOIN Liaison L1 on L1.refid=p.id
JOIN Liaison L2 on L2.refid=p.id
where L1.prop=1 and L2.prop=2

qui est une 3ième façon de faire la même chose.

Sylvain.

Bonsoir



C'est par hasrad que je reviens faire un tour ici, et que je découvre ta
réponse. Donc, désolé de ne pas t'avoir répondu plus tôt.

un champ propriétés de type VARCHAR(255) ou TEXT contenant
[toutes les propriétés]

Oui, ca simplifierai, mais je ne veux pas le faire de cette façon.

dire pourquoi aurait été en charte et sûrement intéressant.

si les N propriétés (N petit) résulte de choix indépendant
d'une interface (des listes de valeurs imposées avec une
possibilité "d'ajouter un critère"), le choix est peut être
le plus simple.

De ce que j'ai compris, tu me proposais de lister dans une table toutes les
options possibles , avec 1 à x possiblitée.
Je n'ai pas fait le calcul, mais cela représenterai X carré enregistrements.
De plus, il est assez probable que je "rajoute" des enregistrements dans la
table principale, ce qui impliquerai donc que chaque fois je doive modifier
la table secondaire ?

Mais, quand bien même j'ai mal compris ce que tu me proposais, j'ai utilisé
ce système de table de liaison car d'habitude je l'en sers pour lier deux
tables "principale", et là, cela m'a semblé logique de faire un peu le même
principe, même si je n'avais pas de seoccnde table à lier.

mais si ce champ peut être construit plus librement et (surtout)
s'il ne doit pas être "déconstruit" (décomposé pour en réextraire
chacune des propriétés individuelles), un champ TEXT reste le plus
efficace; vous disposez via MySQL d'outils de recherches multi-critères
sur de tels champs et ils sont très efficaces.

CREATE TABLE principale (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`nom` TEXT default NULL,
PRIMARY KEY ( `id` )
) ENGINE = MyISAM;

INSERT INTO `principale` (`id`, `nom`) VALUES
(1, 'voiture'),
(2, 'maison'),
(3, 'chaussure'),
(4, 'marteau');


CREATE TABLE liaison (
`id` INT( 10 ) NOT NULL AUTO_INCREMENT ,
`refid` INT( 10 ) NOT NULL references principale(id),
`propriete` TEXT default NULL ,
PRIMARY KEY ( `id` ),
INDEX ( `refid` ),
FULLTEXT ( `propriete` )
) ENGINE = MyISAM;

INSERT INTO `liaison` (`id`, `refid`, `propriete`) VALUES
(1, 1, 'Rouge Vert'),
(2, 1, 'Rouge'),
(3, 1, 'Bleu'),
(4, 1, 'Noir'),
(5, 2, 'Bleu');


(j'utilise le moteur ISAM et non InnoDB car celui-ci ne supporte
pas les index full-text.

Je dois utiliser InnoDB car je crée des ON DELETE CASCADE, en cas de
suppression d'un article dans la table principale.

j'ai également ajouté qlq propriétés pour que la recherche ne risque
pas de retrouver plus de 50% de la table - sans quoi le mot est
considéré non pertinent et est exclu de la réponse).

on peut alors faire la recherche par:

SELECT * FROM principale p inner join liaison s on s.refid=p.id
WHERE MATCH (s.propriete) AGAINST ('+Rouge +Vert' IN BOOLEAN MODE);

Effectivement, ça fonctionne. (Je n'avais jamais utilisé Match et against,
ça me donne l'occasion de lire la doc !)





@+
Pascal