Which model should I use with find_by_sql?

I have three tables that are linked together and I’m trying to figure out which model I should use at the beginning of my find_by_sql call.

There are Users, Schedules with a has_and_belongs_to_many association between them, so a schedules_users join table in between them.

My SQL will want to look something like “select user_id from schedules_users where schedule_id = 123” – but of course I don’t have a SchedulesUsers model to return.

There are several things to consider here.

Firstly, do you actually want a has_many :through association? If so, then you will end up with a SchedulesUsers model (although I would call it a UserSchedule because that just makes more sense), and you can use that for your find_by_sql call.

But, secondly, why are you using find_by_sql? Find by sql uses raw SQL, so it’s not database-independent, whereas ActiveRecord queries are. It leaves you open to SQL injection attacks as soon as you start substituting parameters, unless you are very careful. And in this particular case, what you are really after is easily done with ActiveRecord: User.joins(:schedules).where(schedules: { id: schedule.id }). A simple query, returning a collection of Users, with all database access safely escaped to prevent a very common hack attack.

Do you know what to do but not how it works?

Ever wanted to understand why Rails views work the way that they do? Why variables from your controllers are visible inside your views?

Sign up below to get a free 5 part email course on how Ruby on Rails view rendering works and gain a deep understanding of the Rails magic.

We will send you the course, plus the occasional update from this web-site. But no spam, we promise, and it's easy to unsubscribe