List tree view using eloquent

Question

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

4givN · Accepted Answer · 2019-10-11T07:33:48.827

1

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.

edited Oct 11 '19 at 07:33

answered Oct 10 '19 at 12:00

4givN

2,936
2
22
51

I will try this one and let you know – pal3 Oct 11 '19 at 00:46
I am confused with 'test' and 'DB' is that a model? – pal3 Oct 11 '19 at 01:43
sorry about `test`, it should be your `ThisIsModel`. The `DB::raw( )` allows you to write raw sql in `select` with eloquent way – 4givN Oct 11 '19 at 07:34
I got this error `Class 'App\Controllers\DB' not found` – pal3 Oct 14 '19 at 01:47
You have to import it in the file `use Illuminate\Support\Facades\DB;` – 4givN Oct 15 '19 at 09:43

Moshiur · Answer 2 · 2019-10-12T06:33:54.833

0

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.

edited Oct 12 '19 at 06:33

answered Oct 10 '19 at 10:03

Moshiur

659
6
22

so what it returned ? Is there any error or you are not getting expected array? – Moshiur Oct 12 '19 at 03:17

List tree view using eloquent

2 Answers2