Search code examples
ruby-on-railsactive-relation

Standard sql subquery to ActiveRecord Relation, is it possible?

I've got a piece of code where I have to applies some subqueries. I'm trying to change this piece of pure sql to more rails way. Is it possible to implement ActiveRecord Relation here?

Customer.joins("RIGHT JOIN customers_users ON customers_users.customer_id = customers.id").
         where("customers_users.user_id IN (
                                    SELECT user_id FROM customers_users
                                    GROUP BY customers_users.user_id
                                    HAVING COUNT(customers_users.user_id) = 1)")
Solution

  • Yes you can convert it rails way like

    first create a relation to customer_users in customer model

    Customer.joins(:customers_users).where(customers_users: { user_id: CustomersUser.select(:user_id).group(:user_id).having('COUNT(customers_users.user_id) = 1') })