Compare 2 large ActiveRecord relations for sorting

user_216363 0
Viewed 244

I have 2 large ActiveRecord::Relations with 10s or 100s of thousands of User records each, let's call them ar1 and ar2. I want to sort ar1 so that all the records that also appear in ar2 are placed at the front of ar1.

This is easy enough to to with sort_by, but because the number of records is so large, the sort_by takes over a minute in some cases to execute.

I figured my best bet was to try and sort the records this way through ActiveRecord, but I can't find any way to do this. Is there a way to accomplish this result quickly, using ActiveRecord or some other approach?

Thank you!

Edit:

Here's the sort code I was using: ar1.sort_by { |e| ar2.index(e) || Float::INFINITY }

ruby-on-rails-6 activerecord ruby-on-rails ruby
4 Answers

I will start by saying that you don't really need sort_by!

You can achieve the same thing with:

ar1.sort_by { |e| ar2.index(e) || Float::INFINITY }

with an approach similar to:

result = ((ar2 & ar1) + ar1).uniq

Which is much faster.

Here is a simple benchmark to back this suggestion.

require 'benchmark'
ar1 = 100_000.times.map {|x|( 100_000_000 * rand).to_i }.uniq
ar2 = 100_000.times.map {|x|( 100_000_000 * rand).to_i }.uniq

puts ar1.size
puts ar2.size

Benchmark.bm do |x|
  result_2 = []
  result_1 = []
  x.report('sort') do
    result_2 = ar1.sort_by { |e| ar2.index(e) || Float::INFINITY }
    result_2.size
  end

  x.report('array') do
    result_1 = ((ar2 & ar1) + ar1).uniq
    result_1.size
  end

  puts result_1.size
  puts result_2.size
  puts result_1 == result_2
end

Gives some nice results

       user     system      total        real
sort 45.287331   0.012233  45.299564 ( 45.539641)
array  0.010782   0.004000   0.014782 (  0.014792)

I omitted some validation output.

Now for the ActiveRecord part. Depending on the ar2 collection size you can retrieve the ids and sort the second query by that. Given that ar1 = first_query.order(first_order)

ar2_ids = second_query.pluck(:id)

ar1 = first_query.order("FIELD(id, #{ar2_ids.join(',')}), first_order")

This will keep the ar2 first and the others later. You will need to investigate the size of ar2 because depending on your database it may be suboptimal to provide an order query with some thousands of ids.

After some thought on the problem, I would choose the array manipulation approach to keep things simple. I wouldn't go for the database solutions because it would most probably mess up the readability of the queries, way too much.

It all depends on the details of your problem! I hope this helps.

user_6871420

For the information you gave, you could try this:

ids = ar2.ids
ar1.where(id: ids) + ar1.where.not(id: ids)

If you could tell us more: (e.g. what's the difference ar1 and ar2), there might be a better solution.

user_150970280

If it possible we should use sql queries instead of ruby

active_users = User.where(sign_in_count: 1..) # ar1 query
inactive_users = User.where.not(id: active_users) #ar2 query
all_users = active_users | inactive_users # (ar1 + ar2).uniq
user_11178510

SQL:

-- query1
SELECT * FROM table1

INTERSECT

-- query2
SELECT * FROM table2

UNION

-- query1
SELECT * FROM table1

Option 1

User.connection.unprepared_statement do
  User.find_by_sql """
    #{User.where(query1...).to_sql}

    INTERSECT

    #{User.where(query2...).to_sql}

    UNION

    #{User.where(query1...).to_sql}
  """
end

Option 2

(User.where(query1...) & User.where(query2...)) + User.where(query1...)
user_144758520
Related