How to combine ActiveRecord objects?

Question

I'm wondering if there's an efficient way to combine the results of multiple ActiveRecord objects in Rails. For example, I might make three individual calls to three individual tables, and I want the results combined, and sorted by a common column.

Here's a super basic code example that will hopefully make my question easier to understand:

@results1 = Table1.find(:all)  
@results2 = Table2.find(:all)  
@results3 = Table3.find(:all)  

@combined_results_sorted_by_date_column = (how?)

As suggested by others, here's one solution to the problem.

@combined_results = @result1 + @result2 + @result3
@combined_results.sort! {|x,y| x.date <=> y.date}

What if I want to sort by date, but Table3 refers to the "created_on" column as date?

Answer 1

You don't work with "Tables" but rather objects.

If you think about it this way, it would make no sense to have:

@results1 = Users.find(:all)
@results2 = Posts.find(:all)
@results3 = Comments.find(:all)

What would the "combined" form of it means?

What you probably want is to combine results from the same kind using different "queries". Is that it?

Answer 2

@combined_results = @result1 + @result2 + @result3
@combined_results.sort! {|x,y| x.date <=> y.date}

While this surely is not be the most efficient code in the world, it might just be what you need.

If some models don't have a date method I suggest you create one. It is as easy as.

def date
  created_on
end

Answer 3

@results1 = Table1.find(:all)
@results2 = Table2.find(:all)
@results3 = Table3.find(:all) 

combined = (@results1 + @results2 + @results3).sort { |x, y| x.date <=> y.date }

Answer 4

@results1 = Table1.find(:all)
@results2 = Table2.find(:all)
@results3 = Table3.find(:all)

@combined_results_sorted_by_date_column =
   (@results1 + @results2 + @results3).sort_by(&:date)

What if I want to sort by date, but Table3 refers to the "created_on" column as date?

class Table3
  alias_method :date, :created_on
end

or simply

class Table3
  alias date created_on
end

Answer 5

I'm not sure what you're data volumes are like, but when it comes to sorting, you're database will do a better job of it than you ever will using "application layer" code.

Do you need the data returned as an array of model objects seeing as the three tables will probably generate a mixed up array of three distinct model classes ?

Why not use direct-SQL returning rows and columns and have the DB do all the hard work sorting the data ?

Answer 6

You're probably not going to like this answer, but I would say you might want to revise your database schema. I was in a similar situation, and sorting the results after concatenating them is definitely not the way you want to go.

Answer 7

I'm assuming you want a mixed array of three different types of ActiveRecord objects, sorted by a date of some kind.

@array = (Bucket.all + Mop.all + Detergent.all).sort{|x,y| x.sort_by <==> y.sort_by}

Since your sort_by field is different for each object type, you need to define it.

class Bucket < ActiverRecord::Base
  def sort_by
    cleaned_on
  end
end

class Detergent < ActiverRecord::Base
  def sort_by
    purchased_on
  end
end

You could pull in all the data sorted in a single query using UNION, but you wouldn't get AR objects out of that.