Resolved: What is the Ruby ActiveRecord equivalent for this SQL?

In this post, we will see how to resolve What is the Ruby ActiveRecord equivalent for this SQL?

Question:

What would be an optimized (fast) ActiveRecord equivalent in Ruby:

SELECT DISTINCT customers.city 
FROM customers
INNER JOIN (
  SELECT c_id 
  FROM carts
  WHERE shop_id = #{`shop_id`}
  ORDER BY created_at DESC 
  LIMIT 1000 OFFSET 1000
) AS filtered_carts ON customers.id = filtered_carts.c_id;

Note: shop_id is a ruby variable passed to the query/association. Thank you
This above SQL runs as fast as expected.
I am trying to find a pure ActiveRecord solution to avoid something like this:

customers = Customer
  .select(:city)
  .distinct
  .joins("
    INNER JOIN (
      SELECT c_id
      FROM carts
      WHERE shop_id = #{`shop_id`}
      ORDER BY created_at DESC
      LIMIT 1000 OFFSET 1000
    ) AS filtered_carts
    ON customers.id = filtered_carts.c_id
  ")

The reason is to avoid having hardcoded SQL in the ActiveRecord to prevent possible future issues with backward compatibility of eventual model chages and migrations…

Best Answer:

I think you do not need the JOIN at all. A subquery in the where should be enough:

customer_ids = 
  Cart
    .select(:c_id)
    .where(shop_id: shop_id)
    .order(:created_at)
    .limit(1000)
    .offset(1000)

Customer
  .where(id: customer_ids)
  .distinct
  .pluck(:city)

Note that the customer_ids is only a ActiveRecord::Relation and that the query is not run immediately. It is only run as subquery when the second query is executed.

If you have better answer, please add a comment about this, thank you!

Source: Stackoverflow.com

Resolved: What is the Ruby ActiveRecord equivalent for this SQL?

Resolved: How to stop a thread that has a blocking function from easygui in python

Resolved: Removing null keys from a json array of objects

Resolved: How can I generate at compile-time a separate OpenAPI Swagger.json file for each Controller in my ASP.NET project?

Question:

Best Answer:

Related Posts

Resolved: How to stop a thread that has a blocking function from easygui in python

Resolved: Removing null keys from a json array of objects

Resolved: How can I generate at compile-time a separate OpenAPI Swagger.json file for each Controller in my ASP.NET project?