Get position in rating by scope

This would be a lot easier to do after you grab the results from the database, say you were looping through the results you could just do $loop->iteration in your loop and that would be equal to the position of the item.

If you wanna keep it at the DB level, I had to get hacky - not sure there's a better way to do it:

public function scopeByRating($query)
{
    $query->selectRaw("@row_number:=@row_number+1 AS position, name_of_item_db_table_here.*")
        ->from(DB::raw("name_of_item_db_table_here, (SELECT @row_number:=0) AS t"))
        ->orderBy('points', 'desc');
}

$items = Item::byRating()->get();

In MySQL 8+ it would get a little cleaner using ROW_NUMBER()

@REALRANDYALLEN - But if points is zero, how do in second orderBy, order By created_at?

@DRONAX - Try

->orderByRaw('points desc, created_at desc')

@REALRANDYALLEN - Working :) Thanks!

@DRONAX - You're welcome!

@REALRANDYALLEN - I have a problem. I can't get position in show method of shop. How I can get position in show method? Can you please explain?

public function show(Shop $shop) {
        //
    }

@DRONAX - It depends, does the "Shop" have the points, or do the points live on something else that's related to a shop?

@REALRANDYALLEN - Shop have points, I need get position order by points :)

@DRONAX - This is one approach

public function getRankAttribute()
{
    return Shop::byRating()->get()->firstWhere('id', $this->id)->position;
}

// $shop->rank

I'm not a huge fan though because every Shop model you instantiate the DB will get queried for every single Shop in the DB and then the collection will be searched for the Shop you're editing. This could be ok if you don't expect the Shop table to be huge but it's definitely a design flaw.

You could consider having this as a field in the DB, then you'd have to worry about maintaining it. For example, every time a Shop is created or edited you'd have to run a method that updates the position field for each Shop in the db.

@REALRANDYALLEN - What I need do on create, or update shop, for update position ?

@REALRANDYALLEN - I think that need function in model?

public function updatePosition() {
    return $this->update(['position' => '@row_number:=@row_number+1 AS position, shops.*'])
}

And call this on store and update shops?

@DRONAX - Finally figured this out, the main issue is you can't just update the one Shop because if points change the position of all Shops could possibly change. So I've setup a model event that when a Shop is saved (aka created or edited) the ranks of all Shops are updated:

Shop model:

class Shop extends Model
{
    ...

    public static function boot() {
        parent::boot();

        static::saved(function() {
            static::updateRanks();
        });
    }

    public static function updateRanks()
    {
        DB::statement("UPDATE shops SET position = (SELECT FIND_IN_SET( points, (SELECT GROUP_CONCAT( points ORDER BY points DESC ) FROM (SELECT * FROM shops) as shop_rankings)))");

        return $this;
    }
    ...
}

The issue I was having was that update query, MySQL is kinda dumb wen you want to update a field on the same table in a subquery.

You should update scopeByRating now to just sort by position and you should delete getRankAttribute altogether

@REALRANDYALLEN - You do ORDER BY points, but if points is zero, can we ORDER BY created_at?

@DRONAX - Just add it in ORDER BY points DESC, created_by DESC

@REALRANDYALLEN - Will be: (SELECT GROUP_CONCAT( points ORDER BY points DESC, created_by DESC )

But I don't need add after GROUP_CONCAT( points , created_at?

@DRONAX - I believe this should work:

DB::statement("UPDATE shops SET position = (SELECT FIND_IN_SET( points, (SELECT GROUP_CONCAT( points ORDER BY points DESC, created_at DESC ) FROM (SELECT * FROM shops) as shop_rankings)))");

I just typo'd created_by instead of created_at

@REALRANDYALLEN - I get error: Using $this when not in object context and if I remove return $this then your method update all shops in table and position the same everywhere. How we can fix this?

@DRONAX - You can actually just delete return $this it's not needed.

The only reason you should get the same position for each Shop would be if they have the same number of points (or null points)

@REALRANDYALLEN - You're right, all shops have points: zero (0). How then get position with created_at?

@DRONAX - Try changing your updateRanks function to this:

    public static function updateRanks()
    {
        DB::statement("update shops set position = (SELECT FIND_IN_SET( CONCAT(points, '_', created_at), (SELECT GROUP_CONCAT( CONCAT(points, '_', created_at) ORDER BY points desc, created_at desc ) FROM (select * from shops) as shop_rankings)))");
    }

@REALRANDYALLEN - Thanks, it's working! But about performance, in future all will be good ? If shops will be 10000+ ?

@DRONAX - You're welcome :) I think you'll be ok, this is one query now - the old method it would have been a big issue

@REALRANDYALLEN - Sorry. I probably got you :) I have a problem. Every shop have a category. When I go to certain category, I display shops by this category. I have a question. How I can do rank, for certain category? Example: If I in category "games" display shops by points and created_at only where this shop equal this category. Every shop have a column: category_id.

When I on certain category page, shops show like:

#10 Shop ug #11 Shop tatata #15 Shop basca #22 Shop nobmer #30 Shop abomut

How #10, #11 e.t.c change on: 1,2,3,4,5 ?

@REALRANDYALLEN - No Idea?

@DRONAX - Your byRating scope can just be added on to to filter by category, which should then rank every shop within that category:

$shops = Shop::byRating()->where('category_id', 1)->get();

You could also make another scope byCategory that would make it reusable and clean

//controller
$shops = Shop::byRating()->inCategory(1)->get();


// model
public function scopeInCategory($query, $category_id)
{
    return $query->where('category_id', $category_id);
}

@REALRANDYALLEN - Yes. But rank starts not from 1. When I go to category page, I get ranks 4, 5, 6 and other.. not from 1

@DRONAX - You'll need to make a decision, do you want Shop's to be ranked overall (all Shops) or only by how they rank within their category, or do you want the system to do both?

1. All Shops: Keep your logic the way it is and when you group by Category don't use the position field from the DB, just use where you currently are in the loop as the Shop's rank.

2. By Category Only: Update the updateRanks method to filter by a Shop's category_id

3. Both: Consider adding another field to your Shop table, category_position, and add another method updateRanksByCategory when you create or edit a Shop that would update those values in the db

Or if you are only every going to use rank in your front end when you are displaying a list of shops then you could just retrieve your Shops ordered by Points and artificially display the rank based on where the item is in your collection

@foreach ($shops as $shop)
    <tr>
        <td>Rank</td>
        <td>{{ $loop->iteration }}</td>
        <td>Name</td>
        <td>{{ $shop->name }}</td>
    </tr>
@endforeach

etc

@REALRANDYALLEN - Thanks!

@REALRANDYALLEN - Sorry, I need help now :) Now I need put on order a reviews_count, I have relation in shop model:

 protected $withCount = ['reviews']

 public function reviews() {
        return $this->hasMany(Review::class);
 }

In your method I try put a reviews_count:

public static function updateRanks()
{
    DB::statement("update shops set position = (SELECT FIND_IN_SET( CONCAT(points, '_', reviews_count, '_', created_at), (SELECT GROUP_CONCAT( CONCAT(points, '_', reviews_count, '_', created_at) ORDER BY points desc, reviews_count desc, created_at desc ) FROM (select * from shops) as shop_rankings)))");
}

But me get error:

SQLSTATE[42000]: Syntax error or access violation: 1583 Incorrect parameters in the call to native function 
'concat...

Can you please help?

@DRONAX - reviews_count isn't in your database table, so MySQL doesn't know what it is - you'd need to create it as a field on that table and keep it updated with the correct numbers before you updateRanks