Último caso en ORDER BY ejecuta incorrectamente en postgres.
Tengo la siguiente función almacenada en Postgres. El último CASE WHEN en ORDER BY devuelve datos con un orden incorrecto. Sin embargo, el segundo CASE WHEN similar funciona como se esperaba.
He verificado dos veces que sortcolumn y sortdirection sean correctos. Cualquier ayuda es apreciada.
DB Fiddle: https://dbfiddle.uk/?rdbms=postgres_14&fiddle=32ad76d21a9cb97518b92acf58ddf803
CREATE OR REPLACE FUNCTION find_experts(BIGINT, _sort_column VARCHAR(256), _sort_direction VARCHAR(256))
RETURNS TABLE (
user_id BIGINT,
name VARCHAR(256),
email VARCHAR(256),
email_validated BOOLEAN,
is_verified BOOLEAN,
rating FLOAT,
is_promoted BOOLEAN,
registry_date TIMESTAMP,
picture VARCHAR(256),
price FLOAT,
biography VARCHAR(2028),
short_bio VARCHAR(1000),
timeslots VARCHAR(256)[],
timezone VARCHAR(256)
) AS $$
SELECT users.user_id, users.name, users.email, users.email_validated, users.is_verified, users.rating, users.is_promoted, users.registry_date,
user_settings.picture, user_settings.price, user_settings.biography, user_settings.short_bio, user_settings.timeslots, user_settings.timezone
FROM users
LEFT JOIN user_settings ON (users.user_id=user_settings.user_id)
WHERE users.user_id IN
(SELECT user_id FROM users_tech) AND
active_role='expert' AND price IS NOT NULL
ORDER BY
CASE WHEN _sort_column = 'rating' AND _sort_direction = 'DESC' THEN rating END DESC,
CASE WHEN _sort_column = 'price' AND _sort_direction = 'ASC' THEN price END ASC, rating ASC, registry_date ASC,
CASE WHEN _sort_column = 'price' AND _sort_direction = 'DESC' THEN price END DESC, rating DESC, registry_date ASC
OFFSET ($1 - 1) * 10
LIMIT 10
$$ LANGUAGE SQL;
La función se ejecuta así:
pool.query(`SELECT * FROM find_experts($1, $2, $3)`, [1, 'price', 'DESC'])
Resultado actual:
250
12
11
102
43
856
21
34
63
85
Resultado esperado:
856
250
102
85
63
43
34
21
12
11
davy.ai
El problema parece estar en el último CASE WHEN de la cláusula ORDER BY de la función almacenada. El resultado esperado no se está generando debido a esto. Para resolver el problema, necesitamos modificar la última condición CASE WHEN.
Actualmente, las dos últimas condiciones en la cláusula ORDER BY son:
El problema con esto es que si _sort_column es price y _sort_direction es DESC, solo se ordenará por price en orden descendente. Ignorará las columnas de rating y registry_date, lo cual no es lo que queremos.
Para solucionar este problema y obtener el resultado esperado, necesitamos modificar las dos últimas condiciones en la cláusula ORDER BY a:
Esto asegurará que cuando _sort_column sea price y _sort_direction sea DESC, primero se ordenará por price en orden descendente, pero también se asegurará de que rating y registry_date se ordenen correctamente.
Además, hay que tener en cuenta la adición de “NULLS LAST” en las últimas dos cláusulas. Esto se ha agregado para asegurarse de que los valores NULL se ordenen al final.
Con esta modificación, la función almacenada debería devolver ahora el resultado esperado.