How can I get unique records via query builder in Laravel?

Question

I am new to Laravel, can anyone help me with this how should I get unique records through this query in laravel query builder?

as of now, I'm getting duplicate records for example red, pink, red, pink, green

here is my query

MySQL query:

select count(*) as aggregate from (select `product`.`id`, `name`, `category`.`category`, group_concat(product_synonyms.product_synonym)as product_synonym, group_concat(product_tags.product_tag) as product_tag from `product` left join `product_tags` on `product`.`id` = `product_tags`.`product_id` left join `category` on `category`.`id` = `product`.`category_id` left join `product_synonyms` on `product`.`id` = `product_synonyms`.`product_id` where `user_id` = 1 and `product`.`deleted_at` is null group by `product_tags`.`product_id`) as `aggregate_table`;

Query builder:

Product::leftJoin('product_tags', 'product.id', 'product_tags.product_id')
            ->leftJoin('category', 'category.id', 'product.category_id')
            ->leftJoin('product_synonyms', 'product.id', 'product_synonyms.product_id')
            ->where('user_id', auth()->user()->id)
            ->select('product.id', 'name','category.category',DB::raw('group_concat(product_synonyms.product_synonym)as product_synonym'),DB::raw('group_concat(product_tags.product_tag)) as product_tag')
            ->groupBy('product_tags.product_id')
            ->orderBy('product.name', 'ASC')
            ->paginate(10);

I tried with different join and did many changes, seem I missing something and I couldn't figure it out, and ending with expecting something from a helping hand

Take a look at laravel.com/docs/8.x/collections#method-unique or w3schools.com/sql/sql_distinct.asp — JawadR1
– JawadR1, Commented Mar 18, 2021 at 7:59
Can you share more details? How should select count(*) return duplicate records? — Nico Haase
– Nico Haase, Commented Mar 18, 2021 at 8:15
@Basharmal @jerry555555 thanks, why couldn't I think about this function, this one helps :) with the use of distinct() it doesn't return duplicate — Marty
– Marty, Commented Mar 18, 2021 at 8:28

scorpions78 · Accepted Answer · 2021-03-18 08:11:42Z

0

For get only one result in your query you need use:

$productDetails = Product::leftJoin('product_tags', 'product.id', 'product_tags.product_id')
            ->leftJoin('category', 'category.id', 'product.category_id')
            ->leftJoin('product_synonyms', 'product.id', 'product_synonyms.product_id')
            ->where('user_id', auth()->user()->id)
            ->select('product.id', 'name','category.category',DB::raw('group_concat(product_synonyms.product_synonym)as product_synonym')**->first();**

for example.

Paginate if you need more result of query. If you need more than one result use ->get()

answered Mar 18, 2021 at 8:11

scorpions78

5586 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Basharmal · Accepted Answer · 2021-03-18 11:42:21Z

0

You can try this

Product::leftJoin('product_tags', 'product.id', 'product_tags.product_id')
        ->leftJoin('category', 'category.id', 'product.category_id')
        ->leftJoin('product_synonyms', 'product.id', 'product_synonyms.product_id')
        ->where('user_id', auth()->user()->id)
        ->select('product.id', 'name','category.category',DB::raw('group_concat(product_synonyms.product_synonym)as product_synonym'),DB::raw('group_concat(product_tags.product_tag)) as product_tag')
         ->distinct()
        ->groupBy('product_tags.product_id')
        ->orderBy('product.name', 'ASC')
        ->paginate(10);

answered Mar 18, 2021 at 11:42

Basharmal

1,3941 gold badge14 silver badges36 bronze badges

Collectives™ on Stack Overflow

How can I get unique records via query builder in Laravel?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related