Level 102
You can get some of the way with this converter: https://sql2builder.github.io/
1 like
Oct 28, 2022
7
Level 1
I want SQL To Laravel Query Builder
hello. i want change SQL to Query Builder
SELECT c.*, (SELECT msg FROM chat_msg WHERE chatcon_id = c.id ORDER BY submit_date DESC LIMIT 1) AS msg,
(SELECT submit_date FROM chat_msg WHERE chatcon_id = c.id ORDER BY submit_date DESC LIMIT 1) AS msg_date,
a.name AS account_sender_full_name, a2.name AS account_receiver_full_name FROM chat_con c JOIN users a ON a.id = c.account_sender_id JOIN users a2 ON a2.id = c.account_receiver_id WHERE c.account_sender_id = 1 OR c.account_receiver_id = 4 GROUP BY c.id;
thank you:!
Level 1
@Sinnbeck don't work for me :(
DB::table('chat_con')
->select('chat_con.*', ' as msg', ' as msg_date', 'users.name as account_sender_full_name', 'users.name as account_receiver_full_name')
->join('users','users.id','=','chat_con.account_sender_id')
->join('users','users.id','=','chat_con.account_receiver_id')
->where('chat_con.account_sender_id','=',1)
->orWhere('chat_con.account_receiver_id','=',4)
->groupBy('chat_con.id')
->get();
Level 102
@wallock An error description would help.
Here I did it for you. This is the closest it can get. The rest you need to write yourself
DB::table('chat_con')
->select('chat_con.*', ' as msg', ' as msg_date', 'users.name as account_sender_full_name', 'users.name as account_receiver_full_name')
->join('users','users.id','=','chat_con.account_sender_id')
->join('users','users.id','=','chat_con.account_receiver_id')
->where('chat_con.account_sender_id','=',1)
->orWhere('chat_con.account_receiver_id','=',4)
->groupBy('chat_con.id')
->get();
If you are confused on how to do a specific part, then ask for help that specific part
Level 1
Illuminate\Database\QueryException
SQLSTATE[42000]: Syntax error or access violation: 1066 Not unique table/alias: 'users' (SQL: select `chat_con`.*, `` as `msg`, `` as `msg_date`, `users`.`name` as `account_sender_full_name`, `users`.`name` as `account_receiver_full_name` from `chat_con` inner join `users` on `users`.`id` = `chat_con`.`account_sender_id` inner join `users` on `users`.`id` = `chat_con`.`account_receiver_id` where `chat_con`.`account_sender_id` = 1 or `chat_con`.`account_receiver_id` = 4 group by `chat_con`.`id`)
Level 102
@wallock Yeah you would need to alias them :)
->join('users as u1','u1.id','=','chat_con.account_sender_id')
->join('users as u2','u2.id','=','chat_con.account_receiver_id')
Also be aware that you cannot select chat_con.* as all columns aren't in your group by https://sinnbeck.dev/posts/laravel-groupby-error
1 like
Level 55
@wallock I recommend to use https://laravel.com/docs/9.x/eloquent-relationships#has-one-of-many for msg and msg_date
ChatCon::select(
'chat_con.*',
'sender.name as account_sender_full_name',
'receiver.name as account_receiver_full_name'
)
->join('users as sender', 'sender.id', '=', 'chat_con.account_sender_id')
->join('users as receiver', 'receiver.id', '=', 'chat_con.account_receiver_id')
->with('latestMessage')
->where('account_sender_id', 1)
->where('account_receiver_id', 4)
->get();
at least this should give you an idea
2 likes
Level 1
@SilenceBringer nice ! thank you very much
Please or to participate in this conversation.