How to merge 2 queries to work with pagination?

I currently have a query that sums the number of non-active users in the last year and groups them by country and city and then paginates the result:

    UserData::select(
            country,
            city,
            DB::raw('SUM(CASE WHEN end_date IS NULL THEN 0 ELSE 1 END) AS not_active'),
        )
        ->whereBetween('created_at', [Carbon::now()->subYear(), Carbon::now()])
        ->groupBy('country', 'city')
        ->paginate('500');

But I also need to add to each group a column that shows how many active users are of the same group, of all time, not just last year:

    UserData::select(
            country,
            city,
            DB::raw('SUM(CASE WHEN end_date IS NULL THEN 1 ELSE 0 END) AS active'),
        )
        ->groupBy('country', 'city')
        ->get();

Then in the frontend I want to display the data in a table so I want the data to be "merged" so that I can output the matching active and not_active columns together, so the end result would look like:

      country   |    city   | active(all time) | not active(past year)
    ------------|-----------|------------------|-----------------------
    Sweden      | Stockholm |         15       |           1
    Switzerland |   Bern    |         23       |           13

But how can it be done when using pagination?

I tried to do it with subqueries but that didn't work:

$result = UserData::select('end_date', 'country', 'city')
->where(function ($query) {
    $query->select('end_date')
    ->whereNull('end_date')
    ->whereBetween('created_at', [Carbon::now()->subYear(), Carbon::now()]);
}, 'active')
->where(function ($query) {
    $query->select('end_date')
    ->whereNotNull('end_date')
}, 'not_active')
->groupBy('country', 'city')
->paginate('100');

jlrdw

3 years ago

Level 75

Just a suggestion, sometimes it's easier to write separate queries, and a footer that summarizes data.

Usually in a report a final totals summary is a separate query.

Since you are dealing with such things I suggest getting MS Access or LibreOffice base or other and play with some reports to better understand reports.

Bear in mind you have sometimes:

Running totals
Final Totals
Each section totals

All depends on what you need to present. Just examples:

Running sums:

Summary sums:

Basic counting:

$quy = Powner::query()->leftJoin('dc_pets', 'dc_powners.ownerid', '=', 'dc_pets.ownerid')
                ->select('dc_powners.ownerid', 'dc_powners.oname')
                ->selectRaw('count(dc_pets.petid) as countOfPets')
                ->groupby('dc_powners.ownerid')
                ->orderby('dc_powners.oname')
                ->get();

Results basically give:

ownerid, oname, countOfPets

Like:

5|Bob|3
4|Greg|9
2|Rob|1

If pagination needed use a lengthawarepaginator.

See https://www.mysqltutorial.org/mysql-count/

And

https://www.mysqltutorial.org/ to look up other good tutorials.

Please or to participate in this conversation.