Re: Une recherche sur plusieurs lignes ?

Bonjour à tous,

Je m'adresse à vous pour avoir un peu d'aide car en ce moment même, je
suis confronté à une situation assez compliquée. (A mon sens, peut-être
qu'elle est très simple en réalité :) ).

J'ai trois tables comme ceci:

+--------------+ +----------------+ +--------------+
| context_list | | context_values | | context_keys |
+--------------+ +----------------+ +--------------+
| SERIAL "id" | <-> | int "context" | |-> | SERIAL "id" |
| text "desc" | | int "key" | <-| | text "name" |
+--------------+ | text "value" | +--------------+
+----------------+

Elles décrivent des élements appelés "context".
- Dans "context_list", il y a "id", l'ID du context et sa description
- Dans "context_keys", il y a des clés de valeurs. Cette table permet de
mettre un ID dans "context_values", ce qui permet d'économiser de la
place et éviter les redondances de noms de clés.
- Dans "context_values", il y a les associations context/clés/valeurs.

Il peut il avoir plusieurs couples clé/valeur par context.

Maintenant, j'ai une description du context sous forme de tableau
associatif tel que:
Array(
[field1] => 'value1',
[field2] => 'value2'
)

J'ai donc dans ma base de données une ligne dans "context_list", deux
dans "context_keys" (field1, field2 pour le champ "name") et deux dans
"context_values".

Je souhaites, en une requête, obtenir l'ID du context qui as ces couples
clé/valeur. Est-ce possible ?
En une requête car je souhaiterais utiliser cette requête comme une
sous-requête.

Merci d'avance.
Cordialement, Samuel ROZE.

Bonjour,

Samuel ROZE <samuel(dot)roze(at)aliceadsl(dot)fr> writes:
> - Dans "context_values", il y a les associations context/clés/valeurs.

C'est ce qu'on appelle de l'EAV et tu atteinds les limites de ce que
l'on peut faire avec. N'étant pas d'humeur créative aujourd'hui, je me
contenterai de donner le lien wikipedia qui détaille :

http://en.wikipedia.org/wiki/Entity-attribute-value_model
--
dim

Le lundi 17 août 2009 à 15:25:11, Dimitri Fontaine a écrit :
> [...]
> Samuel ROZE <samuel(dot)roze(at)aliceadsl(dot)fr> writes:
> > - Dans "context_values", il y a les associations context/clés/valeurs.
>
> C'est ce qu'on appelle de l'EAV et tu atteinds les limites de ce que
> l'on peut faire avec. N'étant pas d'humeur créative aujourd'hui, je me
> contenterai de donner le lien wikipedia qui détaille :
>
> http://en.wikipedia.org/wiki/Entity-attribute-value_model

Et hop, j'en profite pour donner un lien vers la vidéo d'une conférence (en
anglais) que j'ai regardé ce matin :

Josh Berkus, « Ten ways to Wreck Your Databases ».
http://www.youtube.com/watch?v=uFLRc6y_O3s

Le son n'est pas terrible, mais les infos sont intéressantes. Il parle d'EAV
là-aussi, et pas en bien (je n'ai cassé aucun suspens vu le titre de sa
conférence :) ).

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Merci :)

En fait, il faudrais là passer par une fonction si je comprends bien les
exemples de solutions alternatives de EAV...

Dans ce cas, il y aurait-il une autre solution de structure de tables
afin d'obtenir le même résultat ?

Cordialement, Samuel.

Le lundi 17 août 2009 à 15:25 +0200, Dimitri Fontaine a écrit :
> Bonjour,
>
> Samuel ROZE <samuel(dot)roze(at)aliceadsl(dot)fr> writes:
> > - Dans "context_values", il y a les associations context/clés/valeurs.
>
> C'est ce qu'on appelle de l'EAV et tu atteinds les limites de ce que
> l'on peut faire avec. N'étant pas d'humeur créative aujourd'hui, je me
> contenterai de donner le lien wikipedia qui détaille :
>
> http://en.wikipedia.org/wiki/Entity-attribute-value_model
> --
> dim
>

Je me doutes bien que ce n'est pas une méthode extrêmement géniale en
effet :) Néanmoins, je vais prendre le temps de regarder (plutôt
écouter) cette présentation.

D'où mon intérrogation, il y a-t-il d'autres solutions, des workaround ?

Merci à vous.
Samuel.

Le lundi 17 août 2009 à 15:35 +0200, Guillaume Lelarge a écrit :
> Le lundi 17 août 2009 à 15:25:11, Dimitri Fontaine a écrit :
> > [...]
> > Samuel ROZE <samuel(dot)roze(at)aliceadsl(dot)fr> writes:
> > > - Dans "context_values", il y a les associations context/clés/valeurs.
> >
> > C'est ce qu'on appelle de l'EAV et tu atteinds les limites de ce que
> > l'on peut faire avec. N'étant pas d'humeur créative aujourd'hui, je me
> > contenterai de donner le lien wikipedia qui détaille :
> >
> > http://en.wikipedia.org/wiki/Entity-attribute-value_model
>
> Et hop, j'en profite pour donner un lien vers la vidéo d'une conférence (en
> anglais) que j'ai regardé ce matin :
>
> Josh Berkus, « Ten ways to Wreck Your Databases ».
> http://www.youtube.com/watch?v=uFLRc6y_O3s
>
> Le son n'est pas terrible, mais les infos sont intéressantes. Il parle d'EAV
> là-aussi, et pas en bien (je n'ai cassé aucun suspens vu le titre de sa
> conférence :) ).
>
>

Samuel ROZE a écrit :
> Bonjour à tous,
>
> Je m'adresse à vous pour avoir un peu d'aide car en ce moment même, je
> suis confronté à une situation assez compliquée. (A mon sens, peut-être
> qu'elle est très simple en réalité :) ).
>
> J'ai trois tables comme ceci:
>
> +--------------+ +----------------+ +--------------+
> | context_list | | context_values | | context_keys |
> +--------------+ +----------------+ +--------------+
> | SERIAL "id" | <-> | int "context" | |-> | SERIAL "id" |
> | text "desc" | | int "key" | <-| | text "name" |
> +--------------+ | text "value" | +--------------+
> +----------------+
>
> Elles décrivent des élements appelés "context".
> - Dans "context_list", il y a "id", l'ID du context et sa description
> - Dans "context_keys", il y a des clés de valeurs. Cette table permet de
> mettre un ID dans "context_values", ce qui permet d'économiser de la
> place et éviter les redondances de noms de clés.
> - Dans "context_values", il y a les associations context/clés/valeurs.
>
> Il peut il avoir plusieurs couples clé/valeur par context.
>
> Maintenant, j'ai une description du context sous forme de tableau
> associatif tel que:
> Array(
> [field1] => 'value1',
> [field2] => 'value2'
> )
>
> J'ai donc dans ma base de données une ligne dans "context_list", deux
> dans "context_keys" (field1, field2 pour le champ "name") et deux dans
> "context_values".
>
> Je souhaites, en une requête, obtenir l'ID du context qui as ces couples
> clé/valeur. Est-ce possible ?
> En une requête car je souhaiterais utiliser cette requête comme une
> sous-requête.
>
> Merci d'avance.
> Cordialement, Samuel ROZE.
>
>
>
Bonjour,

Je ne sais pas si j'ai réellement cerné ton problème en ce chaud après-midi
d'août, mais pourquoi ne pas créer une vue effectuant les jointures de ces
3 tables :

CREATE VIEW contextes AS
SELECT v.context as idcontext, c.desc as context, k.name as key, v.value
FROM context_values as v
INNER JOIN context_list as c ON v.context=c.id
INNER JOIN context_keys as k ON v.key=k.id

Vue que tu pourras interroger simplement :

SELECT idcontext, contexte FROM contextes
WHERE key='clé???' AND valeur='????'

ou pour en revenir à ta demande, simplement utiliser la
requête de création de la vue comme sous-requête :

SELECT v.context as idcontext, c.desc as context
FROM context_values as v
INNER JOIN context_list as c ON v.context=c.id
INNER JOIN context_keys as k ON v.key=k.id
WHERE k.name='clé????' AND v.value='valeur?????'

Par contre, en fonction du nombre d'enregistrements à prévoir,
mieux vaudrait indexer les champs 'text' sur lesquels s'effectueront
les tris.

Cordialement.

--
François Figarola
-- développeur --
=================
INTERNET CONSULT
Mas Guerido
6 rue Aristide Bergès
66330 CABESTANY

Tel 04.68.66.09.29
fax 04.68.66.99.50
* francois(dot)figarola(at)i-consult(dot)fr

Bonjour,

En fait, ce n'est pas ça le problème. Le problème, c'est que j'ai
plusieurs couples clé/valeur.

> SELECT v.context as idcontext, c.desc as context
> FROM context_values as v
> INNER JOIN context_list as c ON v.context=c.id
> INNER JOIN context_keys as k ON v.key=k.id
> WHERE k.name='clé????' AND v.value='valeur?????'

Cette requête fait une recherche pour uniquement un seul couple clé/valeur...

Samuel.

Le lundi 17 août 2009 à 16:25 +0200, François Figarola a écrit :
[...]
> Bonjour,
>
> Je ne sais pas si j'ai réellement cerné ton problème en ce chaud après-midi
> d'août, mais pourquoi ne pas créer une vue effectuant les jointures de ces
> 3 tables :
>
> CREATE VIEW contextes AS
> SELECT v.context as idcontext, c.desc as context, k.name as key, v.value
> FROM context_values as v
> INNER JOIN context_list as c ON v.context=c.id
> INNER JOIN context_keys as k ON v.key=k.id
>
> Vue que tu pourras interroger simplement :
>
> SELECT idcontext, contexte FROM contextes
> WHERE key='clé???' AND valeur='????'
>
> ou pour en revenir à ta demande, simplement utiliser la
> requête de création de la vue comme sous-requête :
>
> SELECT v.context as idcontext, c.desc as context
> FROM context_values as v
> INNER JOIN context_list as c ON v.context=c.id
> INNER JOIN context_keys as k ON v.key=k.id
> WHERE k.name='clé????' AND v.value='valeur?????'
>
> Par contre, en fonction du nombre d'enregistrements à prévoir,
> mieux vaudrait indexer les champs 'text' sur lesquels s'effectueront
> les tris.
>
> Cordialement.
>

Samuel ROZE wrote:

> Je souhaites, en une requête, obtenir l'ID du context qui as ces couples
> clé/valeur. Est-ce possible ?

Il faudrait que tu définisses sous quelle forme les couples clef/valeur de
cette requête se présentent, par exemple
ARRAY[('clef1', 'valeur1'), ('clef2', 'valeur2') ...] ?
ARRAY[ ['clef1', 'valeur1'], ['clef2', 'valeur2'] ...] ?

D'autre part tu as bien en entrée des couples clef/valeur et pas uniquement
des valeurs de clefs? Je demande parce que ça parait inhabituel et ça change
tout.

Et enfin quelle version de postgresql tu utilises?

Cordialement,
--
Daniel

Le lundi 17 août 2009, Samuel ROZE a écrit :
> Bonjour,
>
> En fait, ce n'est pas ça le problème. Le problème, c'est que j'ai
> plusieurs couples clé/valeur.
>
> > SELECT v.context as idcontext, c.desc as context
> > FROM context_values as v
> > INNER JOIN context_list as c ON v.context=c.id
> > INNER JOIN context_keys as k ON v.key=k.id
> > WHERE k.name='clé????' AND v.value='valeur?????'
>
> Cette requête fait une recherche pour uniquement un seul couple
> clé/valeur...

Je ne comprend pas tres bien.
Quels sont les critères de recherche ?

>
> Samuel.
>
> Le lundi 17 août 2009 à 16:25 +0200, François Figarola a écrit :
> [...]
>
> > Bonjour,
> >
> > Je ne sais pas si j'ai réellement cerné ton problème en ce chaud
> > après-midi d'août, mais pourquoi ne pas créer une vue effectuant les
> > jointures de ces 3 tables :
> >
> > CREATE VIEW contextes AS
> > SELECT v.context as idcontext, c.desc as context, k.name as key, v.value
> > FROM context_values as v
> > INNER JOIN context_list as c ON v.context=c.id
> > INNER JOIN context_keys as k ON v.key=k.id
> >
> > Vue que tu pourras interroger simplement :
> >
> > SELECT idcontext, contexte FROM contextes
> > WHERE key='clé???' AND valeur='????'
> >
> > ou pour en revenir à ta demande, simplement utiliser la
> > requête de création de la vue comme sous-requête :
> >
> > SELECT v.context as idcontext, c.desc as context
> > FROM context_values as v
> > INNER JOIN context_list as c ON v.context=c.id
> > INNER JOIN context_keys as k ON v.key=k.id
> > WHERE k.name='clé????' AND v.value='valeur?????'
> >
> > Par contre, en fonction du nombre d'enregistrements à prévoir,
> > mieux vaudrait indexer les champs 'text' sur lesquels s'effectueront
> > les tris.
> >
> > Cordialement.

----
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

Le 17 août 2009 16:33, Samuel ROZE <samuel(dot)roze(at)aliceadsl(dot)fr> a écrit :

> Bonjour,
>
> En fait, ce n'est pas ça le problème. Le problème, c'est que j'ai
> plusieurs couples clé/valeur.
>
> > SELECT v.context as idcontext, c.desc as context
> > FROM context_values as v
> > INNER JOIN context_list as c ON v.context=c.id
> > INNER JOIN context_keys as k ON v.key=k.id
> > WHERE k.name='clé????' AND v.value='valeur?????'
>
> Cette requête fait une recherche pour uniquement un seul couple
> clé/valeur...
>
> Samuel.
>

Je suis pas un spécialiste, mais, je pense, enfin j'ai compris que vous
vouliez rechercher plusieurs couples.

Moi, j'aurais naturellement fait quelque chose comme ça :
Pour une bijection (je sais pas si je peux employer ce terme là) clef =>
valeur
> SELECT v.context as idcontext, c.desc as context
> FROM context_values as v
> INNER JOIN context_list as c ON v.context=c.id
> INNER JOIN context_keys as k ON v.key=k.id
> WHERE (k.name <http://k.name/>='clé1????' AND v.value='valeur1?????')
OR (k.name <http://k.name/>='clé2????' AND v.value='valeur2?????') ...

Si non
> SELECT v.context as idcontext, c.desc as context
> FROM context_values as v
> INNER JOIN context_list as c ON v.context=c.id
> INNER JOIN context_keys as k ON v.key=k.id
> WHERE (k.name <http://k.name/>='clé1????' OR
v.value='valeur2?????'...) AND (k.name <http://k.name/>='clé2????' OR
v.value='valeur1?????'...)

Moi je passerais sois par le langage qui génère la requête sois par une
commande imbriqué pour lister les différentes possibilités (je ne la connais
pas).

Je peux me tromper.

Le lundi 17 août 2009 à 15:47:04, Samuel ROZE a écrit :
> Je me doutes bien que ce n'est pas une méthode extrêmement géniale en
> effet :) Néanmoins, je vais prendre le temps de regarder (plutôt
> écouter) cette présentation.
>
> D'où mon intérrogation, il y a-t-il d'autres solutions, des workaround ?
>

Avant de pouvoir réellement répondre, serait-il possible d'avoir un dump
exemple? avec quelques lignes pour chaque table, histoire de bien comprendre
le contenu de chacune et donc comment structurer la requête.

Merci.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Bonjour,

En concordance avec mon exemple précédent, voici les trois tables avec
quelques enregistrements:

CREATE TABLE context_keys (
id SERIAL,
name text NOT NULL
);
INSERT INTO context_keys (id, name) VALUES
(1, 'label1'),
(2, 'label2');

CREATE TABLE context_list (
id SERIAL
);
INSERT INTO context_list (id) VALUES
(1),
(2);

CREATE TABLE context_values (
context integer NOT NULL,
key integer NOT NULL,
value text NOT NULL
);
INSERT INTO context_values (context, key, value) VALUES
(1, 1, 'somethink'),
(1, 2, 'value2'),
(2, 1, 'value3'),
(2, 2, 'value4');

Je veux avoir l'ID du context (clé "id" de la table "context_list") qui
a la clé "label1" = 'value3' ET "label2" = 'value4'. C'est l'ID #2. Mais
comment avoir ce résultat en une seule requête.. Bonne question :-)

Merci à vous !
Cordialement, Samuel ROZE.

Le mercredi 19 août 2009 à 00:00 +0200, Guillaume Lelarge a écrit :
> Le lundi 17 août 2009 à 15:47:04, Samuel ROZE a écrit :
> > Je me doutes bien que ce n'est pas une méthode extrêmement géniale en
> > effet :) Néanmoins, je vais prendre le temps de regarder (plutôt
> > écouter) cette présentation.
> >
> > D'où mon intérrogation, il y a-t-il d'autres solutions, des workaround ?
> >
>
> Avant de pouvoir réellement répondre, serait-il possible d'avoir un dump
> exemple? avec quelques lignes pour chaque table, histoire de bien comprendre
> le contenu de chacune et donc comment structurer la requête.
>
> Merci.
>
>
> --
> Guillaume.
> http://www.postgresqlfr.org
> http://dalibo.com
>

Le mercredi 19 août 2009, Samuel ROZE a écrit :
> Bonjour,
>
> En concordance avec mon exemple précédent, voici les trois tables avec
> quelques enregistrements:

quel est le serveur postgresql utilisé ? (8.4 ?)

>
> CREATE TABLE context_keys (
> id SERIAL,
> name text NOT NULL
> );
> INSERT INTO context_keys (id, name) VALUES
> (1, 'label1'),
> (2, 'label2');
>
>
> CREATE TABLE context_list (
> id SERIAL
> );
> INSERT INTO context_list (id) VALUES
> (1),
> (2);
>
>
> CREATE TABLE context_values (
> context integer NOT NULL,
> key integer NOT NULL,
> value text NOT NULL
> );
> INSERT INTO context_values (context, key, value) VALUES
> (1, 1, 'somethink'),
> (1, 2, 'value2'),
> (2, 1, 'value3'),
> (2, 2, 'value4');
>
>
> Je veux avoir l'ID du context (clé "id" de la table "context_list") qui
> a la clé "label1" = 'value3' ET "label2" = 'value4'. C'est l'ID #2. Mais
> comment avoir ce résultat en une seule requête.. Bonne question :-)
>
> Merci à vous !
> Cordialement, Samuel ROZE.
>
> Le mercredi 19 août 2009 à 00:00 +0200, Guillaume Lelarge a écrit :
> > Le lundi 17 août 2009 à 15:47:04, Samuel ROZE a écrit :
> > > Je me doutes bien que ce n'est pas une méthode extrêmement géniale en
> > > effet :) Néanmoins, je vais prendre le temps de regarder (plutôt
> > > écouter) cette présentation.
> > >
> > > D'où mon intérrogation, il y a-t-il d'autres solutions, des workaround
> > > ?
> >
> > Avant de pouvoir réellement répondre, serait-il possible d'avoir un dump
> > exemple? avec quelques lignes pour chaque table, histoire de bien
> > comprendre le contenu de chacune et donc comment structurer la requête.
> >
> > Merci.

--
----
Cédric Villemain
Administrateur de Base de Données
Cel: +33 (0)6 74 15 56 53
http://dalibo.com - http://dalibo.org

Le mercredi 19 août 2009 à 00:52:51, Samuel ROZE a écrit :
> Bonjour,
>
> En concordance avec mon exemple précédent, voici les trois tables avec
> quelques enregistrements:
>
> CREATE TABLE context_keys (
> id SERIAL,
> name text NOT NULL
> );
> INSERT INTO context_keys (id, name) VALUES
> (1, 'label1'),
> (2, 'label2');
>
>
> CREATE TABLE context_list (
> id SERIAL
> );
> INSERT INTO context_list (id) VALUES
> (1),
> (2);
>
>
> CREATE TABLE context_values (
> context integer NOT NULL,
> key integer NOT NULL,
> value text NOT NULL
> );
> INSERT INTO context_values (context, key, value) VALUES
> (1, 1, 'somethink'),
> (1, 2, 'value2'),
> (2, 1, 'value3'),
> (2, 2, 'value4');
>
>
> Je veux avoir l'ID du context (clé "id" de la table "context_list") qui
> a la clé "label1" = 'value3' ET "label2" = 'value4'. C'est l'ID #2. Mais
> comment avoir ce résultat en une seule requête.. Bonne question :-)
>

Ceci est la première chose qui me vient en tête:

select cv1.context
from context_values as cv1, context_values as cv2
where cv1.key in (select id from context_keys where name='label1')
and cv1.value='value3'
and cv2.key in (select id from context_keys where name='label2')
and cv2.value='value4'
and cv1.context=cv2.context;

À noter qu'en 8.4, on ferait plutôt ça:

with
ck1 as (select id from context_keys where name='label1'),
ck2 as (select id from context_keys where name='label2')
select cv1.context
from context_values as cv1, context_values as cv2, ck1, ck2
where cv1.key=ck1.id and cv1.value='value3'
and cv2.key=ck2.id and cv2.value='value4'
and cv1.context=cv2.context;

ce qui n'est que deux fois plus rapide.

Ce qui me fait penser que ceci est une version améliorée de la première:

select cv1.context
from context_values as cv1, context_values as cv2,
context_keys as ck1, context_keys as ck2
where cv1.key=ck1.id and cv1.value='value3'
and cv2.key=ck2.id and cv2.value='value4'
and cv1.context=cv2.context
and ck1.name='label1'
and ck2.name='label2';

Même perfs que la requête CTE.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

Eh non, 8.3 ! :)

Le mercredi 19 août 2009 à 02:13 +0200, Cédric Villemain a écrit :
> Le mercredi 19 août 2009, Samuel ROZE a écrit :
> > Bonjour,
> >
> > En concordance avec mon exemple précédent, voici les trois tables avec
> > quelques enregistrements:
>
> quel est le serveur postgresql utilisé ? (8.4 ?)
>
> >
> > CREATE TABLE context_keys (
> > id SERIAL,
> > name text NOT NULL
> > );
> > INSERT INTO context_keys (id, name) VALUES
> > (1, 'label1'),
> > (2, 'label2');
> >
> >
> > CREATE TABLE context_list (
> > id SERIAL
> > );
> > INSERT INTO context_list (id) VALUES
> > (1),
> > (2);
> >
> >
> > CREATE TABLE context_values (
> > context integer NOT NULL,
> > key integer NOT NULL,
> > value text NOT NULL
> > );
> > INSERT INTO context_values (context, key, value) VALUES
> > (1, 1, 'somethink'),
> > (1, 2, 'value2'),
> > (2, 1, 'value3'),
> > (2, 2, 'value4');
> >
> >
> > Je veux avoir l'ID du context (clé "id" de la table "context_list") qui
> > a la clé "label1" = 'value3' ET "label2" = 'value4'. C'est l'ID #2. Mais
> > comment avoir ce résultat en une seule requête.. Bonne question :-)
> >
> > Merci à vous !
> > Cordialement, Samuel ROZE.
> >
> > Le mercredi 19 août 2009 à 00:00 +0200, Guillaume Lelarge a écrit :
> > > Le lundi 17 août 2009 à 15:47:04, Samuel ROZE a écrit :
> > > > Je me doutes bien que ce n'est pas une méthode extrêmement géniale en
> > > > effet :) Néanmoins, je vais prendre le temps de regarder (plutôt
> > > > écouter) cette présentation.
> > > >
> > > > D'où mon intérrogation, il y a-t-il d'autres solutions, des workaround
> > > > ?
> > >
> > > Avant de pouvoir réellement répondre, serait-il possible d'avoir un dump
> > > exemple? avec quelques lignes pour chaque table, histoire de bien
> > > comprendre le contenu de chacune et donc comment structurer la requête.
> > >
> > > Merci.
>
>

Bonjour,

Tout d'abord merci de vous pencher sur mon problème. C'est intéressant,
seulement, dans mon exemple il y a deux couples clé/valeur à rechercher.
Or il se peut que j'en ai un ou cinq...ou dix.. (très très rare mais
quand même)

Merci !

Le mercredi 19 août 2009 à 08:39 +0200, Guillaume Lelarge a écrit :
> Le mercredi 19 août 2009 à 00:52:51, Samuel ROZE a écrit :
> > Bonjour,
> >
> > En concordance avec mon exemple précédent, voici les trois tables avec
> > quelques enregistrements:
> >
> > CREATE TABLE context_keys (
> > id SERIAL,
> > name text NOT NULL
> > );
> > INSERT INTO context_keys (id, name) VALUES
> > (1, 'label1'),
> > (2, 'label2');
> >
> >
> > CREATE TABLE context_list (
> > id SERIAL
> > );
> > INSERT INTO context_list (id) VALUES
> > (1),
> > (2);
> >
> >
> > CREATE TABLE context_values (
> > context integer NOT NULL,
> > key integer NOT NULL,
> > value text NOT NULL
> > );
> > INSERT INTO context_values (context, key, value) VALUES
> > (1, 1, 'somethink'),
> > (1, 2, 'value2'),
> > (2, 1, 'value3'),
> > (2, 2, 'value4');
> >
> >
> > Je veux avoir l'ID du context (clé "id" de la table "context_list") qui
> > a la clé "label1" = 'value3' ET "label2" = 'value4'. C'est l'ID #2. Mais
> > comment avoir ce résultat en une seule requête.. Bonne question :-)
> >
>
> Ceci est la première chose qui me vient en tête:
>
> select cv1.context
> from context_values as cv1, context_values as cv2
> where cv1.key in (select id from context_keys where name='label1')
> and cv1.value='value3'
> and cv2.key in (select id from context_keys where name='label2')
> and cv2.value='value4'
> and cv1.context=cv2.context;
>
> À noter qu'en 8.4, on ferait plutôt ça:
>
> with
> ck1 as (select id from context_keys where name='label1'),
> ck2 as (select id from context_keys where name='label2')
> select cv1.context
> from context_values as cv1, context_values as cv2, ck1, ck2
> where cv1.key=ck1.id and cv1.value='value3'
> and cv2.key=ck2.id and cv2.value='value4'
> and cv1.context=cv2.context;
>
> ce qui n'est que deux fois plus rapide.
>
> Ce qui me fait penser que ceci est une version améliorée de la première:
>
> select cv1.context
> from context_values as cv1, context_values as cv2,
> context_keys as ck1, context_keys as ck2
> where cv1.key=ck1.id and cv1.value='value3'
> and cv2.key=ck2.id and cv2.value='value4'
> and cv1.context=cv2.context
> and ck1.name='label1'
> and ck2.name='label2';
>
> Même perfs que la requête CTE.
>
>

Bonjour Samuel,

c'est un peu tiré par les cheveux et pas forcément très performant si
jamais tu as beaucoup de rows, mais c'est facilement adaptable à
plusieurs couples clés/valeurs:

SELECT
context_values.context
FROM
context_keys
INNER JOIN context_values ON context_values.key = context_keys.id
WHERE TRUE
AND context_keys.name IN ('label1', 'label2')
AND context_values.value IN ('value3', 'value4')
AND ARRAY[context_keys.name, context_values.value] IN (
ARRAY['label1', 'value3'],
ARRAY['label2', 'value4']
)
GROUP BY
context_values.context
HAVING
COUNT(DISTINCT ARRAY[context_keys.name, context_values.value]) = 2
;

Ludo

2009/8/19 Samuel ROZE <samuel(dot)roze(at)aliceadsl(dot)fr>:
> Bonjour,
>
> Tout d'abord merci de vous pencher sur mon problème. C'est intéressant,
> seulement, dans mon exemple il y a deux couples clé/valeur à rechercher.
> Or il se peut que j'en ai un ou cinq...ou dix.. (très très rare mais
> quand même)
>
> Merci !
>
> Le mercredi 19 août 2009 à 08:39 +0200, Guillaume Lelarge a écrit :
>> Le mercredi 19 août 2009 à 00:52:51, Samuel ROZE a écrit :
>> > Bonjour,
>> >
>> > En concordance avec mon exemple précédent, voici les trois tables avec
>> > quelques enregistrements:
>> >
>> > CREATE TABLE context_keys (
>> >   id SERIAL,
>> >   name text NOT NULL
>> > );
>> > INSERT INTO context_keys (id, name) VALUES
>> > (1, 'label1'),
>> > (2, 'label2');
>> >
>> >
>> > CREATE TABLE context_list (
>> >   id SERIAL
>> > );
>> > INSERT INTO context_list (id) VALUES
>> > (1),
>> > (2);
>> >
>> >
>> > CREATE TABLE context_values (
>> >   context integer NOT NULL,
>> >   key integer NOT NULL,
>> >   value text NOT NULL
>> > );
>> > INSERT INTO context_values (context, key, value) VALUES
>> > (1, 1, 'somethink'),
>> > (1, 2, 'value2'),
>> > (2, 1, 'value3'),
>> > (2, 2, 'value4');
>> >
>> >
>> > Je veux avoir l'ID du context (clé "id" de la table "context_list") qui
>> > a la clé "label1" = 'value3' ET "label2" = 'value4'. C'est l'ID #2. Mais
>> > comment avoir ce résultat en une seule requête.. Bonne question :-)
>> >
>>
>> Ceci est la première chose qui me vient en tête:
>>
>> select cv1.context
>> from context_values as cv1, context_values as cv2
>> where cv1.key in (select id from context_keys where name='label1')
>>         and cv1.value='value3'
>>   and cv2.key in (select id from context_keys where name='label2')
>>         and cv2.value='value4'
>>   and cv1.context=cv2.context;
>>
>> À noter qu'en 8.4, on ferait plutôt ça:
>>
>> with
>>   ck1 as (select id from context_keys where name='label1'),
>>   ck2 as (select id from context_keys where name='label2')
>> select cv1.context
>> from context_values as cv1, context_values as cv2, ck1, ck2
>> where cv1.key=ck1.id and cv1.value='value3'
>>   and cv2.key=ck2.id and cv2.value='value4'
>>   and cv1.context=cv2.context;
>>
>> ce qui n'est que deux fois plus rapide.
>>
>> Ce qui me fait penser que ceci est une version améliorée de la première:
>>
>> select cv1.context
>> from context_values as cv1, context_values as cv2,
>>      context_keys as ck1, context_keys as ck2
>> where cv1.key=ck1.id and cv1.value='value3'
>>   and cv2.key=ck2.id and cv2.value='value4'
>>   and cv1.context=cv2.context
>>   and ck1.name='label1'
>>   and ck2.name='label2';
>>
>> Même perfs que la requête CTE.
>>
>>
>
>
> --
> Sent via pgsql-fr-generale mailing list (pgsql-fr-generale(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-fr-generale
>

Le mercredi 19 août 2009 à 11:39:16, Samuel ROZE a écrit :
> [...]
> Tout d'abord merci de vous pencher sur mon problème. C'est intéressant,
> seulement, dans mon exemple il y a deux couples clé/valeur à rechercher.
> Or il se peut que j'en ai un ou cinq...ou dix.. (très très rare mais
> quand même)
>

C'est exactement ce que disait Dimitri en début de thread. Le module EAV est
une galère à gérer. Mieux vaut revoir le schéma pour sortir de ce système.

--
Guillaume.
http://www.postgresqlfr.org
http://dalibo.com

C'est bien ce que je pensais...

Avez-vous quelques idées/suggestions pour un nouveau schéma qui me
permetterais la même chose ?

Samuel.

Le mercredi 19 août 2009 à 12:23 +0200, Guillaume Lelarge a écrit :
> Le mercredi 19 août 2009 à 11:39:16, Samuel ROZE a écrit :
> > [...]
> > Tout d'abord merci de vous pencher sur mon problème. C'est intéressant,
> > seulement, dans mon exemple il y a deux couples clé/valeur à rechercher.
> > Or il se peut que j'en ai un ou cinq...ou dix.. (très très rare mais
> > quand même)
> >
>
> C'est exactement ce que disait Dimitri en début de thread. Le module EAV est
> une galère à gérer. Mieux vaut revoir le schéma pour sortir de ce système.
>
>

Bonjour,

Une solution possible :

-- Création des tables et des liens

CREATE TABLE context_keys
(
ck_id serial NOT NULL,
ck_name text NOT NULL,
CONSTRAINT ck_pk PRIMARY KEY (ck_id)
)

CREATE TABLE context_list
(
cl_id serial NOT NULL,
CONSTRAINT cl_pk_id PRIMARY KEY (cl_id)
)

CREATE TABLE context_values
(
cv_cl_id integer NOT NULL,
cv_ck_id integer NOT NULL,
cv_value text NOT NULL,
CONSTRAINT cv_pk PRIMARY KEY (cv_cl_id, cv_ck_id),
CONSTRAINT ck_id FOREIGN KEY (cv_ck_id)
REFERENCES context_keys (ck_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION,
CONSTRAINT cl_id FOREIGN KEY (cv_cl_id)
REFERENCES context_list (cl_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)

-- création d'une vue pour l'exploitation

CREATE OR REPLACE VIEW vue_global AS
SELECT context_values.cv_cl_id AS context,
context_values.cv_ck_id AS key,
context_keys.ck_name AS name,
context_values.cv_value AS value
FROM context_values
JOIN context_keys ON context_keys.ck_id = context_values.cv_ck_id;

-- création d'une fonction renvoyant le context d'un couple

CREATE OR REPLACE FUNCTION get_context(n text, v text) RETURNS integer AS
$BODY$
BEGIN
RETURN (SELECT context FROM vue_global WHERE name = n AND value = v);
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE STRICT

-- exemple de requete répondant à la question de base (cad connaitre un
"context" pour une liste connue de couple)

select distinct context
from vue_global
where context = get_context('label1', 'value3')
and context = get_context('label2', 'value4')

A voir ...
Georges SIMON

Samuel ROZE wrote:

> Tout d'abord merci de vous pencher sur mon problème. C'est intéressant,
> seulement, dans mon exemple il y a deux couples clé/valeur à rechercher.
> Or il se peut que j'en ai un ou cinq...ou dix.. (très très rare mais
> quand même)

Tu pourrais faire N requêtes simples liées entre elles par un INTERSECT, du
style:
SELECT cid FROM cv JOIN ck ON ck.id=cv.key WHERE name='clef1' AND
value='val1'
INTERSECT
SELECT cid FROM cv JOIN ck ON ck.id=cv.key WHERE name='clef2' and
value='val2'
INTERSECT
SELECT cid FROM cv JOIN ck ON ck.id=cv.key WHERE name='clef3' and
value='val3'
etc...
où cid est l'id de contexte et cv et ck sont context_values et context_keys
(je raccourcis).
A mon avis ça fonctionnera bien mais ça reste du SQL généré. Impossible d'en
faire une vue par exemple.

Maintenant si l'objectif était d'avoir du SQL fixe ça doit être faisable en
utilisant les tableaux, si les (clef,valeur) arrivent en tableau de valeurs
de type couple.
Il faudrait créer un type couple:
CREATE TYPE couple AS (keyname text, value text);

Ensuite Il faudrait, dans une sous-requête, sortir chaque id de contexte et
un tableau de tous les couples qui lui correspondent, du genre:
SELECT id,
ARRAY(select (name,value)::couple
from cv JOIN ck ON ck.id=cv.key WHERE cid=c.id) AS arr
FROM (select id FROM context_list) c
GROUP BY id

Ensuite ne garder que les id pour lesquels le tableau en entrée est contenu
dans le tableau de tous les couples de cet id, grâce au prédicat:
tableau_contenu <@ tableau_contenant

Ca donnerait:
SELECT cid FROM
(requête du dessus) s
WHERE ARRAY[('clef1','val1')::couple, ('clef2','val2')::couple] <@ arr;

Il me semble que ça répond au problème. Au niveau des performances par
contre, s'il y a beaucoup de couples dans la base, ça peut être lent. Il
devrait être possible d'optimiser en commençant à filtrer les clefs dès la
sous-requête, mais il faut s'assurer que la version de base fonctionne avant
d'optimiser.

Cordialement,
--
Daniel

Georges Simon wrote:

> -- création d'une fonction renvoyant le context d'un couple
>
> CREATE OR REPLACE FUNCTION get_context(n text, v text) RETURNS integer AS
> $BODY$
> BEGIN
> RETURN (SELECT context FROM vue_global WHERE name = n AND value = v);
> END;
> $BODY$
> LANGUAGE 'plpgsql' VOLATILE STRICT

Mais ça provoquera une erreur dès qu'il y aura un même couple sous deux
contextes différents.

Cordialement,
--
Daniel

Bonjour,

Si a un couple peut correspondre plusieurs contexte, il y a effectivement
erreur dans la fonction.
Le plus simple reste une intersection de plusieurs requetes.

exemple :

select context from vue_global where name = 'label1' and value = 'value3'
INTERSECT
select context from vue_global where name = 'label2' and value = 'value4'

A+
Georges SIMON

En effet.

En tous les cas, merci à tous d'avoir essayer. Je vais utiliser des
intersections, ça va être bien :)

Merci !
Samuel ROZE.

Le mercredi 19 août 2009 à 15:45 +0200, georges(dot)simon(at)sermeta(dot)com a
écrit :
> Bonjour,
>
> Si a un couple peut correspondre plusieurs contexte, il y a effectivement
> erreur dans la fonction.
> Le plus simple reste une intersection de plusieurs requetes.
>
> exemple :
>
> select context from vue_global where name = 'label1' and value = 'value3'
> INTERSECT
> select context from vue_global where name = 'label2' and value = 'value4'
>
>
> A+
> Georges SIMON
>
>

Samuel ROZE a écrit :
> En effet.
>
> En tous les cas, merci à tous d'avoir essayer. Je vais utiliser des
> intersections, ça va être bien :)
>
> Merci !
> Samuel ROZE.
>
Sinon, pour ton problème particulier, tu peux également
utiliser la contrib hstore (des développeurs de tsearch
Oleg Bartunov et Teodor Sigaev) disponible depuis la
version 8.2 (cf. http://www.sai.msu.su/~megera/wiki/Hstore)
laquelle permet de gérer spécifiquement des couples
clés => valeurs, tout en permettant l'utilisation d'index(s).

Cordialement.

--
François Figarola
-- développeur --
=================
INTERNET CONSULT
Mas Guerido
6 rue Aristide Bergès
66330 CABESTANY

Tel 04.68.66.09.29
fax 04.68.66.99.50
* francois(dot)figarola(at)i-consult(dot)fr