Invalid column reference when trying to fetch distinct column

[otilor]

Problem description

When trying to retrieve unique attendees of an event. I'm using this query:

Attendee::query()->distinct('user_id')

It works on its own but when using it in this chart widget;

Trend::query(Attendee::query()->distinct('user_id'))
            ->between(
                start: now()->setDate(2021, 1, 1),
                end: now(),
            )
            ->perMonth()
            ->count();

I get this error:

SQLSTATE[42P10]: Invalid column reference: 7 ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions LINE 1: select distinct on ("user_id") ^ (SQL: select distinct on ("user_id") to_char(created_at, 'YYYY-MM') as date, count(*) as aggregate from "attendees" where "created_at" between 2021-01-01 16:43:04 and 2022-10-14 16:43:04 group by "date" order by "date" asc)

[Larsklopstra]

Closing stale issue

[vpuentem]

Did you manage to get it? I'm trying to do something similar with no results :/

[otilor]

Can't really remember. I think I did tho'. No longer have access to the codebase.

[vpuentem]

This works apparently, if you look at the aggregate() method used by the count(), sum(), etc. It uses the column name and the aggregate in a string, so you can pass different values depending on what you need

Trend::query(Attendee::query())
            ->between(
                start: now()->setDate(2021, 1, 1),
                end: now(),
            )
            ->perMonth()
            ->count('DISTINCT user_id');

[otilor]

Ok. Thank you.

[MCKLtech]

@vpuentem , thank you, this worked for me.