SQL query to Rails ActiveRecord

Question

I have the following SQLite query, which is the ActiveRecord equivalent?

My main goal here is to build a clients table with a validation of how many sales of the client are over 29 days of antiquity. Any help is appreciated guys.

select
    clients.id,
    clients.razon,
    clients.rfc,
    clients.state,
    clients.city,
    count(sales.id) AS "Num sales",
    SUM(
        CASE WHEN (
            (julianday('now') - julianday(sales.created_at) > 29)
        ) THEN 1 ELSE 0 END
    ) AS outdated_sales
from clients
join sales
    where clients.id = sales.client_id
    and sales.sku != ""
GROUP by sales.client_id
ORDER BY sales.created_at asc;

Edit your question and the rails code that isn't executing the query, and any error messages you are seeing. Please, no screen shots or images.... — Sloan Thrasher
– Sloan Thrasher, Commented Sep 11, 2018 at 5:09
How does your code look so far? How do your models look like? How did you define the association between clients and sales in your models? — spickermann
– spickermann, Commented Sep 11, 2018 at 5:15
When I run the query on "DB Browser for SQLite" Windows app, the code runs fine, but when I try to run it in my Rails app it throws a ActiveRecord::StatementInvalid error, that's why I'm searching if there is a ActiveRecord equivalent to this query. — user3754535
– user3754535, Commented Sep 11, 2018 at 5:16
My main goal is to build a clients table with a validation of how many sales of the client are over 29 days of antiquity. — user3754535
– user3754535, Commented Sep 11, 2018 at 5:20
Why is there a mysql tag here? The date criteria should be part of the WHERE criteria. sales.created_at < (julianday('now') - 29) maybe. — danblack
– danblack, Commented Sep 11, 2018 at 5:34

engineersmnky · Accepted Answer · 2018-09-11 13:19:46Z

1

Below could be an equivalent ruby query for the given sql query.

Client.joins(:sales)
  .where("clients.id == sales.client_id AND sales.sku IS NOT IN (?)", ["", nil])
  .select("id, razon, rfc, state, city, count(sales.id) as num_sales")
  .order("sales.created_at")

Hope this helps.

edited Sep 11, 2018 at 13:19

engineersmnky

30.5k2 gold badges42 silver badges64 bronze badges

answered Sep 11, 2018 at 6:40

Tushar Kulkarni

1345 bronze badges

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

2 Comments

user3754535 Over a year ago

Thank you very much, but the code still lack the most important column, the "outdated_sales" part, that's the thing I'm looking for.

engineersmnky Over a year ago

Also clients.id == sales.client_id can be removed as it is implied by the join and if it is not removed == should be =.

Collectives™ on Stack Overflow

SQL query to Rails ActiveRecord

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related