Error when implementing havingRaw() with max()

Question

I'm trying to translate this SQL query to knex:

select
    "events".*,
    "events"."id",
    "events"."version",
    "events"."reviewed_at",
    "events"."source_id"
from "events"
inner join "events" as "events2" on "events".source_id = "events2".source_id
inner join "event_dates" on "events"."id" = "event_dates"."event_id"
where "event_dates"."end_date" > 'NOW()' 
group by
    "events"."id",
    "events"."version",
    "events"."reviewed_at",
    "events"."source_id"
having MAX("events2".reviewed_at) = "events".reviewed_at;

But I can't seem to get the havingRaw() part right. Here's my havingRaw() as of now:

.havingRaw('MAX("events2".reviewed_at) = ?', "events".reviewed_at)

My query gets executed if I exclude havingRaw(..) but I really need to add the having condition.

score 0 · Accepted Answer · answered Dec 07 '18 at 07:44

At least your code seems to have syntax error and you are using raw API wrong:

.havingRaw('MAX("events2".reviewed_at) = ?', "events".reviewed_at)

You are trying to read attribute reviewed_at of string literal "event"... try:

.havingRaw('MAX(??) = ??', ["events2.reviewed_at", "events.reviewed_at"])

Where ?? is identifier substitution, where it gets automatically correctly quoted. If you use just ? replacement, knex tries to pass substitution as a value binding to database driver.

Error when implementing havingRaw() with max()

1 Answers1