mySun mySun -4 years ago 94
MySQL Question

How to create query with twice a connection to a table in Laravel 5.3?

I need get two city names with one query:

For example:

City table:

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


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;
}


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

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

How to create this query?

Answer Source

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 --

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();
}
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download