How to convert SQL query to optimized Rails Active Record query?

Question

I have this SQL query that works properly when I run it but how do I represent it and get the same data via ActiveRecord?

select concat(o.code, ' - ', u.name) 
from users u join user_organizations uo on u.id = uo.user_id 
join organizations o on uo.organization_id = o.id 
where u.approved = true 
  and u.deleted_at is null 
  and o.deleted_at is null 
  and u.type = 'Banker' 
order by o.code asc;

I tried this

Banker.joins(:user_organizations => :organization)
  .where(:approved => true)
  .select("concat(organizations.code, users.name)")
  .order("organizations.code asc")

but it didn't work and I had expected it to work.

Well what are you expecting to be the result? Your model Banker probably will not have a attribute concat(organizations.code, users.name) — Fallenhero
– Fallenhero, Commented Feb 6, 2017 at 8:16

basiszwo · Accepted Answer · 2017-02-06 09:40:06Z

There a couple of things to address and open questions which make it difficult to answer properly

How are your relations set up between Banker and Organization?
as asked before: what are you expecting?
do you use some gem for this soft-delete feature (deleted_at is NULL)? (e.g. paranoia)
can you provide the generated SQL query from your version?

I assume that your setup looks sth like this

class User < ApplicationRecord
  has_and_belongs_to_many :organizations
end

class Organization < ApplicationRecord
  has_and_belongs_to_many :users
end

If so you should be able to do the following

User.select("concat(organizations.code, ' - ', users.name)")
    .includes(:organizations)
    .where('users.deleted_at is not null')
    .where('organizations.deleted_at is not null')
    .where('users.type = ?', 'Banker')
    .order('organizations.code ASC')

In case you are using the paranoia gem mentioned above for users and organizations the *.deleted_at is not null query parts will be added automatically which reduces the ruby query to sth like this.

User.select("concat(organizations.code, ' - ', users.name)")
    .includes(:organizations)
    .where('users.type = ?', 'Banker')
    .order('organizations.code ASC')

For the rare case you don't know about the rails guides. Here's the link to the article about associations.

Collectives™ on Stack Overflow

How to convert SQL query to optimized Rails Active Record query?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related