CODEWITHSUNDEEP
sqlruby-on-rails-4
Dan Barron
Dan Barron

Reputation: 1104

Complex SQL Query in Rails 4

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.

Upvotes: 0

Views: 921

Answers (2)

Dan Barron
Dan Barron

Reputation: 1104

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.

Upvotes: 0

marzapower
marzapower

Reputation: 5611

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
  )
)

Upvotes: 1

Related Questions