0

I have a table like this:

enter image description here

I want to get distinct row from this. I use:

DB::table('dmspro_mys_campaign_product')->distinct()->get();

But it still give result with 3 rows. I expected 2 rows.

How I can fix this?

Tomato
  • 759
  • 2
  • 14
  • 26
  • Every row is distinct and so the `->distinct()` gives you a correct result. You want distinct with respect to which column? – nice_dev Oct 03 '19 at 07:47
  • I want to distinct with product_id column. But when I put product_id to distinct function, it still give me 3 column – Tomato Oct 03 '19 at 07:48
  • ok, do you use `Eloquent` models? If yes, what's the model name for `dmspro_mys_campaign_product`? Also, how would like other column values to be if you want distinct product_id? – nice_dev Oct 03 '19 at 09:15
  • I create a new post for clearly description. Hope you help me: https://stackoverflow.com/questions/58216579/left-join-with-a-distinct-query-in-laravel – Tomato Oct 03 '19 at 09:53

4 Answers4

1

Simply pass the field name inside the distinct function. like this(in case if you want distinct based on 'compeign_code' column)

DB::table('dmspro_mys_campaign_product')->distinct('compeign_code')->get();

in case if you want to distinct the field based on more than one column value then you can use the select method.

Sachin Kumar
  • 3,001
  • 1
  • 22
  • 47
  • my campaign_code is different each row. I tried put product_id into distinct function but it still give me 3 rows – Tomato Oct 03 '19 at 07:45
  • As you can see that the id field is different in each row. So in such cases, you will need to use the select method. See my updated answer – Sachin Kumar Oct 03 '19 at 07:48
1

In my opinion the answer is correct. District rows give you the results where every row is unique. Since there are slight variations between all three rows, you get all three because no row is exactly equal to another row.

I hope I could help, Sebastian

blickfangQ2
  • 39
  • 7
1

You need a distinct value from dmspro_mys_campaign_product table so Database query needs pass column name.

DB::table('dmspro_mys_campaign_product')->distinct('product_id')->get();
Amit Senjaliya
  • 2,867
  • 1
  • 10
  • 24
1

You simply need to mention the column name in distinct('compeign_code').

 DB::table('dmspro_mys_campaign_product')->distinct('compeign_code')->get();

Or you can try

 DB::table('dmspro_mys_campaign_product')->groupby('product_id')->distinct()->get();

If you want to get only one column then you may use this.

DB::table('dmspro_mys_campaign_product')->distinct()->select('product_id');
Dilip Hirapara
  • 14,810
  • 3
  • 27
  • 49
  • my campaign_code is different each row. I tried put product_id into distinct function but it still give me 3 rows – Tomato Oct 03 '19 at 07:47
  • if I add select, it will give me only one column. Anyway to get a row with distinct column – Tomato Oct 03 '19 at 07:58
  • I mentioned if you use select then you'll get only only one column – Dilip Hirapara Oct 03 '19 at 08:00
  • sorry! I try your second query with group by. I got error: Syntax error or access violation: 1055 'test.dmspro_mys_campaign_product.campaign_id' isn't in GROUP BY (SQL: select distinct * from `dmspro_mys_campaign_product` group by `product_id` – Tomato Oct 03 '19 at 08:03
  • https://stackoverflow.com/questions/41571271/group-by-not-working-laravel see this for group by – Dilip Hirapara Oct 03 '19 at 08:05