1

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);
5
  • @IMSoP thanks for the reply i have edited my question with tried code. Commented Aug 24, 2022 at 11:22
  • You can use closure while building query to perform subquery this might help Commented Aug 24, 2022 at 11:45
  • Why do you not use eloquent builder instead? and define relations in Models? Commented Aug 24, 2022 at 19:58
  • @shaho1090 i have not worked on relations much but i already define relations in their models but issue will be be the same how to use this type of select in select in laravel as this query also have a name 'conversatioList' which should be managed in realtion query. Commented Aug 25, 2022 at 4:29
  • @M Shahbaz , In this link, Laravel documentation, explains how to query nested relations: laravel.com/docs/9.x/eloquent-relationships#querying-relations Commented Aug 25, 2022 at 6:20

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.