問題描述

我需要通過一個(gè)查詢獲得兩個(gè)城市名稱:

I need get two city names with one query:

例如:

城市表:

+---------+----------+
|  Pana   |   Name   |
+---------+----------+
|   THR   |  Tehran  |
|   LON   |  London  |
+---------+----------+

在模型中:from_city 是 THR，to_city 是 LON

In Model: from_city is THR and to_city is LON

public function scopePrintQuery($query, $id)
{
    $join = $query
        -> join('cities', 'cities.pana', 'flights.from_city')
        -> join('cities', 'cities.pana', 'flights.to_city')
        -> where('flights.id', $id)
        ->get([
            'flights.*',
            'cities.name as from_city'
            ??? for to_city?
        ]);
    return $join;
}

現(xiàn)在，我需要在此查詢中獲取 from_city 名稱和 to_city 名稱.

Now, I need get from_city name and to_city name in this query.

該查詢不適用于一張表中的兩個(gè)連接！

The query does not work with two joins from one table!

如何創(chuàng)建這個(gè)查詢?

推薦答案

使用直接的 SQL 你可以給每個(gè)連接的表一個(gè)別名 - 例如

With straight SQL you could give each joined table an alias - e.g.

SELECT flights.*
FROM flights as f
 JOIN cities as fromCity on fromCity.pana = f.from_city
 JOIN cities as toCity on toCity.pana = f.to_city
WHERE f.id = 3 --

對于 Eloquent，使用 select() 來指定選擇字段.還可以使用 DB::raw() 來使用原始 SQL(例如給出一個(gè)表的別名，如 DB::raw('cities as toCity').

With Eloquent, use select() to specify select fields. Also use DB::raw() to use raw SQL (e.g. giving an alias to table like DB::raw('cities as toCity').

public function scopePrintQuery($query, $id)
{
  $join = $query
    -> join(DB::raw('cities as fromCity'), 'fromCity.pana', 'flights.from_city')
    -> join(DB::raw('cities as toCity'), 'toCity.pana', 'flights.to_city')
    -> where('flights.id', $id)
    ->select([
        'flights.*',
        DB::raw('fromCity.name as from_city')
        DB::raw('toCity.name as to_city')
    ]);
    return $join->get();
}

這篇關(guān)于如何在 Laravel 5.3 中創(chuàng)建兩次連接到表的查詢?的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網(wǎng)！