I use parameterized query in Laravel:

$objectsSQL = <<<'EOS'
SELECT
    o.id AS oid,
    o.name AS name,
    o.fields AS fields
FROM projects p
JOIN tasks AS t ON t.project_id = p.id
LEFT JOIN commands AS c ON c.task_id = t.id
LEFT JOIN objects AS o ON o.id = c.object_id
WHERE p.id = :pid
GROUP BY o.id, o.name
ORDER BY o.name
EOS;

Then I use it in loop as $objects = DB::select($objectsSQL, ['pid' => $row->pid]);

For now I want to avoid to use raw SQL and replace it by Query Builder:

$objects = DB::table('projects', 'p')
    ->select(
        'o.id AS oid',
        'o.name AS name',
        'o.fields AS fields'
    )
    ->join('tasks as t', 't.project_id', '=', 'p.id')
    ->leftJoin('commands as c', 'c.task_id', '=', 't.id')
    ->leftJoin('objects as o', 'o.id', '=', 'c.object_id')

    **what to do here to use `:pid`? (WHERE p.id = :pid)**

    ->gropupBy(['o.id', 'o.name'])
    ->orderBy('o.name')

and then how to bind :pid ?

I use parameterized query in Laravel:

$objectsSQL = <<<'EOS'
SELECT
    o.id AS oid,
    o.name AS name,
    o.fields AS fields
FROM projects p
JOIN tasks AS t ON t.project_id = p.id
LEFT JOIN commands AS c ON c.task_id = t.id
LEFT JOIN objects AS o ON o.id = c.object_id
WHERE p.id = :pid
GROUP BY o.id, o.name
ORDER BY o.name
EOS;

Then I use it in loop as $objects = DB::select($objectsSQL, ['pid' => $row->pid]);

For now I want to avoid to use raw SQL and replace it by Query Builder:

$objects = DB::table('projects', 'p')
    ->select(
        'o.id AS oid',
        'o.name AS name',
        'o.fields AS fields'
    )
    ->join('tasks as t', 't.project_id', '=', 'p.id')
    ->leftJoin('commands as c', 'c.task_id', '=', 't.id')
    ->leftJoin('objects as o', 'o.id', '=', 'c.object_id')

    **what to do here to use `:pid`? (WHERE p.id = :pid)**

    ->gropupBy(['o.id', 'o.name'])
    ->orderBy('o.name')

and then how to bind :pid ?

• laravel
• postgresql
• laravel-query-builder

2 Answers 2

Ah I see what you mean, you're trying to have this same query be defined and then run it with different $pid values. Since the query builder's addBinding method mutates the object, you'd need to have a callback or a function that either makes the query (option 1) or returns the query builder without the :pid value bound to it (option 2).

// option 1 - function (could be inside the same class as a method, or somewhere else entirely)
function getObjects($pid): Collection // find a better name
{
    DB::table('projects', 'p')
        ->select(
            'o.id AS oid',
            'o.name AS name',
            'o.fields AS fields'
        )
        ->join('tasks as t', 't.project_id', '=', 'p.id')
        ->leftJoin('commands as c', 'c.task_id', '=', 't.id')
        ->leftJoin('objects as o', 'o.id', '=', 'c.object_id')
        ->where('p.id', '=', $pid)
        ->gropupBy(['o.id', 'o.name'])
        ->orderBy('o.name')
        ->get();
}

...
$objects_4 = getObjects(4);
$objects_5 = getObjects(5);
$objects_6 = getObjects(6);

// option 1 - callback

$objectSQL = fn ($pid): Collection => 
    DB::table('projects', 'p')
        ->select(
            'o.id AS oid',
            'o.name AS name',
            'o.fields AS fields'
        )
        ->join('tasks as t', 't.project_id', '=', 'p.id')
        ->leftJoin('commands as c', 'c.task_id', '=', 't.id')
        ->leftJoin('objects as o', 'o.id', '=', 'c.object_id')
        ->where('p.id', '=', $pid)
        ->gropupBy(['o.id', 'o.name'])
        ->orderBy('o.name')
        ->get();

$objects_4 = $objectSQL(4);
$objects_5 = $objectSQL(5);
$objects_6 = $objectSQL(6);

// option 2 - function (could be inside the same class as a method, or somewhere else entirely)
function getObjects(): Builder // find a better name
{
    DB::table('projects', 'p')
        ->select(
            'o.id AS oid',
            'o.name AS name',
            'o.fields AS fields'
        )
        ->join('tasks as t', 't.project_id', '=', 'p.id')
        ->leftJoin('commands as c', 'c.task_id', '=', 't.id')
        ->leftJoin('objects as o', 'o.id', '=', 'c.object_id')
        ->where('p.id', '=', DB::raw(':pid'))
        ->groupBy(['o.id', 'o.name'])
        ->orderBy('o.name');
}

...
$objects_4 = getObjects()->addBinding(['pid' => 4])->get();
$objects_5 = getObjects()->addBinding(['pid' => 5])->get();
$objects_6 = getObjects()->addBinding(['pid' => 6])->get();

// option 2 - callback

$objectSQL = fn (): Builder => 
    DB::table('projects', 'p')
        ->select(
            'o.id AS oid',
            'o.name AS name',
            'o.fields AS fields'
        )
        ->join('tasks as t', 't.project_id', '=', 'p.id')
        ->leftJoin('commands as c', 'c.task_id', '=', 't.id')
        ->leftJoin('objects as o', 'o.id', '=', 'c.object_id')
        ->where('p.id', '=', DB::raw(':pid'))
        ->groupBy(['o.id', 'o.name'])
        ->orderBy('o.name');

$objects_4 = $objectSQL()->addBinding(['pid' => 4])->get();
$objects_5 = $objectSQL()->addBinding(['pid' => 5])->get();
$objects_6 = $objectSQL()->addBinding(['pid' => 6])->get();

You can simply add a chained call to the where() function:

->where('p.id', '=', $pid)

Laravel will automatically bind the $pid value when it generates a prepared statement based on the builder call.

Updated code:

objects = DB::table('projects', 'p')
->select(
    'o.id AS oid',
    'o.name AS name',
    'o.fields AS fields'
)
->join('tasks as t', 't.project_id', '=', 'p.id')
->leftJoin('commands as c', 'c.task_id', '=', 't.id')
->leftJoin('objects as o', 'o.id', '=', 'c.object_id')
->where('p.id', '=', $pid)
->gropupBy(['o.id', 'o.name'])
->orderBy('o.name')