How can I query a Ruby array, like I do with Rails ActiveRecord?

Question

I have a table, let's call it Widget.

I do some complex processing to get various type of Widgets. These end up in two different variables.

To keep things simple, let's say we have...

widgetsA = Widget.where("blah blah blah")
widgetsB = Widget.where("blah blah blah blah")

We can still perform ActiveRecord functions like .where on widgetsA and widgetsB.

Now, after retrieving the sets for A and B, I need to union them, and then perform additional ActiveRecord functions on them.

I want to do something like this...

widgetsAll = widgetsA | widgetsB
widgetsAll = widgetsAll.order("RANDOM()")
widgetsAll = widgetsAll.where(answers_count: 0).limit(10) + widgetsAll.where("answers_count > 0").limit(10)

This will take all the widgets (union) found in A & B, randomize them, and select 10 with answers and 10 without answers.

The problem is, I cannot user .order and widgetsAll is no longer an ActiveRecord object, but it's an Array because of the widgetsAll = widgetsA | widgetsB line. How do I either

A) Union/Intersect two ActiveRecord sets, into an ActiveRecord set

B) How can I order and perform a 'where' style query on an Array.

Either will solve the issue. I assume B is a bit better for performance, so I suppose that would be the better answer.

Any ideas?

Lastly, lets say the Widget table has columns id, name, description. In the end we want an ActiveRecord or Array (likely preferred) of everything.

EDIT: (Attempting to combine via SQL UNION... but not working)

w1 = Widget.where("id = 1 OR id = 2")
w2 = Widget.where("id = 2 OR id = 3")
w3 = Widget.from("(#{w1.to_sql} UNION #{w2.to_sql})")

PG::SyntaxError: ERROR:  subquery in FROM must have an alias
LINE 1: SELECT "widgets".* FROM (SELECT "widgets".* FROM "widge...

spickermann · Accepted Answer · 2015-05-26 22:36:05Z

2

I see two options:

1) Do the union in SQL: Instead of widgetsA | widgetsB that return an array you can do an union in the database, so that the result is still a relation object:

Widget.from("(#{widgetA.to_sql} UNION #{widgetB.to_sql}) AS widgets")

2) Use normal array methods. Your example:

widgetsAll = widgetsAll.order("RANDOM()")
widgetsAll = widgetsAll.where(answers_count: 0).limit(10) + widgetsAll.where("answers_count > 0").limit(10)

would translate to something like this:

widgetsAll = widgetsAll.shuffle
widgetsAll = widgetsAll.select { |answer| widget.answer_count == 0 }.take(10) + 
             widgetsAll.select { |answer| widget.answers_count > 0).take(10)

13 Comments

chris P Over a year ago

Thanks, this looks like what I want. What do you think is better from a performance point of view? If I do 1, would it be more costly since there is going to be an extra query involved? Would it be better to do 2 with the array manipulation? Or are both likely to be the same, as the SQL is just a UNION anyway on already determined ActiveRecords?

apneadiving Over a year ago

Dont ever do 2. For 1, I'd use OR instead of UNION

spickermann Over a year ago

The first version will be faster, because it does the union in the database, so it runs only on query intead of your example that runs at least two. I add the seconds version only because you asked for it. I would not recommend the second version, the database version is very likely always faster.

spickermann Over a year ago

@apneadiving: OR is different to UNION. In this case, when you combine two relation objects, you need to use UNION.

smathy Over a year ago

Either option is fine @chrisP - some people here are recommending against options (like using the array methods) which are often orders of magnitude faster than doing an SQL query. Even those who are concerned about doing two queries instead of one are worried about queries that usually take fractions of a millisecond. Do whichever makes the most sense to you, they're all great solutions.

|

apneadiving · Accepted Answer · 2015-05-26 22:28:58Z

1

Using the any_of gem you could do:

widgetsAll = Widget.where.any_of(widgetsA, widgetsB)

answered May 26, 2015 at 22:28

apneadiving

116k26 gold badges225 silver badges215 bronze badges

Comments

Community · Accepted Answer · 2017-05-23 12:06:01Z

-1

One way would be to do as follows:

widgetsAllActual = Widget.where(id: widgetsAll)

This is creating a new Widget::ActiveRecord_Relation collection containing all the elements in widgetsA and widgetsB, and allows for making further active record scoping.

Ref: https://stackoverflow.com/a/24448317/429758

edited May 23, 2017 at 12:06

CommunityBot

11 silver badge

answered May 26, 2015 at 22:28

Prakash Murthy

13.1k3 gold badges48 silver badges79 bronze badges

12 Comments

smathy Over a year ago

I edited this to remove the relatively slow .map(&:id) - ActiveRecord just does the right thing for you.

chris P Over a year ago

@smathy Do you know if this would be costly at all (since there is an additional query involved), as opposed to manipulating the already created Array object? Or not likely because the id's are going to be indexed anyway?

chris P Over a year ago

@prakash Do you know if this would be costly at all (since there is an additional query involved), as opposed to manipulating the already created Array object? Or not likely because the id's are going to be indexed anyway?

apneadiving Over a year ago

Its not a great solution: it makes several queries and would not allow you to do order and use pagination

smathy Over a year ago

That is false @apneadiving - you will end up with an AR_Relation in widgetsAllActual and so you will obviously still be able to do order or pagination or any operation that you'd perform on an AR scope.

|

Collectives™ on Stack Overflow

How can I query a Ruby array, like I do with Rails ActiveRecord?

3 Answers 3

13 Comments

Comments

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

13 Comments

Comments

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related