ActiveRecord Query with nested table

Question

I am trying to make next query with ActiveRecord and have no idea about how to achieve this SQL result without using find_by_sql call.

SELECT *
FROM table_x AS a,
    (SELECT locator, max(day_parsed) AS max_day
     FROM table_x
     WHERE day_parsed BETWEEN params[:day_from] AND params[:day_to]  
     GROUP BY locator) AS b
WHERE a.locator = b.locator
AND a.day_parsed = b.max_day

Any idea?

What's wrong with find_by_sql? Why bother converting a readable SQL query to a non-readable ActiveRecord code? — Mihai Dinculescu
– Mihai Dinculescu, Commented Feb 10, 2016 at 16:54
find_by_sql returns a Hash. Ideally I would like to create an ActiveRecord model based in this result. I tried with a postgresql view, but because I need the date range params I discarded to use DB views. What is wrong is that I cannot use the active_record associations — Fran Martinez
– Fran Martinez, Commented Feb 10, 2016 at 17:04
For example, I would like to use an scope based in this result. — Fran Martinez
– Fran Martinez, Commented Feb 10, 2016 at 17:26

Fran Martinez · Accepted Answer · 2016-02-10 18:19:10Z

2

For example, we have active record TableX and its table name is table_x, so the query will be:

from = params[:day_from]
to   = params[:day_to]

TableX.joins("JOIN (
                     SELECT locator, max(day_parsed) AS max_day
                     FROM table_x
                     WHERE day_parsed BETWEEN #{from} AND #{to}  
                     GROUP BY locator
                   ) as b ON b.locator = table_x.locator")
      .where("table_x.day_parsed = b.max_day")

edited Feb 10, 2016 at 18:19

Fran Martinez

3,0822 gold badges29 silver badges38 bronze badges

answered Feb 10, 2016 at 17:29

Hieu Pham

6,6922 gold badges33 silver badges50 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Hieu Pham Over a year ago

what are the values of params[:day_from] and params[:day_to]?

Fran Martinez Over a year ago

for example "20010101" and "20160301"

Fran Martinez Over a year ago

If I hardcode the params it works fine, but I am creating an scope with this query that accepts this two parameters

Hieu Pham Over a year ago

To ensure it works, I think you may convert params[:day_from] to DateTime first

Fran Martinez Over a year ago

I have edited your response with the changes I made. Thanks!

Collectives™ on Stack Overflow

ActiveRecord Query with nested table

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related