Querying multiple related records in Rails

Question

I have an Account, User and Group model. All User and Group records belong to an Account, so it is possible for both users and groups to be related to various accounts.

Basically, I have some really hacky, pieced together SQL queries that lookup all Account records related to a given User or Group record through various associations.

Even though it's fast and only requires a single query for everything to work, I really hate the way it's written.

I was wondering if there is any way I could do this more programatically with something like arel, or if there was a better approach. I also have some security concerns about the code.

class Account < ActiveRecord::Base
 belongs_to :accountable, touch: true, polymorphic: true
 CLAUSE_FOR_RELATED = '"accounts"."accountable_type" = \'%s\' AND "accounts"."accountable_id" IN (%s)'.freeze
 def self.related_to_user(user)
 groups = user.groups.select('"groups".id')
 friends = user.following_friendships.select('"friendships".following_id')
 queries = []
 queries.push ['Group', groups.to_sql]
 queries.push ['User', friends.to_sql]
 queries.push ['User', user.id]
 self.related_to(queries)
 end
 def self.related_to_group(group)
 queries = [
 ['User', group.members.select('"users".id').to_sql]
 ]
 self.related_to(queries)
 end
 def self.related_to(queries)
 clauses = queries.map do |clause|
 sprintf(CLAUSE_FOR_RELATED, clause[0], clause[1])
 end
 self.where(clauses.join(" OR "))
 end
end

• Phrancis: Is the SQL tag properly used here? Was looking for SQL to review in your code but found only tiny snippets. No bad karma just seems a bit misleading.

Answer 1

I would try to use as much as possible what is provided by ActiveRecord. I was thinking to something like:

class Account < ActiveRecord::Base
 belongs_to :accountable, touch: true, polymorphic: true
 CLAUSE_FOR_RELATED = '"accounts"."accountable_type" = \'%s\' AND "accounts"."accountable_id" IN (%s)'.freeze
 class << self
 def related_to_user(user)
 group_ids = user.groups.pluck('groups.id')
 friend_ids = user.following_friendships.pluck('friendships.following_id') 
 query = [
 compose_clause('Group', group_ids.join(', ')),
 compose_clause('User', [user.id, *friend_ids].join(', '))
 ].join(' or ')
 where(query)
 end
 def related_to_group(group)
 member_ids = group.members.pluck('users.id')
 where(compose_clause('User', member_ids.join(', ')))
 end
 private
 def compose_clause(accountable_type, ids)
 sprintf(CLAUSE_FOR_RELATED, accountable_type, ids)
 end
 end
end

Now in Rails 5 you could even write it like:

class Account < ActiveRecord::Base
 belongs_to :accountable, touch: true, polymorphic: true
 class << self
 def related_to_user(user)
 group_ids = user.groups.pluck('groups.id')
 friend_ids = user.following_friendships.pluck('friendships.following_id') 
 where(accountable_type: 'Group', accountable_id: group_ids).or(
 Account.where(accountable_type: 'User', accountable_id: [user.id, *friend_ids])
 )
 end
 def related_to_group(group)
 member_ids = group.members.pluck('users.id')
 where(accountable_type: 'User', accountable_id: member_ids)
 end
 end
end