回答編集履歴
4
要件変更のため回答修正
test
CHANGED
|
@@ -6,22 +6,22 @@
|
|
|
6
6
|
|
|
7
7
|
```sql
|
|
8
8
|
select
|
|
9
|
-
|
|
9
|
+
fighters.id,
|
|
10
10
|
count(likes.user_id),
|
|
11
11
|
rank() over (order by count(likes.user_id) desc)
|
|
12
|
-
from
|
|
12
|
+
from fighters
|
|
13
|
-
left join likes on
|
|
13
|
+
left join likes on fighters.id = likes.fighter_id
|
|
14
|
-
group by
|
|
14
|
+
group by fighters.id
|
|
15
15
|
order by count(likes.user_id) desc
|
|
16
16
|
```
|
|
17
17
|
|
|
18
18
|
```php
|
|
19
|
-
$
|
|
19
|
+
$fighters = Fighter::query()
|
|
20
|
-
->selectRaw('
|
|
20
|
+
->selectRaw('fighters.*')
|
|
21
21
|
->addSelect(DB::raw('count(likes.user_id) as likes_count'))
|
|
22
22
|
->addSelect(DB::raw('rank() over (order by count(likes.user_id) desc) as rank'))
|
|
23
|
-
->leftJoin('likes', '
|
|
23
|
+
->leftJoin('likes', 'fighters.id', '=', 'likes.fighter_id')
|
|
24
|
-
->groupBy('
|
|
24
|
+
->groupBy('fighters.id')
|
|
25
25
|
->orderByRaw('count(likes.user_id) desc')
|
|
26
26
|
->get();
|
|
27
27
|
```
|
3
追記
test
CHANGED
|
@@ -14,3 +14,14 @@
|
|
|
14
14
|
group by products.id
|
|
15
15
|
order by count(likes.user_id) desc
|
|
16
16
|
```
|
|
17
|
+
|
|
18
|
+
```php
|
|
19
|
+
$products = Product::query()
|
|
20
|
+
->selectRaw('products.*')
|
|
21
|
+
->addSelect(DB::raw('count(likes.user_id) as likes_count'))
|
|
22
|
+
->addSelect(DB::raw('rank() over (order by count(likes.user_id) desc) as rank'))
|
|
23
|
+
->leftJoin('likes', 'users.id', '=', 'likes.user_id')
|
|
24
|
+
->groupBy('products.id')
|
|
25
|
+
->orderByRaw('count(likes.user_id) desc')
|
|
26
|
+
->get();
|
|
27
|
+
```
|
2
修正
test
CHANGED
|
@@ -1,3 +1,16 @@
|
|
|
1
1
|
[MySQLでランキングに順位番号を取得する簡単な方法](https://pisuke-code.com/mysql-way-to-get-rank-number/)
|
|
2
2
|
[MySQL 5.7 でクエリだけでランキングを実現する方法](https://tech.mobilefactory.jp/entry/2021/12/11/000000)
|
|
3
3
|
このあたりが参考になると思います。
|
|
4
|
+
|
|
5
|
+
MySQL 8以上であれば、Window関数使えるので、以下のSQLでも取得できる
|
|
6
|
+
|
|
7
|
+
```sql
|
|
8
|
+
select
|
|
9
|
+
products.id,
|
|
10
|
+
count(likes.user_id),
|
|
11
|
+
rank() over (order by count(likes.user_id) desc)
|
|
12
|
+
from products
|
|
13
|
+
left join likes on products.id = likes.product_id
|
|
14
|
+
group by products.id
|
|
15
|
+
order by count(likes.user_id) desc
|
|
16
|
+
```
|
1
追記
test
CHANGED
|
@@ -1,2 +1,3 @@
|
|
|
1
1
|
[MySQLでランキングに順位番号を取得する簡単な方法](https://pisuke-code.com/mysql-way-to-get-rank-number/)
|
|
2
|
+
[MySQL 5.7 でクエリだけでランキングを実現する方法](https://tech.mobilefactory.jp/entry/2021/12/11/000000)
|
|
2
3
|
このあたりが参考になると思います。
|