Início / coding / Perguntas / 79541139
Accepted
vbulash
Asked: 2025-03-28 18:09:14 +0800 CST

Consulta parametrizada no Laravel Query Builder

  • 772

Eu uso consulta parametrizada no 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;

Então eu uso isso em loop como$objects = DB::select($objectsSQL, ['pid' => $row->pid]);

Por enquanto, quero evitar o uso de SQL bruto e substituí-lo pelo 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')

e então como vincular :pid?

laravel

2 respostas

  1. Best Answer

    Ah, entendi o que você quer dizer, você está tentando ter essa mesma consulta definida e então executá-la com valores $pid diferentes. Como o addBindingmétodo do construtor de consultas altera o objeto, você precisaria ter um retorno de chamada ou uma função que faça a consulta (opção 1) ou retorne o construtor de consultas sem o :pid valor vinculado a ele (opção 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();
    • 1

  2. Você pode simplesmente adicionar uma chamada encadeada à where()função:

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

    O Laravel vinculará automaticamente o $pidvalor quando gerar uma instrução preparada com base na chamada do construtor.

    Código atualizado:

    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')
    • 0

relate perguntas