0

I'm having some issues with trying to order a model by association count. The method I'm using was described here and it works in console and whenever I pause it by using byebug. Other times it doesn't execute the select statement at all and just tries to do the group and order. This is my class:

class Course < ActiveRecord::Base

has_many :course_videos, foreign_key: :course_id
has_many :videos, through: :course_videos

scope :ordered_by_video_count, -> {
joins(:videos)
    .select("courses.*, count(videos.id) as video_count")
    .group("courses.id")
    .order("video_count desc")
}
end

And the exact error I'm getting is this:

SQLite3::SQLException: no such column: video_count: SELECT COUNT(*) AS count_all, courses.id AS courses_id FROM "courses" INNER JOIN "course_videos" ON "course_videos"."course_id" = "courses"."id" INNER JOIN "videos" ON "videos"."id" = "course_videos"."video_id" GROUP BY courses.id  ORDER BY video_count desc

Thanks!

Stefan

Community
  • 1
  • 1
StefanDorresteijn
  • 409
  • 1
  • 5
  • 10
  • For some reason it's not respecting the `as`, you can see that in the sql error `SELECT COUNT(*) AS count_all`. You could change the order to be `count_all` rather than `video_count`. If not you're going to need to work out why the as isn't being respected. Maybe try wrapping them in quotes? I don't use sqlite but a quick google shows tutorials having them, I'm not sure if sqlite needs them so might do nothing. `.select("courses.*, count(videos.id) as 'video_count'")` – j-dexx Oct 14 '15 at 13:33
  • @japed Neither the count_all ordering nor the quotes did anything. SQLite is only for the dev env but postgres seems to be skipping it as well. – StefanDorresteijn Oct 14 '15 at 13:36

1 Answers1

0

not sure if it makes a difference, but try

.order(video_count: :desc)

that's the syntax I alway use.

baron816
  • 701
  • 4
  • 13
  • actually, the problem is that you need to use a cache counter. Maybe check this out: http://yerb.net/blog/2014/03/13/three-easy-steps-to-using-counter-caches-in-rails/ – baron816 Oct 14 '15 at 13:29
  • While I could indeed use that, I'd love to know why this SQL isn't working in order to not make the same mistake next time. Thanks though! And the video_count: :desc didn't change it. The entire select statement seems to be overlooked by Rails sometimes. – StefanDorresteijn Oct 14 '15 at 13:33