I have 2 tables: Client
and Document
A client
has many documents
Client |
---|
id |
name |
Document |
---|
id |
name |
expiration_date |
client_id |
expiration_date format is YYYY-MM-DD
And I want to return a table like this:
id | name | documents_count | expired | 30 days |
---|---|---|---|---|
1 | client a | 10 | 5 | 2 |
2 | client b | 8 | 8 | 0 |
3 | client c | 13 | 0 | 10 |
Right now my code is like this:
Client.all.left_joins(:documents).group(:id).select('clients.*, COUNT(documents.id) AS documents_count')
And I am missing the last 2 columns.
How can I get those 2 columns?
I tried:
Client.all.left_joins(:documents).group(:id).select("clients.*, COUNT(documents.id) AS documents_count, COUNT(documents.expiration < #{Date.today}) AS expired")
but the expired columns return booleans and not a number.
id | name | documents_count | expired |
---|---|---|---|
1 | client a | 10 | true |
2 | client b | 8 | true |
3 | client c | 13 | true |
CodePudding user response:
setup.
BEGIN;
CREATE TABLE client (
id bigint,
name text
);
CREATE TABLE document (
id bigint,
name text,
expiration_date date,
client_id int
);
INSERT INTO client
SELECT
i,
'client_' || i
FROM
generate_series(1, 5) _ (i);
INSERT INTO document (id, name)
SELECT
i,
'document' || i
FROM
generate_series(1, 5) _ (i),
generate_series(1, 3, 1);
COMMIT;
UPDATE
document
SET
client_id = (4 * random())::int 1;
UPDATE
document
SET
expiration_date = (date '2022-10-25' (random() * 30)::int);
query
SELECT DISTINCT
c.id,
c.name,
count(expiration_date) FILTER (WHERE expiration_date > CURRENT_DATE -30) OVER (PARTITION BY d.id) AS count_last_month,
count(expiration_date) FILTER (WHERE expiration_date > CURRENT_DATE) OVER (PARTITION BY d.id) AS count_expire
FROM
client c
LEFT JOIN document d ON d.id = c.id
ORDER BY
c.id;
or a simple one.
SELECT
c.id,
c.name,
count(expiration_date) FILTER (WHERE expiration_date > CURRENT_DATE -30) AS count_last_month,
count(expiration_date) FILTER (WHERE expiration_date > CURRENT_DATE) AS count_expire
FROM
client c
LEFT JOIN document d ON d.id = c.id
GROUP BY
1,
2
ORDER BY
c.id;
I don't know ruby, but from this post, I think you can run pure sql query.