Laravel Query Multiple Models and Combine Results

Question

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

crabbly · Accepted Answer · 2016-05-31 04:21:39Z

5

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');

answered May 31, 2016 at 4:21

crabbly

5,2261 gold badge26 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

huuuk · Accepted Answer · 2016-05-31 04:24:57Z

3

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

edited May 31, 2016 at 4:24

answered May 31, 2016 at 4:05

huuuk

4,8052 gold badges22 silver badges28 bronze badges

4 Comments

Matt Pierce Over a year ago

Definitely your business since I'm asking for your help :). I didn't do that because I want the results from both tables to be mixed together based on their "created_at" values. If I do it as you suggest, I believe that I'll have one section of $connectionsPosts results and another of $request results. I could be wrong though.

huuuk Over a year ago

Sections? You'll get 2 variables in your view connectionsPosts and request. Error occurs because your 2 queries has different number of columns. Try to set columns directly.

Matt Pierce Over a year ago

I don't think I'm describing this well. Please see my updated question containing how I loop through the results in the view and what I'm trying to achieve. Thanks!

huuuk Over a year ago

Forget to call ->sortBy('created_at') on the merged collection.

Collectives™ on Stack Overflow

Laravel Query Multiple Models and Combine Results

2 Answers 2

Comments

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related