I want to get the rank of tag number by user.
example
code
// controller
public function test() {
$userItemTag = DB::table('user_item_tag')
->select('userid', DB::raw('COUNT(1) as count'))
->groupBy('userid')
->orderByDesc('count')
->limit(3);
$userItemTag2 = UserItemTag::select('userid', DB::raw('COUNT(1) as count'))
->groupBy('userid')
->orderByDesc('count')
->limit(3);
return [
'tag_sql' => $userItemTag->toSql(),
'tag2_sql' => $userItemTag2->toSql(),
'tag_result' => $userItemTag->get(),
'tag2_result' => $userItemTag2->get(),
];
}
result
{
"tag_sql": "select `userid`, COUNT(1) as count from `user_item_tag` group by `userid` order by `count` desc limit 3",
"tag2_sql": "select `userid`, COUNT(1) as count from `user_item_tag` group by `userid` order by `count` desc limit 3",
"tag_result": [
{
"userid": 1,
"count": 23347
},
{
"userid": 2,
"count": 18749
},
{
"userid": 3,
"count": 18692
}
],
"tag_result": [
{
"userid": 1,
"count": 0
},
{
"userid": 2,
"count": 0
},
{
"userid": 3,
"count": 0
}
]
}
description
"tag_result" is the correct data.
"tag2_result" has the same users as "tag_result".
so I think the groupBy and COUNT is working.
but the property count in "tag2_result" is always return 0.
the only different is using DB::table or UserItemTag(model).
I run the query in database and get the same result as "tag_result".
is there anything need to setup?
environment
{
"php": "^8.0.2",
"laravel/framework": "^9.19",
}
Answer
Here is the issue ...
If your UserItemTag model is not explicitly set to reference the user_item_tag table, Laravel will use the default table name convention, which is usually the plural form of the model name (user_item_tags). This might cause the issue you are facing
So check and fix it by by explicitly define the the table name in the respective model.
`class userItemTags extends model{
protected $table = 'user_item_tag';
}`