How to use ActiveRecord for a sql query with subquery in FROM clause

Question

Do you know how to translate a SQL query with a subquery in a FROM clause ?

Here is an example :

SELECT 
    *
FROM
    cars
WHERE
    cars.id IN (
    SELECT
        cars.user_id
    FROM 
        cars AS cf, (SELECT
        cars.user_id, MAX(consumption) AS consumption FROM
        cars
    GROUP BY
        user_id) AS t1
    WHERE
        cars.consumption = t1.consumption
        AND
        cars.user_id = 2
        AND
        t1.user_id = cars.user_id)

I have a feeling you're overcomplicating things here. Can you provide an example data set and an example result set from that data that you're trying to select? — Jon
– Jon, Commented Jan 6, 2020 at 13:09

Roman Alekseiev · Accepted Answer · 2020-01-06 12:14:35Z

1

Car.where('id IN (SELECT cars.user_id FROM cars AS cf, (SELECT cars.user_id, MAX(consumption) AS consumption FROM cars GROUP BY user_id) AS t1 WHERE cars.consumption = t1.consumption AND cars.user_id = :user_id AND t1.user_id = cars.user_id)', user_id: 2)

answered Jan 6, 2020 at 12:14

Roman Alekseiev

1,92120 silver badges25 bronze badges

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

1 Comment

Defoncesko Over a year ago

Thanks, why complicate things when they can be simple?

Collectives™ on Stack Overflow

How to use ActiveRecord for a sql query with subquery in FROM clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related