CODEWITHSUNDEEP
phpmysqlsqldatabaselaravel
Plic Pl
Plic Pl

Reputation: 551

Laravel - Merge two Queries?

I am building a Twitter-like application which has a feed in it. In this feed I need to display shares with this properties:

-Shares from user, who I am following

-Shares from user, which are sorted by the positive rating, but only the top10%

These two queries I need somehow to merge, so it will become an array in the end, which has all the shares which are applying to this criteria, without any duplicates and ordered by ID, desc

My tables are looking like this:

User, Shares, Follows

Shares:
-user_id
-positive

Follows:
-follower_id
-user_id
-level

What I already tried:

$follower_shares = Share::join('follows', 'shares.user_id', '=', 'follows.user_id')
        ->where('follows.follower_id', Auth::user()->id)
        ->where('follows.level', 1)
        ->get(array('shares.*'));


//count = 10% of total shares
$count = Share::count()/10;
$count = round($count);


$top10_shares = Share::orderBy('positive', 'DESC')
->take($count)
->get();


//sorts by id - descending
$top10_shares = $top10_shares->sortBy(function($top)
{
    return -($top->id);
});


//merges shares
$shares = $top10_shares->merge($follower_shares);

The problem is now, that I was told that there is a better way to solve this. Also, $shares is giving me the result which applies to the criteria, but the shares have duplicates (rows, which are applying to both criteria) and arent ordered by id desc in total.

I would be very happy, if you could tell me, how to do this the right way.

Thanks very much!

Upvotes: 1

Views: 4579

Answers (1)

rmobis
rmobis

Reputation: 27021

I found this to be a pretty clean solution:

// Instead of getting the count, we get the lowest rating we want to gather
$lowestRating = Share::orderBy('positive', 'DESC')
                    ->skip(floor(Share::count() * 0.1))
                    ->take(1)->lists('positive');

// Also get all followed ids
$desiredFollow = Auth::user()->follows()->lists('user_id');

// Select where followed or rating higher than minimal
// This lets the database take care of making them distinct
$shares = Share::whereIn('user_id', $desiredFollow)
               ->orWhere('positive', '>=', $lowestRating[0])
               ->get();

Upvotes: 3

Related Questions