Laravel Query Multiple Models and Combine Results

0
votes

In my app I return a view that contains all the records within the Posts model. I now have another model that I want to return the results of and in that same view I want to combine the query results of the Post model and this new model. Once combined, I want to order the results by the "created_at" date.

I have been attempting to use the Union method on the query builder, but I'm getting the error "The used SELECT statements have a different number of columns...". It might be easier to just create an overall table that holds all the results from these models as well as others I create in the future.

Controller:

$connectionsPosts = Post::where(function($query) {
        return $query->where('user_id', Auth::user()->id)
            ->orWhereIn('user_id', Auth::user()->following()->lists('connection_id'));
    });

$request = DB::table('request')->union($connectionsPosts)->get();

return view('main.discover.connections')
    ->with([
        'connectionsPosts' => $request,
    ]);

UPDATE:

View:

@if ($connectionsPosts->count())
    @foreach ($connectionsPosts as $Posts)
        // Provide markup to loop through results
    @endforeach
@endif

I'm trying to achieve something like this

@foreach ($allResults as $results)
    // Some markup that includes both connectionsPosts and request based on the "created_at" date
@endforeach
2
laravellaravel-5.2

2 Answers

4
votes

Use Laravel Collections for that.

In your example, you have your first result stored in $request. Let's say you have some other result stored in $otherRequest.

First use the merge method to merge both results:

$mergedRequest = $request->merge($otherRequest);

Then use the sortBy method:

$sorted = $mergedRequest->sortBy('created_at');
2
votes

It's not my business, but why you didn't do so:

return view('main.discover.connections')
    ->with([
        'connectionsPosts' => $connectionsPosts,
        'request' => $request,
    ]);

However try this

$connectionsPosts = Post::where(function($query) {
        return $query->where('user_id', Auth::user()->id)
            ->orWhereIn('user_id', Auth::user()->following()->lists('connection_id'));
    })->get();

$request = DB::table('request')->get();

$connectionPostsAndRequests = $request->merge($connectionsPosts);
$connectionPostsAndRequests->sortBy('created_at');

return view('main.discover.connections')
    ->with([
        'connectionsPosts' => $connectionPostsAndRequests,
    ]);

don't know will it work or not