CODEWITHSUNDEEP
phpmysqlsqlcommon-table-expression
codeless
codeless

Reputation: 145

SQL error using a CTE

I'm getting this error :

Query Error : You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ';WITH convs AS ( select c.id, c.title, c.seen, c.id_receiver, c.id_send' at line 1

when i use this query :

$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);

Am i missing something ? Your help would be much appreciated.

PS : i minimised the query to make it simple for this context, if you help me find the solution, i may have another problem with the full query. So i might come back to you for more help. Thank's in advance.

EDIT (WHOLE QUERY)

$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";

What pushed me to use CTE is the need of using aliases in where clause. And as you can see i have many of them.

Can you give me an example of how to use views/temporary tables to achieve my purpose ?

Upvotes: 1

Views: 1562

Answers (2)

dnoeth
dnoeth

Reputation: 60482

CTEs are quite similar to 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

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270723

MySQL/MariaDB doesn't support CTEs. Plus, it is entirely unnecessary in this case:

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

Note: You need to specify the column for the order by as well.

For more complex examples, you can use subqueries, views, and/or temporary tables.

Upvotes: 5

Related Questions