在Eloquent ORM和Laravel中加入的问题

Question

I am struggling to use the query builder to create inner joins between my tables. I have three tables with the following relationships:

aircraft_classes 1:m aircraft_types m:1 brands

I am trying to construct a query where given an aircraft class, I can retrieve a list of brands. The following SQL query works correctly:

SELECT * FROM brands
INNER JOIN aircraft_types ON brands.id = aircraft_types.brand_id
INNER JOIN aircraft_classes ON aircraft_types.aircraft_class_id = aircraft_classes.id
WHERE aircraftClassID = $class

I currently have:

$brands = DB::table('brands')
->join('aircraft_types', 'brands.id', '=', 'aircraft_types.brand_id')
->join('aircraft_classes', 'aircraft_types.aircraft_class_id', '=', 'aircraft_classes.id')
->where('aircraft_classes.id', '=', $class)
->get(array('brands.id', 'brands.brand_name'));

However, this will return multiple instances of the same brand. I am struggling to create an inner join between the tables so that a brand is only returned once.

Where am I going wrong?

Answer 1

IMHO your problem has nothing to do with Laravel since your base sql query is incorrect. As it is your query grabs brand info multiple times (as told by JOIN) if you have several aircraft types of the same brand.

1. First of all you don't need to join with aircraft_classes since you're filtering on aircraft_classes_id
2. Secondly to get distinct list of brands you have to use either DISTINCT or GROUP BY

Your sql query might look like this

SELECT id, brand_name 
  FROM brands 
 WHERE id IN
(
  SELECT DISTINCT brand_id
    FROM aircraft_types
   WHERE aircraft_class_id = 1
);

or

SELECT b.id, b.brand_name
  FROM aircraft_types t JOIN brands b
    ON t.brand_id = b.id
 WHERE aircraft_class_id = 1
 GROUP BY b.id, b.brand_name;

Here is SQLFiddle demo

Now your Laravel Query Builder code might look like

$brands = DB::table('aircraft_types AS t')
            ->join('brands AS b', 't.brand_id', '=', 'b.id')
            ->where('t.aircraft_class_id', '=', 1)
            ->groupBy('b.id', 'b.brand_name')
            ->select('b.id', 'b.brand_name')
            ->get();

Sample output of var_dump($brands);

array(2) {
  [0]=>
  object(stdClass)#133 (2) {
    ["id"]=>
    int(1)
    ["brand_name"]=>
    string(6) "Brand1"
  }
  [1]=>
  object(stdClass)#134 (2) {
    ["id"]=>
    int(2)
    ["brand_name"]=>
    string(6) "Brand2"
  }
}