CODEWITHSUNDEEP
laraveleloquent
crotoan
crotoan

Reputation: 183

Laravel - PopularityCount attribute - Order By

I have a problem in ordering/sorting results. Basically I have posts table, and I count popularity_count based on how many comments and likes it have. The thing is that I also paginate results. So when I use something like this

$Posts->paginate(5);
$Posts->sortBy('popularity_count');
$Posts->get();

It sorts only for specific page, so eg. page 1 has results with popularity count like: 6,5,4,3,2, and second page has 10,7,5,2,1. As you can see there is posts with popularity 10 and it should be as the first result on the first page.

When I try to use

 $Posts->orderBy('popularity_count')

it doesn't work as I don't have column in my database like this. Is there any possibility to achieve what I want without using RAW selects and joins? I have more custom Attributes on my Model.

Thanks!

edit:

`public function getPopularityCountAttribute(){

    $comments = $this->getRelation('commentsCount');

    $likes = $this->getRelation('likesCount');


    $comments = ($comments) ? (int) $comments->comments_count : 0;

    $likes = ($likes) ? (int) $likes->likes_count : 0;

    $Result = $likes + ( $comments * 1.2 );

  return $Result;
}`

Upvotes: 2

Views: 804

Answers (2)

Paul Spiegel
Paul Spiegel

Reputation: 31802

A fast solution (in terms of implementation) is to use oderByRaw():

$Posts->orderByRaw("
    (
        select count(*)
        from likes
        where likes.post_id = posts.id
    ) + (
        select count(*)
        from comments
        where comments.post_id = posts.id
    ) * 1.2 DESC
");

This however will be very slow if your posts table is huge. You could try to create a query with two joins and two aggregates, but that will not change much. It doesn't matter that you want to fetch only 5 rows - You will need to calculate the "popularity" for every post int the DB every time.

So if the performance is an issue, you can create three new "cache" columns in your posts table:

  • likes_count
  • comments_count
  • popularity

with an index for the popularity column.

Every time you insert or delete a comment or a like, you will also need to update the related post. I would probably do that with triggers.

Having that, you can just use orderBy('popularity', 'DESC').

You can also create a new table (post_popularity) with the columns post_id, comments_count, likes_count, popularity in order to phisicaly separate the cached data. But then you would need a join:

$Posts->join('post_popularity as pp', 'pp.post.id', '=', 'posts.id')
      ->orderBy(pp.popularity, 'DESC');

Upvotes: 3

Alexey Mezenin
Alexey Mezenin

Reputation: 163788

You should sort data before getting it:

$Posts->orderBy('popularity_count')->paginate(5);

When you use sortBy() after paginate(), you're only sorting collection of 5 items.

Upvotes: 0

Related Questions