Laravel group by and unique values for each group

Question

I have a table for Specifications and the table contains specification_key , specification_value.

I'm trying to group all specifications by the specification_key and also each group must contain only unique values.

I tried:

        $specifications = Specification::whereIn('product_id', $products->pluck('id'))->get();

        $SpecsGroup = $specifications->groupBy('specification_key');

        dd($SpecsGroup);

This returns groups by specification_key but these contain the same specification_value multiple times.

Illuminate\Database\Eloquent\Collection {#1572 ▼
    #items: array:12 [▼
      "RAM" => Illuminate\Database\Eloquent\Collection {#1522 ▼
        #items: array:4 [▼
          0 => App\Models\Specification {#1607 ▼
            #attributes: array:6 [▼
              "id" => 1
              "product_id" => 1
              "specification_key" => "RAM"
              "specification_value" => "16 GB"
              "created_at" => "2021-03-21 15:35:21"
              "updated_at" => "2021-03-21 15:35:21"
            ]
          }
          1 => App\Models\Specification {#1586 ▼
            #attributes: array:6 [▼
              "id" => 51
              "product_id" => 2
              "specification_key" => "RAM"
              "specification_value" => "16 GB"
              "created_at" => "2021-04-16 15:38:23"
              "updated_at" => "2021-04-16 15:38:23"
            ]
          }
          2 => App\Models\Specification {#1584 ▼
            #attributes: array:6 [▼
              "id" => 55
              "product_id" => 3
              "specification_key" => "RAM"
              "specification_value" => "8 GB"
              "created_at" => "2021-04-16 16:36:03"
              "updated_at" => "2021-04-16 16:36:03"
            ]
          }
          3 => App\Models\Specification {#1494 ▶}
        ]
      }
      "Brand" => Illuminate\Database\Eloquent\Collection {#1503 ▶}
      "Manufacturer" => Illuminate\Database\Eloquent\Collection {#1562 ▶}
      "Model" => Illuminate\Database\Eloquent\Collection {#1563 ▶}
      "Model Year" => Illuminate\Database\Eloquent\Collection {#1564 ▶}
      "Product Dimensions" => Illuminate\Database\Eloquent\Collection {#1565 ▶}
      "Model Name" => Illuminate\Database\Eloquent\Collection {#1566 ▶}
      "LED" => Illuminate\Database\Eloquent\Collection {#1567 ▶}
      "Size" => Illuminate\Database\Eloquent\Collection {#1568 ▶}
      "Pack" => Illuminate\Database\Eloquent\Collection {#1569 ▶}
      "Fan Controller" => Illuminate\Database\Eloquent\Collection {#1570 ▶}
      "Warranty" => Illuminate\Database\Eloquent\Collection {#1571 ▶}
    ]
  }

I want the values to be unique as in the above it currently returning the same specification_value multiple times.

Like if it has "16 GB RAM" 2 times it should return 1 only 1 time "16 GB RAM"

How can I do that?

Thanks in advance, I've been trying this for the last 2 hours :(, now StackOverflow is the last hope.

Can u show some expected result also? maybe it can help others to figure it out — Wailan Tirajoh, Apr 16 '21 at 12:13
`should return 1 only 1 time "16 GB RAM"` > if the data has 2 items. Which items do you want to display? Newest or oldest? — Wahyu Kristianto, Apr 16 '21 at 13:06
Anything would do the job Oldest or Newest, I just want it only 1 time — Aniket Das, Apr 16 '21 at 13:15

score 2 · Accepted Answer · answered Apr 16 '21 at 14:11

You can do simply this way:

$data = Specification::whereIn('product_id', $products->pluck('id'))
    ->groupBy(['specification_key', 'specification_value']) // group by query
    ->get()
    ->groupBy('specification_key'); // group by collection

array:1 [
  "RAM" => array:2 [
    0 => array:6 [
      "id" => 1
      "product_id" => 1
      "specification_key" => "RAM"
      "specification_value" => "16 GB"
      "created_at" => "2021-03-21T15:35:21.000000Z"
      "updated_at" => "2021-03-21T15:35:21.000000Z"
    ]
    1 => array:6 [
      "id" => 3
      "product_id" => 3
      "specification_key" => "RAM"
      "specification_value" => "8 GB"
      "created_at" => "2021-04-16T16:36:03.000000Z"
      "updated_at" => "2021-04-16T16:36:03.000000Z"
    ]
  ]
]

If you have problem with Syntax error or access violation: 1055, you can follow this answer.

Edit your applications's database config file config/database.php

In mysql array, set strict => false to disable MySQL's strict mode

If you want to display specification_value only, you can use mapToGroups() :

$data = Specification::whereIn('product_id', $products->pluck('id'))
    ->groupBy(['specification_key', 'specification_value']) // group by query
    ->get()
    ->mapToGroups(function ($item, $key) {
        return [$item['specification_key'] => $item['specification_value']];
    });

array:1 [
  "RAM" => array:2 [
    0 => "16 GB"
    1 => "8 GB"
  ]
]

What is the difference between a group by query and a group by collection? — Petar Vasilev, Apr 26 '22 at 19:01
@PetarVasilev The process is different. Via query will be the same as `GROUP BY` query. Meanwhile, using collection is processing after getting data from the database. — Wahyu Kristianto, Apr 27 '22 at 19:23
That is not the only difference. The group by collection separates the results in a different way. — Petar Vasilev, May 02 '22 at 10:44

score 1 · Answer 2 · answered Apr 16 '21 at 13:43

1

After trying for hours I got this myself, I'm still not sure how I did this but it works. :D

        $specifications = Specification::whereIn('product_id', $products->pluck('id'))
        ->distinct('specification_value')
        ->groupBy('specification_value')
        ->distinct('specification_key')
        ->groupBy('specification_key')
        ->get()
        ->groupBy('specification_key');

answered Apr 16 '21 at 13:43

Aniket Das

367
1
6
17

I have used your code but it is not working for me, first I used the same way but first time also I didn't get the desired answer! – Hedayatullah Sarwary Apr 16 '21 at 16:45

Hedayatullah Sarwary · Answer 3 · 2021-04-17T16:18:09.390

0

Use this code:

$spec_values = ['8 GB', '16 GB'];
$SpecsGroup = collect();
foreach($spec_values as $spec_value) {
    $SpecGroup = Specification::whereIn('product_id', $products->pluck('id'))->where('specification_value', $spec_value)->first();
    if ($SpecGroup !== null) {
        $SpecsGroup->push($SpecGroup);
    }
}
dd($SpecsGroup);

edited Apr 17 '21 at 16:18

answered Apr 16 '21 at 12:35

Hedayatullah Sarwary

2,664
3
24
38

This returns an error: BadMethodCallException Method Illuminate\Database\Eloquent\Collection::distinct does not exist. – Aniket Das Apr 16 '21 at 12:43
Still the same error, I had also tried it myself. – Aniket Das Apr 16 '21 at 12:51
Ok, let me check it myself. – Hedayatullah Sarwary Apr 16 '21 at 12:55
distinct() is not working with wherein but using the above groupby is working. – Hedayatullah Sarwary Apr 16 '21 at 14:03
1

Thank you for your time :) but the above answer of @Wahyu Kristianto worked for me. – Aniket Das Apr 16 '21 at 14:57

score -3 · Answer 4 · edited Apr 16 '21 at 12:54

-3

You can use ->unique()

$specifications = Specification::whereIn('product_id', $products->pluck('id'))->unique();

edited Apr 16 '21 at 12:54

Wahyu Kristianto

8,719
6
43
68

answered Apr 16 '21 at 12:21

user123

9
2

Laravel group by and unique values for each group

4 Answers4