php - recursive - Error SQL usando un CTE

mysqli prepare select

Question

Me aparece este error:

Error de consulta: tiene un error en su sintaxis SQL; revise el manual que corresponde a su versión de servidor de MariaDB para la sintaxis correcta para usar cerca de ''; WITH convs AS (seleccione c.id, c.título, c.seen, c.id_receiver, c.id_send'' en la línea 1)

cuando uso esta consulta:

$query = ";WITH convs AS (
        select c.id, c.title, c.seen, c.id_receiver, c.id_sender
        from conversations c
        )
        select id, title, seen, id_receiver, id_sender
        from convs
        where id_receiver = ''5''
        order by title desc limit 0,25";

$res = mysqli_query($connection ,$query);

Me estoy perdiendo de algo ? Su ayuda sería muy apreciada.

PD: minimicé la consulta para hacerlo simple en este contexto, si me ayudas a encontrar la solución, puedo tener otro problema con la consulta completa. Así que puedo volver con usted para obtener más ayuda. Gracias por adelantado.

EDITAR (PREGUNTA COMPLETA)

$query = "WITH convs AS (
        select c.id, c.title, c.seen, c.id_receiver, c.id_sender,
        (select max(date) from messages where id_conversation = c.id and id_user <> ''$iduser'') as last_msg,
        (select top 1 id_user from messages where id_conversation = c.id and id_user <> ''$iduser'' order by date desc) as last_user,
        (select count(distinct id_user) from messages where id_conversation = c.id) as nbruser,
        (select count(*) from messages where id_conversation = c.id) as nbrmsg,
        (select username from users where id = c.id_sender) as sender, (select username from users where id = c.id_receiver) as receiver,
        (select count(*) from deleted_conversations where id_user=''$iduser'' and id_conversation=c.id) as deleted,
        from conversations c
        )
        select id, title, seen, id_receiver, id_sender, receiver, sender, last_msg, last_user, deleted, nbruser, nbrmsg
        from convs
        where (id_receiver = ''$iduser'' or (id_sender == ''$iduser'' and nbruser > 1)) and deleted = 0
        order by last_msg desc limit $pageLimit,$REC_PER_PAGE";

Lo que me empujó a usar CTE es la necesidad de usar alias en where clause. Y como pueden ver, tengo muchos de ellos.

¿Me puede dar un ejemplo de cómo usar vistas / tablas temporales para lograr mi propósito?

Answer 1

CTE son bastante similares a Derived Tables:

select id, title, seen, id_receiver, id_sender, receiver, sender, last_msg, last_user, deleted, nbruser, nbrmsg
FROM
 (
        select c.id, c.title, c.seen, c.id_receiver, c.id_sender,
        (select max(date) from messages where id_conversation = c.id and id_user <> ''$iduser'') as last_msg,
        (select top 1 id_user from messages where id_conversation = c.id and id_user <> ''$iduser'' order by date desc) as last_user,
        (select count(distinct id_user) from messages where id_conversation = c.id) as nbruser,
        (select count(*) from messages where id_conversation = c.id) as nbrmsg,
        (select username from users where id = c.id_sender) as sender, (select username from users where id = c.id_receiver) as receiver,
        (select count(*) from deleted_conversations where id_user=''$iduser'' and id_conversation=c.id) as deleted,
        from conversations c
) as convs
where (id_receiver = ''$iduser'' or (id_sender == ''$iduser'' and nbruser > 1)) and deleted = 0
order by last_msg desc limit $pageLimit,$REC_PER_PAGE

Answer 2

MySQL / MariaDB no es compatible con CTE. Además, es completamente innecesario en este caso:

    select id, title, seen, id_receiver, id_sender
    from conversations c
    where id_receiver = ''5''
    order by ?? desc
    limit 0, 25;

Nota: También debe especificar la columna para el order by .

Para ejemplos más complejos, puede usar subconsultas, vistas y / o tablas temporales.