Is it possible to "mimic" group_by and order_by after a query?

I have a base query which needs to be done a few times with the only difference between them is the queryBy and orderBy columns. The results are supposed to be loaded into the same page. So I don't want to do separate queries because it will take time

For example, this is the base query:

MyModel::where()
->select(DB::raw('SUM(col1) as col1, SUM(col3) as col1, SUM(col3) as col3'))

But it has very similar variations:

MyModel::where()
->select(DB::raw('SUM(col1) as col1, SUM(col3) as col1, SUM(col3) as col3'))
->groupBy('col1')
->get()

or:

MyModel::where()
->select(DB::raw('SUM(col1) as col1, SUM(col3) as col1, SUM(col3) as col3'))
->groupBy('col1')->groupBy('col2')
->orderBy('col1')
->get()

or:

MyModel::where()
->select(DB::raw('SUM(col1) as col1, SUM(col3) as col1, SUM(col3) as col3'))
->groupBy('col1')->groupBy('col2')->groupBy('col3')
->orderBy('col1')->orderBy('col2')
->get()

So instead of calling the queries separately, can I somehow do "mimic" the groupBy and orderBy after the query? Or I should instead get the base data, and use PHP to do whatever I need?

tykus

3 years ago

Level 104

The resulting Collection has groupBy and sortBy methods to allow you to perform such operations - however, I would expect a small number of database queries to be more performant than iterating over a PHP array the same number of times.

1 like

Ligonsker

3 years ago

Level 8

@tykus the problem is there is a lot of data in that specific table, from some reason it takes long time to do these queries (1 minute), so I could try that as well? I'm going to try the groupBy and sortBy now

The actual queries are not only selecting from a table with a lot of data, but also sum dozens of columns, more than 60 I believe (It's a big table)

Ligonsker

3 years ago

Level 8

@tykus update: It's not working as expected. I do get a collection, but there's an array inside it.

This is the original collection I get from using ->get():

Illuminate\Database\Eloquent\Collection
    #items: array: 1 [
        0 => App\Models\MyModel

but then when I do:

$collection->groupBy('col1'), it nests it inside "":

Illuminate\Database\Eloquent\Collection
    #items: array: 1 [
        "": Illuminate\Database\Eloquent\Collection
            #items: array: 1 [
                0 => App\Models\MyModel
```

Please or to participate in this conversation.