Laravel 4 Eloquent Query Builder - 复杂的变量连接

Laravel 4 Eloquent Query Builder - Complicated joins with variable

提问by John Mellor

I'm trying to replicate a join like so using the laravel query builder:

我正在尝试使用 laravel 查询构建器复制这样的连接：

LEFT JOIN content_userdata
ON content_id = content.id
AND user_id = $user_id

I have discovered that I can do additional "ons" using the following function in my model which extends Eloquent

我发现我可以在扩展 Eloquent 的模型中使用以下函数做额外的“on”

public function scopeJoinUserData($query, $user_id)
{
    return $query->leftJoin('content_userdata', function($join)
    {
        $join->on('content_userdata_content_id', '=', 'content.content_id')->on('content_userdata_user_id', '=', 10);
    });
}

But this creates two problems. Firstly I cannot get the $user_id variable into the function and secondly even if I hardcode it for testing purposes as I have done above (to int "10") Laravel encases it in ` meaning that it is interpreted as a column name when it shouldn't be, like so:

但这会产生两个问题。首先，我无法将 $user_id 变量放入函数中，其次，即使我出于测试目的对它进行了硬编码，就像我上面所做的那样（到 int "10"），Laravel 将它封装在 ` 中，这意味着它应该被解释为列名不是，像这样：

left join `content_userdata`
on `content_id` = `content`.`id`
and `user_id` = `10`

So now I have two problems.

所以现在我有两个问题。

1. I cannot get the $user_id into the join function when using query scopes
2. Even if I could I cannot send a variable to the join since it always interprets it as a column name

1. 使用查询范围时，我无法将 $user_id 加入连接函数
2. 即使我可以，我也无法向连接发送变量，因为它总是将其解释为列名

Why would I want to do this?I realise one response may be to place it in a where. However I am trying to do it this way as the join may not necessarily return any results (hence the left join), since the content_userdata table contains things like a users rating for a piece of content. If I use a where then results with nothing in the content_userdata table will not be returned, where as if I can put it in the join then they will be returned due to the left join.

我为什么要这样做？我意识到一种反应可能是把它放在某个地方。但是，我试图这样做，因为连接可能不一定返回任何结果（因此是左连接），因为 content_userdata 表包含诸如用户对一段内容的评级之类的内容。如果我使用 where 则不会返回 content_userdata 表中没有任何内容的结果，如果我可以将其放入连接中，那么由于左连接，它们将被返回。

Is there anyway to achieve this in Laravel and if not what are the alternatives, obviously completely changing ditching Laravel is over the top but the only alternative I can think of is to get the userdata in a separate query.

无论如何要在 Laravel 中实现这一点，如果没有的话，有什么替代方案，显然完全改变放弃 Laravel 是最重要的，但我能想到的唯一替代方案是在单独的查询中获取用户数据。

采纳答案by John Mellor

I managed to fix this myself, there's a note at the bottom of why it's not completely optimal but here's how to do it anyway:

我设法自己解决了这个问题，底部有一个注释说明为什么它不是完全最优的，但无论如何如何做到这一点：

public function scopeJoinUserData($query, $user_id)
{
    return $query->leftJoin('content_userdata', function($join) use ($user_id)
    {
        $join->on('content_userdata_content_id', '=', 'content.content_id')->on('content_userdata_user_id', '=', DB::raw('"'.$user_id.'"'));
    });
}

Note the use of "use ($user_id)" as suggested by @Half Crazed.

请注意@Half Crazed 建议使用“use ($user_id)”。

DB::raw() is used to wrap $user_id in quotes even though it's an integer and not a string. This will stop Laravel automatically using ` which makes it MySQL interpret it as a column name.

DB::raw() 用于将 $user_id 括在引号中，即使它是整数而不是字符串。这将使用 ` 自动停止 Laravel，这使得 MySQL 将其解释为列名。

Performance:One thing to note is that MySQL queries can be considerably faster when using an integer rather than a string and will interpret it as a string if it's wrapped in quotes. I'm not worrying about that for now, but I figured I should mention it if others are using this as a solution.

性能：需要注意的一件事是，当使用整数而不是字符串时，MySQL 查询会快得多，并且如果它用引号括起来，则会将其解释为字符串。我现在不担心这个，但我想如果其他人使用它作为解决方案，我应该提到它。

回答by Half Crazed

You need to pass the variable to the closure using the usekeyword - which imports the variable into scope. Example:

您需要使用use关键字将变量传递给闭包- 将变量导入作用域。例子：

public function scopeJoinUserData($query, $user_id)
{
    return $query->leftJoin('content_userdata', function($join) use ($user_id)
    {
        $join->on('content_userdata_content_id', '=', 'content.content_id')
             ->on('content_userdata_user_id',    '=', DB::raw($user_id));
    });
}

This is a PHP syntax related issue and not a Laravel limitation!

这是一个 PHP 语法相关的问题，而不是 Laravel 的限制！

回答by prograhammer

In the accepted answer, just adding quotes around the DB::rawpart of the query will not fully protect it from sql injection. Just pass some quotes in your user_id and see. To parameterize you can do something like this:

在接受的答案中，仅DB::raw在查询部分周围添加引号并不能完全保护它免受 sql injection 的影响。只需在您的 user_id 中传递一些引号并查看。要参数化，您可以执行以下操作：

public function scopeJoinUserData($query, $user_id)
{
    return $query->leftJoin('content_userdata', function($join)
        {
            $join->on('content_userdata_content_id', '=', 'content.content_id')
                 ->on('content_userdata_user_id',    '=', DB::raw('?'));
        }
    ->setBindings(array_merge($query->getBindings(),array($user_id)));
}

Notice in this example that you don't have to pass the variable into the closure. Alternatively you could try and write this part completely raw.

请注意，在此示例中，您不必将变量传递到闭包中。或者，您可以尝试完全原始地编写这部分。

UPDATE: Taylor addedjoinWhere, leftJoinWhere... if you have a function join just use ->whereand ->orWherefrom within the Closure.

更新：Taylor 补充说joinWhere，leftJoinWhere...如果你有一个函数 join 就在 Closure 中使用->where和->orWhere。

回答by Laurence

Why dont you just use relationships? That is the whole point of an ORM like Eloquent?

为什么不直接使用关系？这就是像 Eloquent 这样的ORM的全部意义所在？

Something like this;

像这样的东西；

class User extends Eloquent {
    public function userdata()
    {
        return $this->hasOne('Userdata');
    }
}

$result= User::find(1)->userdata();

edit to show you can do whatever you want with relationships

编辑以表明您可以对人际关系做任何想做的事

Option 1:

选项1：

$place = new Place;

$array = $place->with(array('users' => function($query)
{
    $query->where('user_id', $user_id);
}))->get();

var_dump($array->toArray());

or Option 2:

或选项 2：

$place = new Place;

$array = $place->with('users')->where('user_id', $user_id)->get();

var_dump($array->toArray());

Both give different results - but you get the idea

两者都给出不同的结果 - 但你明白了

回答by Hieu Le

Your first problem: You should use PHP syntax for closure as the answer of Half. About your second problem, I think the part AND user_id = $user_idof the query does not belong to a JOIN clause but a WHERE clause because it just depends on one table, not both in this joining relationship. I think you should use a subquery like this:

您的第一个问题：您应该使用 PHP 语法进行闭包作为Half的答案。关于你的第二个问题，我认为AND user_id = $user_id查询的部分不属于 JOIN 子句，而是属于 WHERE 子句，因为它只依赖于一个表，而不是在这种连接关系中。我认为你应该使用这样的子查询：

public function scopeJoinUserData($query, $user_id)
{
    return $query->leftJoin(\DB:raw("(SELECT * FROM content_userdata WHERE user_id = {$user_id}) AS t"), function($join)
    {
        $join->on('t.content_id', '=', 'content.content_id');
    });
}

However, as you see, let be sure that the $user_idvariable is safe because we use \DB:rawmethod.

但是，如您所见，请确保$user_id变量是安全的，因为我们使用\DB:raw方法。