How to convert SQL query in laravel query?-CodePudding

I know this question is not asked in a well manner way,so I am sorry,I have SQL query this one

`SELECT
    c.*
FROM
    merchantlink m,
    company c,
    merchantlinkrelation mlr
WHERE
    (m.initiator_user_id = c.owner_user_id AND
     m.responder_user_id = 86 AND
     mlr.ptype='dealer')
     OR
    (m.initiator_user_id = 86 AND
     m.responder_user_id = c.owner_user_id AND
     mlr.ptype = 'dealer')
     OR
    (m.initiator_user_id = c.owner_user_id AND
     c.owner_user_id=86 AND
     mlr.ptype='dealer')
GROUP BY
    c.id;`

I want to convert it in PHP laravel query form so tried this query

 $twowaycompany = DB::table('company')
                ->join('merchantlink','merchantlink.responder_user_id', 'company.owner_user_id')
->join('merchantlinkrelation','merchantlinkrelation.merchantlink_id','merchantlink.id')
                
                ->orWhere('merchantlink.initiator_user_id', 86)
                ->join('merchantlink','merchantlink.initiator_user_id', 'company.owner_user_id')
                ->orWhere('merchantlink.responder_user_id', 86)
                ->pluck('name')->toArray();

but I don't know SQL and even I am not understanding how I convert it, can someone help just convert SQL query to laravel query?

here is the db image merchante link

merchantlinkrelation

CodePudding user response：

You could simply do this:

DB::Select(
  DB::Raw('
    SELECT
      c.*
    FROM
      merchantlink m,
      company c,
      merchantlinkrelation mlr
    WHERE
       (m.initiator_user_id = c.owner_user_id AND
       m.responder_user_id = 86 AND
       mlr.ptype='dealer')
    OR
       (m.initiator_user_id = 86 AND
       m.responder_user_id = c.owner_user_id AND
       mlr.ptype = 'dealer')
    OR
       (m.initiator_user_id = c.owner_user_id AND
       c.owner_user_id=86 AND
       mlr.ptype='dealer')
    GROUP BY
       c.id
  ')
);

But there are probably better ways to do it.

CodePudding user response：

Please I have tried to create a query (Laravel Query Builder). Please check and let me know is it working or not.

DB::table(DB::raw("merchantlink m, company c, merchantlinkrelation mlr"))
->select("c.*")
->whereRaw ("(m.initiator_user_id = c.owner_user_id and m.responder_user_id = 86 and mlr.ptype = 'dealer')")
->orWhereRaw("(m.initiator_user_id = 86 and m.responder_user_id = c.owner_user_id and mlr.ptype = 'dealer')")
->orWhereRaw("(m.initiator_user_id = c.owner_user_id and c.owner_user_id = 86 and mlr.ptype = 'dealer')")
->groupBy("c.id")
->get();