拉拉维尔连接三张桌子

Question

我有三张表如下：

invent_accessory id，代码，详细信息，is_length

invent_price_accessory id，invent_accessory_id_fk，价格，颜色

invent_price_accessory_length id，invent_accessory_id_fk，价格，颜色

我如何组合这三个表，以便我可以得到id，代码，detal，is_length，价格，颜色

我已经做了以下工作，但它没有返回任何数据：

 public function getAccessoryInventPrice(){

  $accessory=DB::table('invent_accessory')
        ->select('invent_accessory.id as accessory_id')
        ->join('invent_price_accessory','invent_price_accessory.invent_accessory_id_fk','=','invent_accessory.id')
        ->join('invent_price_accessory_length','invent_price_accessory_length.invent_accessory_id_fk','=','invent_accessory.id')
      ->get();

  return $accessory;

 }

我猜我必须先做invent_price_accessory和invent_price_accessory_length之间的联合，然后加入invent_accessory？我该怎么做？请帮帮忙。

Answer 1

我猜对了！！我通过以下方式解决了这一问题：

  public function getAccessoryInventPrice(){


    $data1=\DB::table('invent_price_accessory')->select('accessory_code_id','color_id','rate');
    $data2=\DB::table('invent_price_acc_length')->select('accessory_code_id','color_id','rate')
              ->union($data1);

    $query= \DB::table(\DB::raw("({$data2->toSql()}) as invent_price"))

            ->join('invent_accessory','invent_price.accessory_code_id','=','invent_accessory.id')
            ->join('invent_color','invent_price.color_id','=','invent_color.id')
            ->select('invent_accessory.id','code','sl_detail','is_length','rate','invent_color.name')
            ->orderBy('invent_accessory.id','asc')
            ->get();
              return $query;

 }