How to prepare the data so that I can count results by lowest selected hierarchy?,

I have 4 dropdowns in the frontend, each representing a hierarchy level in the company:

    Groups > Units > Departments > Teams

So users can select a group then it will only show units of that group and departments of that group and so on..

But users can also select multiple choices so they can select Group1, Group2, and it will show all the corresponding hierarchy "items".

I let the users select however they want - they can choose any department, any group and team and so on.

The goal is to count the number of results from the lowest of each selected hierarchy.

Example:

id  |  username  |  group  |  unit  |  department  |  team
----|------------|---------|--------|--------------|---------
 1  |    user1   |   g1    |   u1   |      d1      |   t1
 2  |    user2   |   g1    |   u1   |      d1      |   t2
 3  |    user3   |   g3    |   u6   |      d12     |   t30
 4  |    user4   |   g25   |   u54  |      d70     |   t88

The way I currently do it is to clean the selections so if for example a user clicked g1>u1>d1 and g25>u54>d70>t88, I build it with query builder like so:

    foreach ($data as $row) {
        $myQuery->orWhere(function($query) use ($filter) {
            foreach ($filter as $column => $value) {
                $query->where($column, '=', $value)
            }
        }
    }

So the the raw SQL query would result in this:

    SELECT * FROM table
    WHERE (group='g1' AND unit='u1' AND department='d1')
    OR WHERE (group='g25' AND unit='u54' AND department='d70' AND team='t88')

But I need to return the count for each, not the actual results.

I could do something like the following using UNION:

    SELECT group
         , unit
         , department
         , NULL as team
         , COUNT(*) AS rows
      FROM table
     WHERE group='g1' 
       AND unit='u1' 
       AND department='d1'
    GROUP
        BY group
         , unit
         , department
    UNION ALL
    SELECT group
         , unit
         , department
         , team
         , COUNT(*) AS rows
      FROM table
     WHERE group='g25' 
       AND unit='u54' 
       AND department='d70' 
       AND team='t88'
    GROUP
        BY group
         , unit
         , department
         , team

But I am not sure I can convert it do work with Query Builder.

Also, I feel like maybe the entire way I organize the data or build the initial query is wrong for the purpose.

Should I arrange the data differently completely? Or there is a way to achieve what I'm trying in this way?

tisuchi

3 years ago

Level 70

@ligonsker You can try this:

DB::table('table')
    ->select('group', 'unit', 'department', DB::raw('NULL as team'), DB::raw('COUNT(*) as rows'))
    ->where([
        ['group', '=', 'g1'],
        ['unit', '=', 'u1'],
        ['department', '=', 'd1'],
    ])
    ->groupBy('group', 'unit', 'department')
    ->union(DB::table('table')
        ->select('group', 'unit', 'department', 'team', DB::raw('COUNT(*) as rows'))
        ->where([
            ['group', '=', 'g25'],
            ['unit', '=', 'u54'],
            ['department', '=', 'd70'],
            ['team', '=', 't88'],
        ])
        ->groupBy('group', 'unit', 'department', 'team')
    )
    ->get();

Or you can use this also:

$firstQuery = DB::table('table')
    ->select('group', 'unit', 'department', DB::raw('NULL as team'), DB::raw('COUNT(*) AS rows'))
    ->where('group', 'g1')
    ->where('unit', 'u1')
    ->where('department', 'd1')
    ->groupBy('group', 'unit', 'department');

$secondQuery = DB::table('table')
    ->select('group', 'unit', 'department', 'team', DB::raw('COUNT(*) AS rows'))
    ->where('group', 'g25')
    ->where('unit', 'u54')
    ->where('department', 'd70')
    ->where('team', 't88')
    ->groupBy('group', 'unit', 'department', 'team');

$result = $firstQuery->unionAll($secondQuery)->get();

ℹ️ Don't forget to eager loading (where necessary) in order to reduce the number of queries to improve the performance.

1 like

Please or to participate in this conversation.