CODEWITHSUNDEEP
ruby-on-railswhere-clauselimit
ecki
ecki

Reputation: 790

how to get subset of activerecord objects after performing .limit()?

I want to be able to limit the activerecord objects to 20 being returned, then perform a where() that returns a subset of the limited objects which I currently know only 10 will fulfil the second columns criteria.

e.g. of ideal behaviour:

o = Object.limit(20)
o.where(column: criteria).count
=> 10

But instead, activerecord still looks for 20 objects that fulfil the where() criteria, but looks outside of the original 20 objects that the limit() would have returned on its own.

How can I get the desired response?

Upvotes: 0

Views: 192

Answers (1)

The Wizard
The Wizard

Reputation: 953

One way to decrease the search space is to use a nested query. You should search the first N records rather than all records which match a specific condition. In SQL this would be done like this:

select * from (select * from table order by ORDERING_FIELD limit 20) where column = value;

The query above will only search for the condition in 20 rows from the table. Notice how I have added a ORDERING_FIELD, this is required because each query could give you a different order each time you run it.

To do something similar in Rails, you could try the following:

Object.where(id: Object.order(:id).limit(20).select(:id)).where(column: criteria)

This will execute a query similar to the following:

SELECT [objects].* FROM [objects] WHERE [objects].[id] IN (SELECT TOP (20) [objects].[id] FROM [objects] ORDER BY [objects].id ASC) AND [objects].[column] = criteria

Upvotes: 1

Related Questions