Make this query in Laravel Eloquent-CodePudding

Can someone tell me how to convert this SQL query to Laravel Eloquent?

SELECT
    campo1, 
    campo2,
    IFNULL((SELECT campox FROM tabla2 where campoy=123), valorSiesNull) nombreCampoTabla2,
    campo3,
    campo4,
    IFNULL((SELECT campox FROM tabla3 where campoy=123), valorSiesNull)nombreCampoTabla3
FROM (
    SELECT * FROM tabla1 WHERE campo='ABC'
) AS consulta;

CodePudding user response：

For expressions like IFNULL (...), the easiest way is to use DB::raw(expression, [bindings]). Just remember to use bindings if any user value is provided to avoid SQL injections.
For subquery tables FROM (...) alias, the fromSub(Builder|Closure, alias) should be enough.

// Builder
$subquery = DB::query()-from('tabla1')->where('campo', 'ABC');

// Closure
$subquery = function ($query) { $query-from('tabla1')->where('campo', 'ABC'); }

You can also inline $subquery, but pick the syntax that you're more comfortable with.

$results = DB::query()
    ->select(
        'campo1', 
        'campo2',
        DB::raw('IFNULL((SELECT campox FROM tabla2 WHERE campoy = ?), valorSiesNull) nombreCampoTabla2', [123]),
        'campo3',
        'campo4',
        DB::raw('IFNULL((SELECT campox FROM tabla3 WHERE campoy = ?), valorSiesNull) nombreCampoTabla3', [123])
    )
    ->fromSub($subquery, 'consulta')
    ->get();

CodePudding user response：

You can use

DB::raw(
 SELECT
    campo1, 
    campo2,
    IFNULL((SELECT campox FROM tabla2 where campoy=123), valorSiesNull) nombreCampoTabla2,
    campo3,
    campo4,
    IFNULL((SELECT campox FROM tabla3 where campoy=123), valorSiesNull)nombreCampoTabla3
 FROM (
    SELECT * FROM tabla1 WHERE campo='ABC'
 ) AS consulta;
);