Re: Last event per user

> On Mon, Aug 12, 2019 at 5:03 PM Luís Roberto Weck 
> <[email protected] <mailto:[email protected]>> wrote:
>
>>     If you modify last_user_event_2 to select user and event info in
>>     the view, and just put there where clause directly on the view
>>     which is not joined to anything, instead of on the "extra copy"
>>     of the users table like you were showing previously, I would
>>     expect that the performance should be excellent.
>>
>     But I need user_id and user_group to be outside of the view
>     definition. user_id and user_group are dynamic values, as in, I
>     need to call this query multiple times for different user_ids and
>     user_groups .
>
>
> I don't follow. Perhaps there is something within the limitations of 
> the ORM layer that I am not expecting. If you have this view-
>
> "last_user_event_2"
>
> SELECT u.*, e.*
>
>    FROM users u
>
>         JOIN LATERAL (SELECT *
>
> FROM events
>
>  WHERE user_id = u.user_id
>
>  AND user_group = u.user_group
>
>  ORDER BY timestamp_inc DESC
>
>  LIMIT 1 ) e ON TRUE
>
>
> And you execute a query like this-
>
> SELECT * FROM last_user_event_2 e WHERE  user_id = 1272897 and 
> user_group = 19117;
>
> Then I would expect very good performance.

You're right, thanks! I just had to do a little adjustment on the 
lateral join. Since both users and events have user_id and user_group, 
PostgreSQL complains that I can't have more than one column with the 
same name. I fixed it by changing the LATERAL condition from "ON TRUE" 
to "USING (user_id,user_group)" (which I didn't even knew I could do).