Rails Union Hack, How to Pull Two Different Queries Together

rails union hack, how to pull two different queries together

Doing an UNION query is not natively possible with ActiveRecord. So there are two solutions :

• Using find_by_sql to build your query as you want it. I wouldn't advise for it.
• Using a plugin like union to do a UNION sql query.

ActiveRecord Query Union

Here's a quick little module I wrote that allows you to UNION multiple scopes. It also returns the results as an instance of ActiveRecord::Relation.

module ActiveRecord::UnionScope
  def self.included(base)
    base.send :extend, ClassMethods
  end

  module ClassMethods
    def union_scope(*scopes)
      id_column = "#{table_name}.id"
      sub_query = scopes.map { |s| s.select(id_column).to_sql }.join(" UNION ")
      where "#{id_column} IN (#{sub_query})"
    end
  end
end

Here's the gist: https://gist.github.com/tlowrimore/5162327

Edit:

As requested, here's an example of how UnionScope works:

class Property < ActiveRecord::Base
  include ActiveRecord::UnionScope

  # some silly, contrived scopes
  scope :active_nearby,     -> { where(active: true).where('distance <= 25') }
  scope :inactive_distant,  -> { where(active: false).where('distance >= 200') }

  # A union of the aforementioned scopes
  scope :active_near_and_inactive_distant, -> { union_scope(active_nearby, inactive_distant) }
end

Union of two active record relations should be another active record in ruby on rails

Update for Rails 5

ActiveRecord now brings built-in support for UNION/OR queries! Now you can (the following examples are taken, as-is, from this nice post. Make sure you read the full post for more tricks and limitations):

Post.where(id: 1).or(Post.where(title: 'Learn Rails'))

or combine with having:

posts.having('id > 3').or(posts.having('title like "Hi%"'))

or even mix with scopes:

Post.contains_blog_keyword.or(Post.where('id > 3'))

Original answer follows

I do not think that AR provides a union method. You can either execute raw SQL and use SQL's UNION or perform the 2 different queries and union the results in Rails.

Alternatively you could take a look in these custom "hacks":
ActiveRecord Query Union or
https://coderwall.com/p/9hohaa

Alternative to a union with ActiveRecord

Your models would start off looking something like this:

class Price < ActiveRecord::Base
  belongs_to :item
  belongs_to :category
  belongs_to :discount

  scope :category, where("prices.category_id IS NOT NULL")
  scope :discount, where("prices.discount_id IS NOT NULL")
end

class Item < ActiveRecord::Base
  has_many :prices
end

class Category < ActiveRecord::Base
  has_many :prices
end

class Discount < ActiveRecord::Base
  has_many :prices
end

One way of doing this is to add a class method to Price that encapsulates this logic:

class Price < ActiveRecord::Base
  def self.used
    discount_items_sql = self.discount.select("prices.item_id").to_sql
    where("prices.discount_id IS NOT NULL OR prices.item_id NOT IN (#{discount_items_sql})")
  end
end

This is effectively the same as this query:

SELECT * FROM prices
WHERE prices.discount_id IS NOT NULL -- the discount_id is present on this record,
  OR prices.item_id NOT IN (         -- or no discount_id is present for this item
      SELECT item_id FROM prices WHERE discount_id IS NOT NULL)

You can add these helper methods on your Item model for simplicity:

class Item < ActiveRecord::Base
  def category_price
    prices.category.first
  end

  def discount_price
    prices.discount.first
  end

  def used_price
    prices.used.first
  end
end

Now you can easily get each individual 'type' of price for a single item (will be nil for prices that aren't available):

item.category_price
item.discount_price
item.used_price

Rails multiple associations in one result set

You can make a method in your model that concatenates the two associations and then sorts them. For example:

def feedback
  results = (comments + reviews).sort_by{ |e| e.created_at }
end

If you have a lot of records and want to speed up the process a little bit, you can easily limit the number of results that are returned. Note that you need to order each association independently first, then merge, then sort again.

def feedback max
  # We need to get `max` entries from each to ensure that we will have
  # enough entries in the final result.
  results = comments.order(:created_at).limit(max) + reviews.order(:created_at).limit(max)

  # Then, we only return the first `max` entries of the result, since there
  # will be `2*max` entries in `results`.
  results.sort_by{ |e| e.created_at }.first(max)
end

This leads into the second part of your question.

---

If you just want the most recent feedback available in the view, then you don't actually need to change your controller action. Instead, you can just access them in the view directly.

Assuming you keep the list of videos in the variable @videos, this could look like (omitting any ERB/Haml you may be using):

@videos.each do |video|
  video.feedback(3).each do |fb|
    # do whatever with each feedback item here
  end
end

If you need to differentiate between the types of feedback, you can use a case...when block:

case fb
when Comment
  # The feedback is a comment
when Review
  # The feedback is a review
end

Combining two or more different SELECT queries to same table with different conditions in PostgreSQL

Just write this as one query:

select sum(price) as lucro_esperado, count(*) as tarefas_abertas
from tasks
where extract(month from enddate) = 12 and
      extract(year from enddate) = 2019

I would advise you to change the where clause to:

where enddate >= '2019-12-01' and
      enddate < '2020-01-01'

This allows the database to use an index on enddate (if available). Also, removing the function calls on the column helps the optimizer.

EDIT:

I see, the two date parameters are different. Just use conditional aggregation:

select sum(case when enddate >= '2019-12-01' and enddate < '2020-01-01' then price end) as lucro_esperado,
       sum(case when date_added >= '2019-12-01' and date_added < '2020-01-01' then 1 else 0 end) as tarefas_abertas
from tasks;

Can Rails' Active Record handle SQL aggregate queries?

As Pallan pointed out, the :select option cannot be used with the :include option. However the :joins option can. And this is what you want here. In fact, it can take the same arguments as :include or use your own SQL. Here's some rough, untested code, may need some minor fiddling.

Event.all(:select => "events.id, patients.lname, events.patient_id, events.event_type, max(events.event_date) as max_date", :joins => :patient, :group => "patients.lname, events.patient_id, events.event_type")

Note I modified things slightly. I renamed the event_date alias to max_date so there's no confusion over which attribute you are referring to. The attributes used in your :select query are available in the models returned. For example, in this you can call event.max_date. I also added the event id column because you can sometimes get some nasty errors without an id attribute (depending on how you use the returned models).

The primary difference between :include and :joins is that the former performs eager loading of the associated models. In other words, it will automatically fetch the associated patient object for each event. This requires control of the select statement because it needs to select the patient attributes at the same time. With :joins the patient objects are not instantiated.

Combining 2 scopes to get single scope that has A,B,A,B,A,B order from the original scopes (Like zip.flatten would to an Array)

Not sure it's the best solution, but I found a solution using row_number in postgres and union.

select *, 'items1' as table_name, row_number() over () as rownum
  from "items" where key = 'some_key'
union
select *, 'items2' as table_name, row_number() over () as rownum
  from "items" where key <> 'some_key'
order by rownum, table_name

select twice from the same table using your 2 scopes, add row numbers, union them together and order the result according to row number. The constant table_name is there to ensure that one of the queries always comes first on the same row number so they indeed alternate.

Converting this to Arel left as an exercise to the reader.

Sorting model instances with Globalize3

Country.with_translations(I18n.locale).order('name') for current locale.

Edit:

You can also use fallbacks:

Country.with_translations(I18n.fallbacks[I18n.locale]).order('name')

---

Related Topics