Tarlen Tarlen - 2 months ago 8
SQL Question

Limitting results in association

I want to limit the results in a lateral join, so that it only returns the N most recent matches.

This is my query, but the limit inside the join does not seem to work, as it returns all visitors

select am.id, am.title, ame.event, array_agg(row_to_json(visitors))
from auto_messages am
left join apps a on am.app_id = a.id
left join app_users au on a.id = au.app_id
left join auto_message_events ame on ame.auto_message_id = am.id
left join lateral (
select
id,
name,
avatar,
ame.inserted_at
from visitors v
where v.id = ame.visitor_id
order by ame.inserted_at desc
limit 1
) as visitors on visitors.id = ame.visitor_id
where am.id = '100'
group by am.id, ame.event

Answer

I am pretty sure the problem is with ame. That is where the rows are generated. The join to visitors is only picking up additional information.

So, this might solve your problem:

select am.id, am.title, visitors.event, array_agg(row_to_json(visitors))
from auto_messages am left join
     apps a
     on am.app_id = a.id left join
     app_users au
     on a.id = au.app_id left join lateral
     (select v.id, v.name, v.avatar,
             ame.event, ame.inserted_at, ame.auto_message_id
      from auto_message_events ame join
           visitors v
           on v.id = ame.visitor_id
      order by ame.inserted_at desc
      limit 1
     ) visitors
     on visitors.auto_message_id = am.id
where am.id = '100'
group by am.id, visitors.event;

You also might want to change your select clause, if you only want a subset of columns.