Tengo tablas de rutas y criterios en PostgreSQL. Las rutas tienen múltiples criterios que deben cumplirse para ser seleccionados.

Tabla de ruta con (id, name), Tabla de criterios con (id, key, value, path_id)

Necesito crear una consulta SQL para seleccionar una sola ruta que cumpla con dos o más entradas de criterios (por ejemplo, edad de 30 años y sexo masculino). He intentado ANY como se indica a continuación.

SELECT p.*, array_agg(c.*) as criteria 
FROM paths as p 
INNER JOIN criteria as c ON c.path_id = p.id 
WHERE (c.key, c.value) = ANY (array[
    ("age","30"),
    ("gender","male") 
    ])
GROUP BY p.id

Pero esto obtiene una ruta cada vez que se cumple uno de los criterios (whether age = 30 or sex = "male" no ambos). Reemplacé ANY con ALL pero eso no devuelve ningún valor en absoluto.

0
Solalem 27 dic. 2019 a las 10:26

3 respuestas

La mejor respuesta

Si te seguí correctamente, debes mover las condiciones a la cláusula having en lugar de la cláusula where. Además, si desea que los registros satisfagan todas las condiciones, debe verificar cada condición individualmente:

select p.*, array_agg(c.key || ':' || c.value) as criteria
from paths p
inner join criteria c on c.path_id = p.id
group by p.id
having
    count(*) filter(where c.key = 'age' and c.value = '30') > 0
    and count(*) filter(where c.key = 'gender' and c.value = 'male') > 0

Esto también se puede expresar con dos condiciones EXISTS, que debería ser una consulta eficiente y con un índice en criteria(path_id, key, value) (pero luego pierde la capacidad de agregar todos los criterios):

select p.*
from paths p
where
    exists (
        select 1 
        from criteria c
        where c.path_id = p.id and c.key = 'age' and c.value = '30'
    )
    and exists (
        select 1 
        from criteria c
        where c.path_id = p.id and c.key = 'gender' and c.value = 'male'
    )
0
GMB 27 dic. 2019 a las 09:48

Como se trata de pares clave / valor, puede ser más fácil agregar los criterios en objetos JSON y usar las funciones json de Postgres para seleccionar los que desee:

select p.*, c.*
from paths as p 
  join (
     select path_id, jsonb_object_agg(key, value) as all_criteria
     from criteria
     group by path_id
     having jsonb_object_agg(key, value) @> '{"age": "30", "gender": "male"}'
  )  c on c.path_id = p.id 

Ejemplo en línea

1
a_horse_with_no_name 27 dic. 2019 a las 11:56

Una solución mas.

Intentemos seleccionar todos los criterios según sus condiciones:

SELECT c.path_id
  FROM criteria AS c
 WHERE (c.key = 'age' AND c.value = '30') OR
       (c.key = 'gender' AND c.value = 'male')
 GROUP BY c.path_id
HAVING count(*) = 2;

Todo lo que necesitamos es vincular los registros de ruta con exists:

SELECT *
  FROM path AS p
 WHERE EXISTS(
    SELECT 1
      FROM criteria AS c
     WHERE (c.key = 'age' AND c.value = '30') OR
           (c.key = 'gender' AND c.value = 'male')
       AND c.path_id = p.id
     GROUP BY c.path_id
    HAVING count(*) = 2
);

Espero que esto ayude.

0
Danila Ganchar 27 dic. 2019 a las 11:25