Here is my SQL query; I want to convert it to Laravel query builder:
SELECT * FROM (
SELECT DISTINCT ON (c.conversation_meta_id) c.conversation_meta_id cmid,c.id,
c.created_date,c.message,u.first_name,u.last_name,pr.name AS practice_role
FROM dr_iq.oc_conversation_histories ch
LEFT JOIN dr_iq.conversation c ON ch.conversation_meta_id = c.conversation_meta_id
LEFT JOIN dr_iq.users u on u.id = c.user_id
LEFT JOIN dr_iq.user_group ug on ug.user_id = u.id
LEFT JOIN dr_iq.practice_roles pr on ug.practice_role_id = pr.id
WHERE ch.ref_id = $ref_id
GROUP BY c.created_date, c.conversation_meta_id, c.message, u.first_name,u.last_name,pr.name,c.id,ug.group_id
ORDER BY c.conversation_meta_id desc, c.id DESC,c.created_date DESC ) converstaionList order by converstaionList.created_date desc limit 50;
This is the code I have tried so far, but I am facing an issue how to use the select inside select:
DB::table('dr_iq.oc_conversation_histories ch')
->SELECT('c.conversation_meta_id')->distinct()
->SELECT('c.conversation_meta_id AS cmid','c.id','c.created_date','c.message','u.first_name','u.last_name','pr.name AS practice_role')
->LEFTJOIN ('dr_iq.conversation c', 'ch.conversation_meta_id', '=', 'c.conversation_meta_id')
->LEFTJOIN ('dr_iq.users u', 'u.id', '=', 'c.user_id')
->LEFTJOIN ('dr_iq.user_group ug', 'ug.user_id', '=', 'u.id')
->LEFTJOIN ('dr_iq.practice_roles pr', 'ug.practice_role_id', '=', 'pr.id')
->WHERE('ch.ref_id', 15218)
->GROUP BY('c.created_date', 'c.conversation_meta_id', 'c.message', 'u.first_name', 'u.last_name', 'pr.name', 'c.id', 'ug.group_id')
->ORDER BY('c.conversation_meta_id DESC, c.id DESC, c.created_date DESC') LIMIT (50);