laravel join关联查询代码实例

laravel join关联查询

1、两表关联

$fbaInventoryTb = (new \\App\\Model\\Amz\\Fba\\InventoryReport)->getTable();
$productTb = (new \\App\\Model\\Amz\\Product)->getTable();
$twInventoryTb = (new \\App\\Model\\TWUsa\\TwusaInventory)->getTable();
$qry = \\DB::table($fbaInventoryTb);
  $qry->select($fbaInventoryTb.\'.*\')
   ->where($fbaInventoryTb.\'.ec_id\',1)
   ->leftjoin($productTb, $productTb.\'.sku\', \'=\', $fbaInventoryTb.\'.sku\')
   ->addSelect($productTb.\'.id as goods_id\',$productTb.\'.sku as mfn\', $productTb.\'.ec_id as pro_ec_id\');
//  ->where($productTb.\'.ec_id\',1); //不应该在此处排除 product 表的ec_id

//  return $qry->toSql();
  $res = $qry->get()->whereIn(\'pro_ec_id\',[1, null] );//leftJion 最后排除不符合条件的

2、三表关联

$qry = \\DB::table($twInventoryTb);
  $qry->select($twInventoryTb.\'.*\')
   ->leftjoin($fbaInventoryTb, $fbaInventoryTb.\'.fnsku\', \'=\', $twInventoryTb.\'.product_sn\')
   ->addSelect($fbaInventoryTb.\'.ec_id\')
   ->where($fbaInventoryTb.\'.ec_id\',1);

  $qry->LeftJoin($productTb, $productTb.\'.sku\', \'=\', $fbaInventoryTb.\'.sku\')
//   ->where($productTb.\'.ec_id\',1)
   ->addSelect($productTb.\'.id as goods_id\', $productTb.\'.sku as mfn\', $productTb.\'.ec_id as pro_ec_id\');

  $res = $qry->get()->whereIn(\'pro_ec_id\', [1, null]);