How to convert SQL query in laravel query?

Question

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

Check: jjlabajo.github.io/SQLtoEloquent and I suggest you use proper join in your MySQL query — Ergest Basha
– Ergest Basha, Commented Oct 6, 2021 at 12:41
And you do not have a join condition for merchantlinkrelation table — Ergest Basha
– Ergest Basha, Commented Oct 6, 2021 at 12:43
can you please add a photo of the DB schema? So that I can help you to write the query. — KudosIntech
– KudosIntech, Commented Oct 6, 2021 at 12:55

KudosIntech · Accepted Answer · 2021-10-06 14:13:10Z

1

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();

edited Oct 6, 2021 at 14:13

answered Oct 6, 2021 at 13:04

KudosIntech

5443 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Hamza Qureshi Over a year ago

no this is not help ful it gives me error

KudosIntech Over a year ago

can you please tell me what is an error?

Hamza Qureshi Over a year ago

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'ocosystemdb.merchantlink m, company c, merchantlinkrelation mlr' doesn't exist (SQL: 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') is null or (m.initiator_user_id = 86 and m.responder_user_id = c.owner_user_id and mlr.ptype = 'dealer') is null or (m.initiator_user_id = c.owner_user_id and c.owner_user_id = 86 and mlr.ptype = 'dealer') is null group by c.id)

KudosIntech Over a year ago

@hamza-qureshi I have changed the query. can you please check it?

Rens van Melis · Accepted Answer · 2021-10-06 14:02:06Z

1

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.

edited Oct 6, 2021 at 14:02

answered Oct 6, 2021 at 13:56

Rens van Melis

436 bronze badges

Collectives™ on Stack Overflow

How to convert SQL query in laravel query?

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related