Laravel/Eloquent pagination and groupBy for a postgres materialized view model

Question

I've created a materialized view: accounts_index that has a couple left joins, so I need to do a group by with the eloquent model. I also need to paginate the collection and am having trouble nailing the order of things. The materialized view:

CREATE materialized VIEW accounts_index
        AS SELECT a.account_uuid,
                  a.account_no,
                  a.person_owner_uuid,
                  a.company_owner_uuid,
                  a.account_group_uuid,
                  a.account_scope_uuid,
                  a.created_at,
                  a.deleted_at,
                  s.service_uuid,
                  s.status,
                  st.service_type
        FROM   accounts a
                LEFT JOIN services s
                        ON a.account_uuid = s.account_uuid
                LEFT JOIN service_types st
                        ON s.service_type_uuid = st.service_type_uuid

The eloquent model can grab that table like so: AccountIndex::all();. I could paginate that: AccountIndex::paginate(); or do a groupBy: AccountIndex::all()->groupBy('account_uuid');, but lost on how to combine the two. Some attempts:

AccountIndex::all()->groupBy('account_uuid')->paginate(): Method Illuminate\Database\Eloquent\Collection::paginate does not exist.
AccountIndex::paginate()->groupBy('account_uuid');: returns the collection without paginating.
$accountsIndex = collect(\DB::table('accounts_index')->get()); $accountsIndex->groupBy('account_uuid')->paginate();: Same Method Illuminate\Support\Collection::paginate does not exist. exception.

The main problem I'm trying to solve is that these joins will be returning accounts that have multiple services (so multiple rows) and I need to then group by the account_uuid. Thanks in advance for any insights!

score 0 · Answer 1 · answered Jun 19 '19 at 21:34

0

paginate method is related to Illuminate\Database\Query\Builder or Illuminate\Database\Eloquent\Builder classes, it is not exists in Collection class, the corresponding method for it in Collection class is forPage method, you can get more information from the documentation

answered Jun 19 '19 at 21:34

Aboudeh87

716
5
16

That loses all pagination meta-data. I want to group by first and then paginate with full meta-data. – Matt Larsuma Jun 19 '19 at 21:38
so you should to apply groupBy on database level but then you will have only one row for each account – Aboudeh87 Jun 19 '19 at 21:40
That would lose the multiple services that each account could have. – Matt Larsuma Jun 19 '19 at 21:44

score 0 · Answer 2 · answered Apr 09 '21 at 05:57

almost

AccountIndex::groupBy('account_uuid')->paginate()

paginate actually DOES something

groupBy() is just writing SQL just like ->where() ->join() etc that all prepares an SQL statement you can try by running ->toSql() returns an sql string.

get() all() paginate() all actually do something e.g. execute the SQL.

Laravel/Eloquent pagination and groupBy for a postgres materialized view model

2 Answers2