在Eloquent ORM和Laravel中加入的问题

问题描述：

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?

我正在努力使用查询构建器在我的表之间创建内部联接。我有三个表具有以下关系： p>

aircraft_classes 1：m aircraft_types m：1个品牌 p>

我正在尝试构建一个给定的查询飞机类，我可以检索一个品牌列表。以下SQL查询正常工作： p>

  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 \  nWHERE aircraftClassID = $ class 
  code>  pre> 
 
 我目前有： p> 
 
 
  $ brands = DB :: table（'brands  '）
-＆gt; join（'aircraft_types'，'brands.id'，'='，'aircraft_types.brand_id'）
-＆gt; join（'aircraft_classes'，'aircraft_types.aircraft_class_id'，'='，  'aircraft_classes.id'）
-＆gt; where（'aircraft_classes.id'，'='，$ class）
-＆gt; get（array（'brands.id'，'brands.brand_name'））; \  n  code>  pre> 
 
 但是，这将返回同一品牌的多个实例。 我正在努力在表之间创建一个内部联接，这样一个品牌只返回一次。 p> 
 
 
我哪里出错？ p> 
  div>

答

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.

First of all you don't need to join with aircraft_classes since you're filtering on aircraft_classes_id
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
);

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

在Eloquent ORM和Laravel中加入的问题

相关推荐