Join activities with users makes redundant rows, I just want first activity-CodePudding

I am on a postgres database

So I have a raw sql query like the following I intend to use to query the database

Select
acts.created_at as "firstActivity",
users.*
from  users
join activities acts on acts.user_id = users.id 
and acts.created_at > users.created_at 
and acts.created_at < users.updated_at
where users.region_id='1'

the problem is that there are multiple activities in between the user's creation and update. The created_at and updated_at fields are of course dates like the following 2021-11-10 09:27:14 00

I would like to only return the first activity of those activities between the two times.

CodePudding user response：

Take a look at DISTINCT ON (expression), this is probably what you need

Select DISTINCT ON (users.id) id, acts.created_at as "firstActivity",
from users
join activities acts on acts.user_id = users.id 
and acts.created_at > users.created_at 
and acts.created_at < users.updated_at
where users.region_id='1'
Order by users.id, acts.created_at

See documentation

CodePudding user response：

well if I underattended your question right

you should use the function min(created_at/updated_at)

in your query and you can add the condition of between the dates A and B

CodePudding user response：

If you only want to join the first activity per user, then use a lateral join:

select
  a.created_at as "firstActivity",
  u.*
from  users u
cross join lateral
(
  select *
  from activities acts 
  where acts.user_id = u.id 
  and acts.created_at > u.created_at 
  and acts.created_at < u.updated_at
  order by acts.created_at
  fetch first row only
) a
where u.region_id = '1';

By joining only the rows you are interested in, you prevent from getting a big intermediate result that you must then deal with afterwards.