drz73366 2013-07-23 02:59
浏览 63
已采纳

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

  • 写回答

1条回答 默认 最新

  • dongtuan8547 2013-07-23 05:15
    关注

    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"
      }
    }
    
    本回答被题主选为最佳回答 , 对您是否有帮助呢?
    评论

报告相同问题?