laravel 是否可以在没有 DB:Raw 的情况下从多个表中进行选择？

Is it possible to select from multiple tables without DB:Raw?

提问by Hector

Some noob question about Laravel's query builder. I'm trying to translate that simple SQL query :

关于 Laravel 的查询构建器的一些菜鸟问题。我正在尝试翻译那个简单的 SQL 查询：

SELECT s.name, e.name, m.name
FROM sports as s, events as e, matches as m
WHERE s.name = 'Football'

Where sports -> events -> matchs have a 1 -> n relation

其中体育 -> 事件 -> 比赛有 1 -> n 关系

• Match has an event_id foreign key
• Event has a sport_id foreign key,
• Sport is root.

• 匹配有一个 event_id 外键
• 事件有一个 sport_id 外键，
• 运动是根。

So the above select query with s.name = 'Football' gives :

所以上面带有 s.name = 'Football' 的选择查询给出：

+----------+-------------+----------------------+
| name     | name        | name                 |
-------------------------------------------------
|Football  | World Cup   |  England vs Germany  |
|Football  | World Cup   |  Argentina vs France |
| ...      | ...         |  ...                 |
-------------------------------------------------

I would like to translate it in a Laravel Query Builder query, without using DB:Raw, and I'm a bit confused as all examples shown in the doc start with DB:table(blabla), which seems to accept only 1 table as parameter. So something like

我想在 Laravel Query Builder 查询中翻译它，而不使用 DB:Raw，而且我有点困惑，因为文档中显示的所有示例都以 DB:table(blabla) 开头，它似乎只接受 1 个表作为范围。所以像

$events = DB::table('sports as s', 'events as e')
->select('s.name', 'e.name')
->where('s.name', '=', 'Football')
->get();

says e.name is considered unknown. I've read some answers here that were all using DB:Raw or some strange joins.

说 e.name 被认为是未知的。我在这里阅读了一些答案，这些答案都使用 DB:Raw 或一些奇怪的连接。

So...Do you guys know any Laravel's Query Builder elegant way to handle those queries? Or do I have to stick to DB:Raw?

那么……你们知道 Laravel 的 Query Builder 优雅的处理这些查询的方式吗？还是我必须坚持使用 DB:Raw？

Thanks in advance!

提前致谢！

回答by Tim Sheehan

I believe what you're trying to do is create a union between your three tables.

我相信你想要做的是在你的三个表之间创建一个联合。

From http://laravel.com/docs/5.1/queries#unions

来自http://laravel.com/docs/5.1/queries#unions

$first = DB::table('users')
            ->whereNull('first_name');

$users = DB::table('users')
            ->whereNull('last_name')
            ->union($first)
            ->get();

The unionAll method is also available and has the same method signature as union.

unionAll 方法也可用，并且具有与 union 相同的方法签名。

A join would be used if you wanted to query your matches table for a match then join the event the match was played at on with match_id = event_id for example.

如果您想查询比赛表中的比赛，然后加入比赛进行的事件，例如 match_id = event_id，将使用连接。

回答by Hector

Well after wasting quite some time, I found out that :

好吧，在浪费了一段时间之后，我发现：

• Using a SELECT WHERE on multiple tables instead of explicit JOIN is considered a bad practice, essentially for code readability.
• There's probably no way with Laravel's Query Builder to achieve what I wanted (the bad practice way) without using DB:raw

• That the correct way to solve my problem is :

  $events = DB::table('sports as s')
          ->join ('events as e', 'e.sport_id', '=' , 's.id')
          ->join ('matches as m', 'm.event_id', '=' , 'e.id')
          ->select('s.name as sname',
                      'e.name as ename',
                      'm.name as mname')
          ->get();
  

• 在多个表上使用 SELECT WHERE 而不是显式 JOIN 被认为是一种不好的做法，主要是为了代码可读性。
• 如果不使用 DB:raw，Laravel 的 Query Builder 可能无法实现我想要的（糟糕的做法）

• 解决我的问题的正确方法是：

  $events = DB::table('sports as s')
          ->join ('events as e', 'e.sport_id', '=' , 's.id')
          ->join ('matches as m', 'm.event_id', '=' , 'e.id')
          ->select('s.name as sname',
                      'e.name as ename',
                      'm.name as mname')
          ->get();
  

(Works fine at least)

（至少工作正常）

• That you MUSTalias the names in the SELECT when the fields names are identical, or you'll get just one column for all identical names (that's where I lost quite some time :) ).

• I tried with Yii framework and the "bad practice way" worked fine as well as the join method :

   $query = (new \yii\db\Query())
   ->select(['sport_name' => 's.name','event_name' => 'e.name'])
   ->from(['s' => 'sports','e' => 'events'])
   ->where(['s.name' => 'Football'])
   ->all();
  

• 当字段名称相同时，您必须为 SELECT 中的名称添加别名，否则您将只获得一列所有相同的名称（这就是我失去了相当长一段时间的地方 :) ）。

• 我尝试使用 Yii 框架，“糟糕的实践方式”和 join 方法都运行良好：

   $query = (new \yii\db\Query())
   ->select(['sport_name' => 's.name','event_name' => 'e.name'])
   ->from(['s' => 'sports','e' => 'events'])
   ->where(['s.name' => 'Football'])
   ->all();
  

I hope this will help someone else,

我希望这会帮助别人，

Bye.

再见。