Complex SQL Query in Rails 4

Question

I have a complicated query I need for a scope in my Rails app and have tried a lot of things with no luck. I've resorted to raw SQL via find_by_sql but wondering if any gurus wanted to take a shot. I will simplify the verbiage a bit for clarity, but the problem should be stated accurately.

I have Users. Users own many Records. One of them is marked current (#is_current = true) and the rest are not. Each CoiRecord has many Relationships. Relationships have a value for when they were active (active_when) which takes four values, [1..4].

Values 1 and 2 are considered recent. Values 3 and 4 are not.

The problem was ultimately to have a scopes (has_recent_relationships and has_no_recent_relationships) on User that filters on whether or not they have recent Relationships on current Record. (Old Records are irrelevant for this.) I tried create a recent and not_recent scope on Relationship, and then building the scopes on Record, combining with checking for is_current == 1. Here is where I failed. I have to move on with the app but have no choice but to use raw SQL and continue the app, hoping to revisit this later. I put that on User, the only context I really need it, and set aside the code for the scopes on the other objects.

The SQL that works, that correctly finds the Users who have recent relationships is below. The other just uses "= 0" instead "> 0" in the HAVING clause.

SELECT * FROM users WHERE `users`.`id` IN (
  SELECT 
    records.owner_id
  FROM `coi_records`
  LEFT OUTER JOIN `relationships` ON `relationships`.`record_id` = `records`.`id`
  WHERE `records`.`is_current` = 1
  HAVING (
    SELECT count(*)
    FROM relationships
    WHERE ((record_id = records.id) AND ((active_when = 1) OR (active_when = 2)))
  ) > 0
)

My instincts tell me this is complicated enough that my modeling probably could be redesigned and simplified, but the individual objects are pretty simple, just getting at this specific data from two objects away has become complicated.

Anyway, I'd appreciate any thoughts. I'm not expecting a full solution because, ick. Just thought the masochists among you might find this amusing.

marzapower · Accepted Answer · 2014-09-17 14:03:11Z

1

Have you tried using Arel directly and this website?

Just copy-and-pasting your query you get this:

User.select(Arel.star).where(
  User.arel_table[:id].in(
    Relationship.select(Arel.star.count).where(
      Arel::Nodes::Group.new(
        Relationship.arel_table[:record_id].eq(Record.arel_table[:id]).and(
          Relationship.arel_table[:active_when].eq(1).or(Relationship.arel_table[:active_when].eq(2))
        )
      )
    ).joins(
      CoiRecord.arel_table.join(Relationship.arel_table, Arel::Nodes::OuterJoin).on(
        Relationship.arel_table[:record_id].eq(Record.arel_table[:id])
      ).join_sources
    ).ast
  )
)

answered Sep 17, 2014 at 14:03

marzapower

5,6217 gold badges41 silver badges77 bronze badges

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

1 Comment

Dan Barron Over a year ago

Thanks for the response, marzapower. I did try Arel briefly but just didn't have time to dig into at the time. I will make a bookmark for that site, though! Looks very useful! See the answer I posted for how I got it to work, for now at least.

Dan Barron · Accepted Answer · 2014-09-30 14:49:33Z

I managed to find a way to create what I needed which returns ActiveRelationship objects, which simplifies a lot of other code. Here's what I came up with. This might not scale well, but this app will probably not end up with so much data that it will be a problem.

I created two scope methods. The second depends on the first to simplify things:

def self.has_recent_relationships
  joins(records_owned: :relationships)
  .merge(Record.current)
  .where("(active_when = 1) OR (active_when = 2)")
  .distinct
end

def self.has_no_recent_relationships
  users_with_recent_relationships = User.has_recent_relationships.pluck(:id)
  if users_with_recent_relationships.length == 0
    User.all
  else
    User.where("id not in (?)", users_with_recent_relationships.to_a)
  end
end

The first finds Users with recent relationships by just joining Record, merging with a scope that selects current records (should be only one), and looks for the correct active_when values. Easy enough.

The second method finds Users who DO have recent relationships (using the first method.) If there are none, then all Users are in the set of those with no recent relationships, and I return User.all (this will really never happen in the wild, but in theory it could.) Otherwise I return the inverse of those who do have recent relationships, using the SQL keywords NOT IN and an array. It's this part that could be non-performant if the array gets large, but I'm going with it for the moment.

Collectives™ on Stack Overflow

Complex SQL Query in Rails 4

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related