Query for all records with a status plus the last one (no matter what status) in one query

25
February 11, 2019, at 06:20 AM

I have something like:

(Model.where(status: :active).to_a << Model.last).uniq

I'm looking for a way to make it happen in one single query. Is that possible?

Answer 1

I don't think you can use a single query, maybe you can do a query with a nested subquery but you can't do that easy with just ActiveRecord (maybe with Arel).

Personally, I would use 2 ActiveRecord queries, one to get the las record's id and then the actual query:

last_id = Model.last.id
records = Model.where(status: active).or(Model.where(id: last_id))

You'll have 2 queries but the first one will be really depreciable and the second one is a lot faster than turning all the results into an array and doing uniq.

Answer 2

You can construct a query with plain SQL, and then use ActiveRecord#find_by_sql to get array of ActiveRecord objects:

query = <<-SQL
  SELECT * FROM subscriptions
  WHERE
   status = 'active' OR
   id = (SELECT max(id) FROM subscriptions)
SQL
Subscription.find_by_sql(query) #=> [ array of subscriptions]

If you need a ActiveRecord::Relation instance, use ActiveRecord#from method. It allows to pass plain SQL into FROM statement. Since #from returns relation you can use all Active Record methods for querying

Subscription.from(query).where('created_at > ?', Time.zone.now)

It builds single query like this:

SELECT * FROM (
  SELECT * FROM subscriptions
  WHERE
   status = 'active' OR
   id = (SELECT max(id) FROM subscriptions)
) WHERE created_at > '2019-01-01 00:00'
READ ALSO
Using junction tables in PHP and MySQL to categorize and include and exclude categories

Using junction tables in PHP and MySQL to categorize and include and exclude categories

I'm trying to analyze tweets using manually assigned categoriesEverything's stored in a MySQL database

32
pgloader mysql to Postgres command line exclude table

pgloader mysql to Postgres command line exclude table

I have the following command that works

63
Phpmyadmin, I want to restore the backup of the databases

Phpmyadmin, I want to restore the backup of the databases

I've gone and reviewed all the databases and I chose the export option in phpmyadmin and he collect all the databases in one file now I want to restore this filePhpmyadmin Say Select Database

46
How can I select from the same table with different where conditions and differnet group by conditions in this case?

How can I select from the same table with different where conditions and differnet group by conditions in this case?

Basically this is what I am trying to achieve but I don't know how to do it in a single query, I don't know if it can be done in a single query

34