How to convert SQL query to query builder?

Question

controlens is a table in my database and entite, state are the fields of this table

SELECT a.entite, a.etat,  COUNT(a.etat) as nombre_toperform, b.nombre_performed,c.nombre_incompatible
FROM `controlens` a

LEFT JOIN ( SELECT entite,COUNT(etat) as nombre_performed from `controlens` WHERE etat like 'PERFORMED' GROUP BY entite, etat) b on a.entite = b.entite

LEFT JOIN ( SELECT entite,COUNT(etat) as nombre_incompatible from `controlens` WHERE etat like 'INCOMPATIBLE' GROUP BY entite, etat) c on a.entite = c.entite

WHERE a.etat like '%TOPERFORM%' 
GROUP BY a.entite, a.etat, b.nombre_performed,c.nombre_incompatible

This is the English version of Stackoverflow. Please post your question in English. — Mozammil
– Mozammil, Commented Jan 28, 2019 at 12:00
help me translate the sql query into the query builder query — naelamadou
– naelamadou, Commented Jan 28, 2019 at 12:03
Edit your question, and show us some code, where you tried to do this. We can't made all for you, and check how to post a question — Saromase
– Saromase, Commented Jan 28, 2019 at 12:15

cfnerd · Accepted Answer · 2019-01-28 16:14:33Z

0

There are likely few ways in which you can accomplish this. Here is one idea that might work (note: haven't fully tested, so it might need some tweaks):

$toPerform = '%TOPERFORM%';
$subQuery1 = 'SELECT entite,COUNT(etat) as nombre_performed from `controlens` WHERE etat like 'PERFORMED' GROUP BY entite, etat';
$subQuery2 = 'SELECT entite,COUNT(etat) as nombre_incompatible from `controlens` WHERE etat like 'INCOMPATIBLE' GROUP BY entite, etat';

DB::table('controlens as a')->select([
    'a.entite',
    'a.etat',
    DB::raw('COUNT(a.etat) AS nombre_toperform'),
    'b.nombre_performed',
    'c.nombre_incompatible'
])
->leftJoin(DB::raw("($subQuery1) as b"), 'a.entite', '=', 'b.entite')
->leftJoin(DB::raw("($subQuery2) as c"), 'a.entite', '=', 'c.entite')
->where('a.etat', $toPerform)
->groupBy('a.entite', 'a.etat', 'b.nombre_performed', 'c.nombre_incompatible');

Alternatively, you could try this package to see if it helps the cause: eloquent-subquery-magic

Beyond that, here are a few helpful articles that might point you in the correct direction:

subquery with join in laravel

Laravel Fluent Query Builder Join with subquery

How to write this (left join, subquery ) in Laravel 5.1?

answered Jan 28, 2019 at 16:14

cfnerd

3,79912 gold badges36 silver badges45 bronze badges

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

1 Comment

naelamadou Over a year ago

thanks cfnerd i have tested your answer it's a good help for me

Collectives™ on Stack Overflow

How to convert SQL query to query builder?

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related