在Laravel中用独特的查询左联接 [英] Left join with a distinct query in Laravel

问题描述

我有2张这样的桌子:

product_master:

product_master:

campaign_product:

campaign_product:

我使用此查询从product_master和campaign_product表中获取数据:

I use this query to get data from product_master and campaign_product tables:

$oSelect = $this->select(
    "{$this->table}.product_code",
    "{$this->table}.product_display_name",
    'dmspro_mys_campaign_product.campaign_code',
    'dmspro_mys_campaign_product.product_id',
)->leftJoin(
    'dmspro_mys_campaign_product', 
    \DB::raw("SELECT DISTINCT product_id FROM dmspro_mys_campaign_product"), '=', "{$this->table}.product_id"
);

return $oSelect->get();

但是您可以看到，在我的campaign_product表中，有2行相同的product_id，所以我的结果是重复的行:

But as you can see, in my campaign_product table, there are 2 rows same product_id, so my result is duplicated row:

现在，我希望它通过不同的查询仅返回1个product_id. 我试过了:

Now I want it return only 1 product_id by distinct query. I tried this:

 ->leftJoin(\DB::raw("(SELECT DISTINCT product_id FROM dmspro_mys_campaign_product) as cp"), 'cp.product_id', '=', "{$this->table}.product_id");

但这是关于我的查询语法的错误.

But it's error about my query syntax.

我该怎么做?

推荐答案

如果您使用的是MySQL，请先运行以下查询，然后再运行主查询.

If you're using the MySQL, Run the below query before run your main query.

SET SESSION sql_mode=(SELECT REPLACE(@@sql_mode,'ONLY_FULL_GROUP_BY', ''));

这篇关于在Laravel中用独特的查询左联接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！