How to implement complex SQL queries in Ruby on Rails?

Question

I have four tables: "users", "user_groups", "groups", "categories". "users" and "groups" are many_to_many relations through "user_groups". "groups" and "categories" are many_to_one relations.

I created the following SQL query, but I'm not sure how to implement it in Ruby on Rails:

SELECT u.*
FROM users u
WHERE EXISTS (SELECT 1
                FROM  user_groups ug,
                        groups g,
                        categories c
                WHERE u.id = ug.user_id
                AND   ug.group_id = g.id
                AND   g.category_id = c.id
                AND   c.id in ('1, 2, 3'))

What is the best way to implement it without using raw SQL in Ruby on Rails?

max · Accepted Answer · 2020-01-01 19:42:45Z

6

I am not a big fan of always translating complex queries into the ActiveRecord query language. Instead, I think it is perfectly fine to write complex queries in plain SQL because SQL is usually easier to write and to understand.

That said, I think this might work:

User.where(
  id: UserGroup.select('user_groups.user_id')
               .joins(groups: :categories)
               .where(categories: { id: [1, 2, 3] })
)

edited Jan 1, 2020 at 19:42

max

103k15 gold badges113 silver badges177 bronze badges

answered Dec 31, 2019 at 8:18

spickermann

108k9 gold badges115 silver badges147 bronze badges

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

6 Comments

Sebastián Palma Over a year ago

I think it is perfectly fine to write complex queries in plain SQL because SQL is usually easier to write and to understand +1

vlad Over a year ago

I think so.

@users = User.find_by_sql([           "SELECT u.* FROM users u WHERE EXISTS (SELECT 1 FROM user_groups ug, groups g, categories c where u.id = ug.user_id AND ug.group_id = g.id AND g.category_id = c.id AND c.id in ('1, 2, 3'))"])

Aleksei Matiushkin Over a year ago

“SQL is usually easier to write and to understand” — 💯

the Tin Man Over a year ago

An ORM helps transform from a DBM-specific language to a more generic one, which anyone who's had to deal with recoding big corporate mainframes with proprietary language knows can be a major PITA. In those cases the ORM can save years of work. If translating between two DBMs that are SQL compliant, it's easier to make use of raw SQL when switching than it used to be, but still, sticking to the ORM will make it even easier; Imagine developing in SQLite or PgSQL on your laptop on a plane then switching to full Oracle on a big server with only a tiny change in the connection string. ORM FTW.

max Over a year ago

Fair enough, but isn't the question just a job security way of doing a left join? Or am I missing something?

|

max · Accepted Answer · 2020-01-01 19:51:54Z

0

This is probably a lot less complicated than you think:

class User
  has_many :user_groups
  has_many :groups, through: :user_groups
end

class Group
  has_many :user_groups
  has_many :users, through: :user_groups
  belongs_to :category
end

User.joins(:groups)
    .where(groups: { category_id: [1,2,3] })

ActiveRecord will handle joining the intermediate table.

Since this is a left inner join any rows without a match in the join table are omitted. No need to do WHERE EXISTS .... This will give you users that belong to at least one group with the category 1, 2 or 3.

answered Jan 1, 2020 at 19:51

max

103k15 gold badges113 silver badges177 bronze badges

Collectives™ on Stack Overflow

How to implement complex SQL queries in Ruby on Rails?

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related