Tengo la siguiente solicitud:

let artPerCall = 20
let artPerUser = 2
let start = req.params.page

let query = `
    SELECT * from 
    (
        SELECT a.*, row_to_json(u.*) as userinfo,
            row_number() over (partition by u.address order by a.date desc) as ucount
        FROM artworks a INNER JOIN users u ON a.address = u.address 
        WHERE a.flag != ($1) OR a.flag IS NULL
    ) t
    WHERE ucount <= ($2)
    ORDER BY date DESC 
    LIMIT ${artPerCall} OFFSET ${(start-1) * artPerCall}`

pool.query(query, ["ILLEGAL", artPerUser])
    .then(users => {
        if (users) {
            res.json(users.rows);
        }
    })
    .catch(err => {
        next(err);
    })

Llamado a través de una API con la siguiente ruta /artworks/paginate/1/20 donde (/artworks/paginate/{page}/20)

El resultado esperado es obtener 20 resultados por llamada con un máximo de 2 entradas por usuario.

El resultado actual: parece que devuelve solo 2 entradas por usuario como se esperaba, pero una vez que devuelve 2 para un usuario en una página, no hay más resultados para el mismo usuario en las páginas siguientes, incluso si tienen entradas.

¿Alguna idea de lo que me estoy perdiendo?

0
Christophe 3 mar. 2021 a las 16:34

1 respuesta

La mejor respuesta

Parece que devuelve solo 2 entradas por usuario como se esperaba, pero una vez que devuelve 2 para un usuario en una página, no hay más resultados para el mismo usuario en las páginas siguientes, incluso si tienen entradas.

Correcto, esto es lo que hace la consulta. Selecciona:

row_number() over (partition by u.address order by a.date desc) as ucount
...
WHERE ucount <= ($2)

Si el parámetro $ 2 se establece en 2 como está en su código de ejemplo, entonces seleccionará 2 entradas por usuario, no más, antes de ordenar y paginar. Si el usuario tiene más entradas, se filtrarán.

Si elimina "WHERE ucount <= ($ 2)", simplemente obtendrá todos los resultados ordenados por fecha, pero eso no suena como lo que desea.

Sin embargo, lo que creo que quieres lograr suena un poco complicado. Tampoco estoy seguro de que sea excelente para la usabilidad, ya que los resultados serían bastante aleatorios para el usuario. Por lo tanto, deberá describir exactamente lo que desea, con datos de ejemplo.

Por ejemplo, si desea evitar que un usuario publique una gran cantidad de elementos con la misma fecha empujando a todos los demás usuarios hacia abajo en los resultados de búsqueda, limitar la cantidad de resultados por usuario es una buena idea, pero tal vez un botón "más de esto usuario ... "sería una mejor opción que empujar los elementos de los usuarios a las páginas siguientes.

Suponga que solo tiene dos usuarios, el usuario1 publica 20 elementos con la fecha "hoy" y el usuario2 publicó 10 elementos ayer. ¿Quiere 2 elementos del usuario 1, luego 2 elementos del usuario 2, luego los 18 elementos restantes del usuario 1 y luego los 8 elementos restantes del usuario? ¿O se intercalarán entre sí de alguna manera, lo que hará que el orden de las fechas sea un poco aleatorio en los resultados?

EDITAR

Aquí hay una propuesta:

SELECT * from 
(
    SELECT *, 
        row_number() over (partition by user_id order by date desc) as ucount
    FROM artworks
) t
ORDER BY (ucount/3)::INTEGER ASC, date DESC 
LIMIT 20 OFFSET 0;

"(ucount / 3) :: INTEGER" es 0 para las dos primeras obras de arte de cada usuario, luego 1 para las 3 siguientes, luego 2 para las 3 siguientes, etc. Así que las 2 obras de arte más recientes de cada usuario terminan primero, luego las 3 obras de arte más recientes de cada usuario, etc.

Otro:

ORDER BY ucount<3 ASC, date DESC 

Esto colocará primero las 2 obras de arte más recientes de cada usuario, luego el resto simplemente se ordenará por fecha.

1
bobflux 3 mar. 2021 a las 15:46