"Select distinct case when..." query conversion from raw PHP to Laravel query builder-CodePudding

I have a table in my database named 'messages' and the basic columns are message_from,message_to, message. I need to list the other users who sent messages to me or I sent messages to whom. And I am trying to convert a raw PHP query in Laravel.

Raw Query:

$user_id = 'my_user_id';

Select Distinct case when message_from = '$user_id' then message_to else message_from end as
contact from messages where '$user_id' in (message_from, message_to) ORDER BY id DESC

My Conversion to Laravel:

$user_id = Auth::user()->user_id;

$messages = DB::table('messages')
            ->selectRaw("(SELECT DISTINCT CASE WHEN message_from = '$user_id' THEN message_to 
              ELSE message_from END as messages FROM messages WHERE '$user_id' IN 
              (message_from,message_to)")
            ->latest()
            ->get();

But unfortunately, it's not working. I am getting this error:

SQLSTATE[42000]: Syntax error or access violation: 1064 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
'from `messages` order by `created_at` desc' at line 1 (SQL: select (SELECT DISTINCT CASE WHEN 
 message_from = 'client-20220215L0pu1xDTy9S3SKf8055449' THEN message_to ELSE message_from END as 
 messages WHERE 'client-20220215L0pu1xDTy9S3SKf8055449' IN (message_from,message_to) from 
`messages` order by `created_at` desc)

CodePudding user response：

What is wrong in your current query is you slammed the entire query in the select part of the query builder, which will not work because Laravel will try to build the rest of the query around it so will look like SELECT (SELECT CASE...FROM...WHERE) FROM ... which is clearly not the query you wanted it to perform.

Your query would look better like:

DB::table('messages')
   ->selectRaw("CASE WHEN message_from = ? THEN message_to 
              ELSE message_from END as messages", [ $user_id ])
            
            ->where('message_from', $user_id) 
            ->orWhere('message_to', $user_id)
            ->latest()
            ->distinct()
            ->get();

Note that I changed the WHERE $user_id IN (message_from, message_to) to WHERE message_from = $user_id OR message_to = $user_id because the other syntax can also only be done with a raw query.

Also notice the parameters used in selectRaw rather than injecting the user_id which may be dangerous (depending on context it is unclear whether $user_id comes from user input directly or indirectly).