Laravel using where clause on a withCount method

Question

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 where upvotes.upvoteable_id = posts.id and upvotes.upvoteable_type = App\Post) as upvotes_count from posts where upvotes_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 where upvotes.upvoteable_id = posts.id and upvotes.upvoteable_type = App\Post and upvotes_count > 5) as upvotes_count from posts where id = 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 where id = 1 and upvotes_count > 5)

I just want to use where clause on a count() method which is in a relationship with a parent model.

Medin Piranej · Accepted Answer · 2017-10-13 14:31:09Z

48

You can achieve requested result by using:

$posts = Post::withCount('upvotes') ->having('upvotes_count', '>', 5) ->get();

answered Oct 13, 2017 at 14:31

Medin Piranej

8391 gold badge9 silver badges10 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Stetzon Over a year ago

I am still getting the error: column upvotes_count does not exist. I am not sure how this works since from my research it looks like the having clauses are executed before the select statements, therefore the alias upvotes_count is not yet defined. I am using Postgres. Does this only work for MySQL? Is there a way around this other than having to do a havingRaw to manually get the count again?

Medin Piranej Over a year ago

@Stetzon withCount('upvotes') should add upvotes_count, so you should add this, or if you still have this problem, send me the query and i will check it.

slapbot Over a year ago

@Stetzon Remember that you cannot do where clauses on aggregated columns, You can however do having clauses, so make sure that you are not doing where there.

Nikolay Traykov Over a year ago

I also have an error that the column doesn't exist with MySQL.

Miguelp25 Over a year ago

Same here, "Undefined column: 7 ERROR: column "users_count" does not exist". The thing is, "having()" is producing the error, "orderBy" works.

Yousef Altaf · Accepted Answer · 2019-12-14 09:43:12Z

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

Isn't 'upvotes as upvotes_count' enough, do we really need to have 'upvotes', in array passed to withCount(...)method?

Salman Zafar · Accepted Answer · 2019-11-08 09:57:56Z

10

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();

edited Nov 8, 2019 at 9:57

Salman Zafar

4,0655 gold badges23 silver badges44 bronze badges

answered Nov 6, 2019 at 19:09

Sean Glendinning

1011 silver badge3 bronze badges

Comments

Keith Turkowski · Accepted Answer · 2020-01-25 18:26:47Z

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.

Felipe Loge · Accepted Answer · 2021-07-28 21:30:29Z

If you need to select only rows that the counter is greater than or equal to 1 you can try the following code:

$posts = Post::withCount('upvotes') ->havingRaw('upvotes_count') ->get();

I don't know if this is the best solution but it's an alternative. Another alternative would be get the posts and use array filter as mentioned in a comment above.

BigMitch · Accepted Answer · 2016-12-06 06:35:10Z

I ran into the same problem, and tried the same things you did. I'm guessing there is a way to replicate the SQL generated by the withCounts call but add a way to make xxx_counts available to a where clause, but I just filtered the resulting collection instead.

$allTags = Tag::withCount('articles')->get(); $usedTags = $allTags->filter(function ($value, $key) { return $value->articles_count > 0; });

It's been quite a time, and as I learnt more about sql, you can't do where clauses on the aggregate functions, hence upvotes_count column isn't found since it's aggregated and not selected. but the above problem can be solved using some advanced queries and grouping them together.

Collectives™ on Stack Overflow

Laravel using where clause on a withCount method

6 Answers 6

5 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

5 Comments

1 Comment

Comments

Comments

Comments

1 Comment

Linked

Related