主页 / coding / 问题 / 79541139
Accepted
vbulash
Asked: 2025-03-28 18:09:14 +0800 CST

Laravel 查询生成器中的参数化查询

  • 772

我在 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;

然后我在循环中使用它$objects = DB::select($objectsSQL, ['pid' => $row->pid]);

现在我想避免使用原始 SQL 并用查询生成器替换它：

$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')

然后如何绑定:pid？

laravel

2 个回答

  1. Best Answer

    啊，我明白你的意思了，你试图定义这个相同的查询，然后使用不同的 $pid 值运行它。由于查询生成器的addBinding方法会改变对象，因此你需要有一个回调或函数来执行查询（选项 1）或返回没有绑定到它的值的查询生成器:pid （选项 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. 您可以简单地向函数添加链式调用where()：

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

    $pidLaravel 将在基于构建器调用生成准备好的语句时自动绑定该值。

    更新的代码：

    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

相关问题