Hola amigos. Soy Arturo Espinosa, de México.
Primeramente, me gustaría recibir recomendaciones de su parte de libros, en
inglés o español, para aprender SQL y de preferencia con Postgres. Yo sé
algo de SQL, pero cuando las cosas se complican sólo un poco, empiezo a
flaquear.
En segunda instancia, tengo una duda de SQL sobre Postgres, espero que me
puedan ayudar. He encontrado una solución a mi problema, pero no sé si hay
alguna manera más elegante de resolverlo. El mensaje se alarga un poco a
partir de aquí, pero he querido incluirles todo el material necesario para
que se les facilite emitir su opinión, que agradezco mucho.
En una parte de una base de datos que estoy desarrollando tengo tres
relaciones para representar personas con fotografía. Cada persona puede
tener una cantidad variable de fotografías, y cada fotografía es un archivo
que tiene asociado una fecha de creación. Aquí las partes relevantes de la
estructura (he omitido enumeraciones, secuencias y foreign key constraints):
CREATE TABLE public.persona (
persona_id INTEGER NOT NULL DEFAULT
nextval('public.persona_persona_id_seq'),
inst_id INTEGER NOT NULL,
type imr_persona_type NOT NULL,
prefix VARCHAR,
name VARCHAR NOT NULL,
paterno VARCHAR,
materno VARCHAR,
gender imr_gender,
remarks VARCHAR,
p_status imr_record_status NOT NULL,
CONSTRAINT persona_pk PRIMARY KEY (persona_id, inst_id)
);
CREATE TABLE public.persona_photo (
persona_id INTEGER NOT NULL,
file_id INTEGER NOT NULL,
inst_id INTEGER NOT NULL,
CONSTRAINT persona_photo_pk PRIMARY KEY (persona_id,
file_id, inst_id)
);
CREATE TABLE public.file (
file_id INTEGER NOT NULL DEFAULT
nextval('public.file_file_id_seq'),
inst_id INTEGER NOT NULL,
fname VARCHAR NOT NULL,
created TIMESTAMP NOT NULL,
mime_type_id INTEGER NOT NULL,
CONSTRAINT file_pk PRIMARY KEY (file_id, inst_id)
);
CREATE TABLE public.account (
persona_id INTEGER NOT NULL,
inst_id INTEGER NOT NULL,
username VARCHAR(20) NOT NULL,
passwd VARCHAR(32) NOT NULL,
account_type imr_account_type NOT NULL,
status charp_account_status NOT NULL,
CONSTRAINT account_pk PRIMARY KEY (persona_id, inst_id)
);
account es un subtipo de persona (relación uno-a-cero-o-uno).
Ahora, tengo un stored procedure para listar a los usuarios del sistema. La
idea es que de entre los resultados se reporte el nombre del archivo de su
fotografía (file.fname). Como cada persona puede tener varias fotografías
asociadas, se toma la de más reciente creación. También, he usado un LEFT
JOIN para que el campo "picture" de un usuario dado sea NULL en caso de que
no tenga ninguna fotografía asociada.
Aquí el query que preparé interactivamente con psql antes de meterlo en el
resto de mi stored procedure:
SELECT
persona_id,
inst_id,
fname
FROM
(SELECT
persona_id,
inst_id,
MAX(created)
FROM
persona_photo
NATURAL JOIN file
GROUP BY
inst_id,
persona_id
) AS q
NATURAL JOIN file;
Aquí el código de mi stored procedure:
--- Dado un usurio del sistema identificado por _uid, obtener la lista de
usuarios pertenecientes a su instancia cuyo
--- status no sea DELETED. Se devuelven el id del usuario, tipo de cuenta,
username, nombre opcional del archivo que
--- representa su fotografía más reciente, anotaciones y datos personales.
CREATE FUNCTION rp_user_list_get(_uid charp_user_id)
RETURNS TABLE( persona_id integer, type imr_account_type, username
varchar, picture varchar, remarks varchar,
prefix varchar, name varchar, paterno varchar,
materno varchar, status charp_account_status,
gender imr_gender ) AS
$BODY$
SELECT a.persona_id, a.account_type, a.username, f.fname, p.remarks,
p.prefix, p.name, p.paterno, p.materno, a.status, p.gender
FROM account AS a1
JOIN account AS a USING (inst_id)
JOIN persona AS p ON (a.persona_id = p.persona_id)
LEFT JOIN (SELECT persona_id, inst_id, fname
FROM (SELECT persona_id, inst_id, max(created)
FROM persona_photo
NATURAL JOIN file
GROUP BY inst_id, persona_id) AS
q
NATURAL JOIN file) AS f
ON (a.inst_id = f.inst_id AND a.persona_id =
f.persona_id)
WHERE a1.persona_id = $1 AND a.status <> 'DELETED';
$BODY$
LANGUAGE sql STABLE;
charp_user_id es un domain a tipo integer simplemente. El argumento _uid
que recibe la función es el account_id del usuario que está pidiendo la
lista de usuarios desde el frontend. La base de datos está diseñada para
soportar varias instancias del negocio a nivel de datos (algo muy típico de
plataformas SaaS), por lo que se usa la llave foránea inst_id en todas las
tablas para identificar a las instancias (hay una relación "inst" que no he
incluido aquí por brevedad).
La parte que no me gusta son los dos subqueries anidados para poder extraer
la fotografía más reciente, y que en el subquery 'q' haga yo un natural
join con file, para luego hacer otro en el subquery 'f'.
Alguna sugerencia? Es este un buen query, o hay algo más eficiente o que
exprese más claramente lo que se desea obtener?
Como me gustarí un recetario de SQL para aprender las mejores prácticas
para ciertos tipos de casos; si tienen recomendaciones de lecturas
parecidas, se las agradeceré.
Saludos,
Arturo
