Laravel 4：原始的查询到雄辩 [英] Laravel 4 : Raw Query to Eloquent

问题描述

我想把这个查询转换成Eloquent：

I want to convert this query to Eloquent:

SELECT *
FROM news
WHERE who_see='6'
  OR (news.id_user IN
        (SELECT id_user
         FROM koneksi_user
         WHERE to_id_user='$user->id_user'
           AND confirmed='0')
      OR news.id_user IN
        (SELECT to_id_user
         FROM koneksi_user
         WHERE id_user='$user->id_user'
           AND confirmed='0')))
ORDER BY created_at DESC

到目前为止已经尝试过：

So far I have tried this:

$user = \Auth::user();

return \DB::table('news')
        ->where('who_see','6')
        ->orWhereIn('news.id_user', function($query)
            {
                $query->select('id_user')
                ->from('koneksi_user')
                ->whereRaw('to_id_user = $user->id_user');
            })
        ->orWhereIn('news.id_user', function($query2)
            {
                $query2->select('id_user')
                ->from('koneksi_user')
                ->whereRaw('id_user = $user->id_user');
            })
        ->get();

但是我收到这个错误：

SQLSTATE [42601]：语法错误：7错误：'sintaks错误'pada atau didekat«$»LINE 1：...id_user来自koneksi_user，其中to_id_user = $ user-> id _... ^（select）fromnewswherewho_see= 6 ornews。id_userin（从koneksi_user中选择id_user，其中to_id_user = $ user-> id_user）或新闻。id_user（从koneksi_user中选择id_user，其中id_user = $ user-> id_user））

SQLSTATE[42601]: Syntax error: 7 ERROR: 'sintaks error' pada atau didekat « $ » LINE 1: ... "id_user" from "koneksi_user" where to_id_user = $user->id_... ^ (SQL: select * from "news" where "who_see" = 6 or "news"."id_user" in (select "id_user" from "koneksi_user" where to_id_user = $user->id_user) or "news"."id_user" in (select "id_user" from "koneksi_user" where id_user = $user->id_user))

我解决了这个问题？

推荐答案

代码的问题是，您尝试使用 whereRaw 作为两个条件，但在字符串中包含实际的变量名称，这意味着：

You almost had it working. The problem with the code is that you tried to use whereRaw for two of the conditions but included the actual variable name within the string, which means that this:

->whereRaw('to_id_user = $user->id_user');

将创建此SQL条件：

WHERE to_id_user = $user->id_user

所以它只是使用文字 $ user-> id_user 字符串而不是实际的ID值。要修复，你只需要使用常规的其中方法：

so it just uses the literal $user->id_user string instead of the actual ID value. To fix that you just have to use the regular where method:

->where('to_id_user', $user->id_user);

将生成您要查找的SQL查询的完整查询构建器代码应如下所示：

The complete Query Builder code that will generate the SQL query you're looking for, should look like this:

\DB::table('news')
   ->where('who_see', '6')
   ->orWhereIn('news.id_user', function($query) {
       $query->select('id_user')
             ->from('koneksi_user')
             ->where('to_id_user', $user->id_user)
             ->where('confirmed', 0);
   })
   ->orWhereIn('news.id_user', function($query2) {
       $query2->select('id_user')
              ->from('koneksi_user')
              ->where('id_user', $user->id_user)
              ->where('confirmed', 0);
   })
   ->orderBy('created_at', 'desc')
   ->get();

为了将来参考，如果由于任何原因您想/需要使用 whereRaw 并将变量传递给该条件，您应该使用绑定来避免 SQL注入：

And for future reference, if for any reason you want/need to use whereRaw and pass variables to the condition, you should use bindings to avoid SQL Injection:

->whereRaw('to_id_user = ?', [$user->id_user]);

这篇关于Laravel 4：原始的查询到雄辩的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！