CODEWITHSUNDEEP
phplaraveleloquenttreedistinct
pal3
pal3

Reputation: 241

List tree view using eloquent

I have this table structure below

TABLE VIEW

+------+---------------+---------------+---------------+
|id    |  group_name   |   sub_group   |    title      |
+------+---------------+---------------+---------------+
|1     |  Mobile Phone |   Apple       |  Iphone X     |
|2     |  Mobile Phone |   Apple       |  Iphone 7 Plus|
|3     |  Mobile Phone |   Samsung     |  J6 Plus      |
|4     |  Mobile Phone |   Nokia       |  XpressMusic  |
+------+---------------+---------------+---------------+

How to loop this table and store in an array and look like this

LOOP

Mobile Phone
    -> Apple
        -> Iphone X
        -> Iphone 7 Plus
    -> Samsung
        -> J6 Plus
    -> Nokia
        -> XpressMusic

This is my code using eloquent, I've run a distinct under group_name sub_group but I have no idea how am I gonna get the title based on group_name and sub_group

$group_names = ThisIsModel::distinct()->orderBy('group_name', 'asc')->get(['group_name']);

I don't know what to do next. Please help me. Thanks

Upvotes: 1

Views: 290

Answers (2)

Moshiur
Moshiur

Reputation: 685

You can do it by using groupBy()

$group_names = Employee::query()
            ->orderBy('sub_group', 'asc')
            ->get()
            ->groupBy('group_name','sub_group');

Then you will have a collection on $group_names with key like Mobile phone and subgroups like apple, samsung etc.

Upvotes: 0

4givN
4givN

Reputation: 3244

Normally you cannot have that easily without having 03 separates table and using eloquent relations like here.

But for you case with single table, maybe this works ?

ThisIsModel::select(DB::raw("group_concat(distinct(group_name)) as group_name, group_concat(distinct(sub_group)) as sub_group, group_concat(title) as title"))->groupBy('sub_group', 'group_name')->get()->groupBy('group_name')->toArray();

which gives result as follow:

[
     "Mobile Phone" => [
       [
         "group_name" => "Mobile Phone",
         "sub_group" => "Apple",
         "title" => "Iphone X,Iphone 7 Plus",
       ],
       [
         "group_name" => "Mobile Phone",
         "sub_group" => "Nokia",
         "title" => "XpressMusic",
       ],
       [
         "group_name" => "Mobile Phone",
         "sub_group" => "Samsung",
         "title" => "J6 Plus",
       ],
     ],
   ]

To get the leaves on title, you just need to explode with , separator and you have all in one query.

Upvotes: 1

Related Questions