I am trying to do a where clause on withCount method of laravel's eloquent query builder using this piece of code.
$posts = Post::withCount('upvotes')->where('upvotes_count', '>', 5)->get();
and this code is giving me this error.
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'upvotes_count' in 'where clause' (SQL: select , (select count() from
upvotes
whereupvotes
.upvoteable_id
=posts
.id
andupvotes
.upvoteable_type
= App\Post) asupvotes_count
fromposts
whereupvotes_count
> 5)
So from what I can guess is that upvotes_count isn't selected and hence the column is not being found, BUT if I execute this piece of code.
$posts = Post::withCount('upvotes')->get();
Then I am getting this output.
{
"id": 1,
"user_id": 15,
"title": "Voluptatum voluptas sint delectus unde amet quis.",
"created_at": "2016-10-07 13:47:48",
"updated_at": "2016-10-07 13:47:48",
"upvotes_count": 7
},
{
"id": 2,
"user_id": 2,
"title": "Molestiae in labore qui atque.",
"created_at": "2016-10-07 13:47:48",
"updated_at": "2016-10-07 13:47:48",
"upvotes_count": 2
},
Which basically means that upvotes_count is being selected, hence i am really confused about how to solve this problem.
(More options that I tried so far are given below with the respective error associated to it.)
$posts = Post::where('id', $id)->withCount(['upvotes' => function($query) {
$query->where('upvotes_count', '>', 5);
}])->get();
error.
SQLSTATE[42S22]: Column not found: 1247 Reference 'upvotes_count' not supported (forward reference in item list) (SQL: select , (select count() from
upvotes
whereupvotes
.upvoteable_id
=posts
.id
andupvotes
.upvoteable_type
= App\Post andupvotes_count
> 5) asupvotes_count
fromposts
whereid
= 1)
code.
$posts = Post::where('id', $id)->with(['upvotes' => function($query) {
$query->select('upvoteable_id AS upvotes_count');
}])->where('upvotes_count', '>', 5)->get();
AND
$posts = \App\Post::where('id', $id)->with(['upvotes' => function($query) {
$query->selectRaw('upvoteable_id AS upvotes_count');
}])->where('upvotes_count', '>', 5)->get();
error.
SQLSTATE[42S22]: Column not found: 1054 Unknown column 'upvotes_count' in 'where clause' (SQL: select * from
posts
whereid
= 1 andupvotes_count
> 5)
I just want to use where clause on a count() method which is in a relationship with a parent model.
You can achieve requested result by using:
$posts = Post::withCount('upvotes')
->having('upvotes_count', '>', 5)
->get();
another good way to do this we can filter that separately and even assign an alias to that column name
$posts = Post::withCount([
'upvotes',
'upvotes as upvotes_count' => function ($query) {
$query->where('upvotes_count', '>', 5);
}])
->get();
Now in blade you can do
$posts->upvotes_count
I'm not sure if this was implemented after your question, but you can now do it like this
$posts = Post::has('upvotes','>',5)->get();
I think using has() is the best solution:
Post::has('upvotes','>',5)->withCount('upvotes')->get()
You could also use a filter:
Post::withCount('upvotes')->get()->filter(function($post) { return $post->upvotes_count > 5; })
You could also disable strict mode in config/database.php (probably not a good idea)
'strict' => false,
Post::withCount('upvotes')->having('upvotes_count','>',5)->get()
You could also try to add a groupBy clause (using having in strict mode), but this will likely require you to include every column in your table (due to 'ONLY_FULL_GROUP_BY'), which could break things if you ever add another column to your table, and probably won't work anyway because I think you need to include 'upvotes_count' in the groupBy and it seems to be a non grouping field.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With