programación - PostgreSQL-ordenar por una matriz

postgresql tutorial

Question

Tengo 2 tablas: curso que contiene la identificación y el nombre de los cursos y tagCourse que contiene etiquetas para cada curso.

course                    tagcourse
------------            ----------------
PK id_course            PK tag
   name                 PK, FK id_course

Me gustaría escribir una función que busque cursos por un conjunto de etiquetas determinado y los devuelva ordenados por cantidad de etiquetas coincidentes. Sin embargo, no sé cómo escribirlo correctamente y de manera eficiente. Por favor, ayúdame.

es decir.

CREATE OR REPLACE FUNCTION searchByTags(tags varchar[])
RETURNS SETOF.....
  RETURN QUERY SELECT * FROM course c INNER JOIN tagcourse tc ON c.id_course = tc.id_course
  WHERE ???  ORDER BY ???

END....

Answer 1

create or replace function searchByTags(tags varchar[])
returns table (id_course integer, name text, quantitiy integer)
as $$
    select *
    from (
        select c.id_course, c.name, count(*) quantity
        from
            course c
            inner join
            tagcourse tc on c.id_course = tc.id_course
            inner join
            unnest(tags) s(tag) on s.tag = tc.tag
        group by c.id_course, c.name
    ) s
    order by quantity desc, name
    ;
$$ language sql;

Answer 2

CREATE OR REPLACE FUNCTION search_by_tags(tags varchar[])
  RETURNS TABLE (id_course integer, name text, tag_ct integer) AS
$func$
   SELECT id_course, c.name, ct.tag_ct
   FROM  (
      SELECT tc.id_course, count(*)::int AS tag_ct
      FROM   unnest($1) x(tag)
      JOIN   tagcourse tc USING (tag)
      GROUP  BY 1                      -- first aggregate ..
      ) AS ct
   JOIN   course c USING (id_course)   -- .. then join
   ORDER  BY ct.tag_ct DESC            --  more columns to break ties?
$func$  LANGUAGE sql;

• Utilice unnest() para generar una tabla a partir de su matriz de entrada, como ya lo demostró @Clodoaldo .

• No necesita plpgsql para esto. Más simple con una función SQL simple.

• Uso unnest($1) (con parámetro posicional) en lugar de unnest(tags) , ya que el último solo es válido para PostgreSQL 9.2+ en funciones de SQL (a diferencia de plpgsql). Cito el manual aquí :

En el enfoque numérico más antiguo, los argumentos se referencian utilizando la sintaxis $n : $1 refiere al primer argumento de entrada, $2 al segundo, y así sucesivamente. Esto funcionará independientemente de que el argumento particular haya sido declarado o no con un nombre.

• count() devuelve bigint . Necesita lanzarlo a int para que coincida con el tipo de devolución declarada o declarar la columna devuelta como bigint para comenzar.

• Ocasión perfecta para simplificar un poco la sintaxis con USING (equi-join): USING (tag) lugar de ON tc.tag = c.tag .

• Es regularmente más rápido agregar primero , luego unirse a otra mesa. Reduce las operaciones de unión necesarias.
  Según la pregunta de @Clodoaldo en los comentarios , aquí hay un SQL Fiddle para demostrar la diferencia.

• OTOH, si agregas después de la unión, no necesitas una subconsulta. Más corto, pero probablemente más lento:

SELECT c.id_course, c.name, count(*)::int AS tag_ct
FROM   unnest($1) x(tag)
JOIN   tagcourse tc USING (tag)
JOIN   course     c USING (id_course)
GROUP  BY 1
ORDER  BY 3 DESC;  --  more columns to break ties?