Rails: How can I use .to_sql on a .select() request-CodePudding

I have an ActiveRecord request:

Post.all.select { |p| Date.today < p.created_at.weeks_since(2) }

And I want to be able to see what SQL request this produces using .to_sql

The error I get is: NoMethodError: undefined method 'to_sql'

TIA!

CodePudding user response：

You need to call to_sql on a relation. select executes the query and gives you the result, and on the result you don't have to_sql method.

There are similar questions which you can look at as they offer some alternatives.

CodePudding user response：

ISSUE

There are 2 types of select when it comes to ActiveRecord objects, from the Docs

select with a Block.

First: takes a block so it can be used just like Array#select.
This will build an array of objects from the database for the scope, converting them into an array and iterating through them using Array#select.

This is what you are using right now. This implementation which will load every post instantiate a Post object and then iterating over each Post using Array#select to filter the results into an Array. This is highly inefficient, cannot be chained with other AR semantics (e.g. where,order,etc.) and will cause very long lags at scale. (This is also what is causing your error because Array does not have a to_sql method)

select with a list of columns (or a String if you prefer)

Second: Modifies the SELECT statement for the query so that only certain fields are retrieved...

This version is unnecessary in your case as you do not wish to limit the columns returned by the query to posts.

Suggested Resolution:

Instead what you are looking for is a WHERE clause to filter the records at the database level before returning them to the ORM.

Your current filter is (X < Y 2)

 Date.today < p.created_at.weeks_since(2)

which means Today's Date is less than Created At plus 2 Weeks.

We can invert this criteria to make it easier to query by switching this to Today's Date minus 2 weeks is less than Created At. (X - 2 < Y)

Date.today.weeks_ago(2) < p.created_at

This is equivalent to p.created_at > Date.today.weeks_ago(2) which we can convert to a where clause using standard ActiveRecord query methods:

Post.where(created_at: Date.today.weeks_ago(2)...)

This will result in SQL like:

SELECT 
  posts.*
FROM 
  posts.* 
WHERE 
  posts.created_at > '2022-10-28'

Notes:

created_at is a TimeStamp so it might be better to use Time.now vs Date.today.
Additional concerns may be involved from a time zone perspective since you will be performing date/time specific comparisons.