Return table with multiple COUNT from joined table

Question

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

score 0 · Accepted Answer · edited Nov 12 '22 at 08:36

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.

Return table with multiple COUNT from joined table

1 Answers1