laravel hydrateRaw/fromQuery and eager loading with pagination

Question

I currently found out that you can hydrate an Raw sql query.

I have following query:

DB::table(DB::raw('(SELECT *, Y(location) AS longitude, X(location) AS latitude FROM meetings WHERE MBRCONTAINS(@quadrat, location)) AS sub'))
            ->select(DB::raw('(FLOOR(SQRT(POW((@ibk_breite - sub.latitude) * 111, 2) + POW((@ibk_laenge - sub.longitude) * 111 * ABS(COS(RADIANS(@ibk_breite))),2)))) AS distance, sub.*, latitude, longitude'));

which I hydrate as following

$meetings = Meeting::fromQuery($query->toSql());

In the blade view i need to get some additional data from different tables, for example:

 $meeting->user

which references to the User Model. But if I'm not complety wrong that would result to a n+1 problem in a for each loop, because I'm not eager loading it?! So is it possible to eager load the required models as you would normally do with

->with('user', 'books', 'etc...')

??

Also is it possible to paginate it like $meetings = $query->paginate(5); and do $meetings->withPath('home');

EDIT: Found a solution:

// Do your query stuff
 // Get count before the query because it won't work with skip and take     parameter
    $count = $query->count();

    $query->skip($skip);
    $query->take($meetingsPerPage);
    $meetings = Meeting::fromQuery($query->toSql());
    $meetings->load('user', 'interest.image', 'img_thumbnail');
    $meetings = new LengthAwarePaginator($meetings, $count, $meetingsPerPage);

$meetings->load acts as ->with(). As last step you need to create a paginator. IMPORTANT: Use query->count() before you set skip() and/or take() Otherwise it will not work.

Original answer from laracasts. Theres also another possibily stated that didn't work for me.

I asked in another forum and got this part of answer [link](https://laracasts.com/discuss/channels/laravel/hydraterawfromquery-with-pagination) — Ronon, Jul 11 '17 at 22:47
@Ronon You have no idea how much frustration this just saved me — sheng, Nov 05 '18 at 04:34
@Ronon It would be better if you add your answer as a actual answer and mark it as accepted instead of providing the answer in the question. See [self answer documentation](https://stackoverflow.com/help/self-answer). Thanks. — Luís Cruz, Oct 14 '19 at 14:47

score 2 · Answer 1 · answered Mar 27 '20 at 09:35

You can use setCollection() function with LengthAwarePaginator object (found in Illuminate/Pagination/AbstractPaginator.php). Use load() for eager loading.

 $users = DB::table('users')->paginate();
 $users->setCollection(User::hydrate($users->items())->load(['category']));

score 1 · Accepted Answer · answered Oct 14 '19 at 18:15

My solution:

// Do your query stuff
// Get count before the query because it won't work with skip and take parameter
$count = $query->count();

$query->skip($skip);
$query->take($meetingsPerPage);
$meetings = Meeting::fromQuery($query->toSql());
$meetings->load('user', 'interest.image', 'img_thumbnail');
$meetings = new LengthAwarePaginator($meetings, $count, $meetingsPerPage);

$meetings->load acts as ->with(). As last step you need to create a paginator. IMPORTANT: Use query->count() before you set skip() and/or take() Otherwise it will not work.

Original answer from laracasts. Theres also another possibily stated that didn't work for me.

Awsm! The Eloquent collection "load()" method is exactly what I was looking for! — bhu Boue vidya, Mar 15 '21 at 11:27

laravel hydrateRaw/fromQuery and eager loading with pagination

2 Answers2