CODEWITHSUNDEEP
mysql
Vince Lowe
Vince Lowe

Reputation: 3620

Mysql - Run a user query for all users

I have a user specific query which i need to run for all users.

I am struggling on how to replace the hard coded uuid with a reference or if it needs a different approach altogether?

select max(MaxDate), users.created_at
from (
    select max(`moment`.`created_at`) as MaxDate
    from `moment`
    where `moment`.`user_uuid` = "7dd668af-241a-4176-a1da-f5689214b206"
    union (
        select max(`module`.`updated_at`) as MaxDate 
        from `module` 
        where `module`.`user_uuid` = "7dd668af-241a-4176-a1da-f5689214b206"
    )
) as stuff, `users`
where `users`.`uuid` = "7dd668af-241a-4176-a1da-f5689214b206"

the end goal is to get the date a user was created and a date the same user last updated something and then get the avage time between them. But for all users not a single user.

Upvotes: 0

Views: 73

Answers (2)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521289

Here is a general query which would report all users, sorted by user:

SELECT
    u.user_uuid,
    GREATEST(COALESCE(t1.max_created_at, t2.max_updated_at),
             COALESCE(t2.max_updated_at, t1.max_created_at)) AS max_date
FROM users u
LEFT JOIN
(
    SELECT user_uuid, MAX(created_at) AS max_created_at
    FROM moment
    GROUP BY user_uuid
) t1
    ON u.user_uuid = t1.user_uuid
LEFT JOIN
(
    SELECT user_uuid, MAX(updated_at) AS max_updated_at
    FROM module
    GROUP BY user_uuid
) t2
    ON u.user_uuid = t2.user_uuid
ORDER BY
    u.user_uuid;

If you want to restrict to a single user, you may still do so via a WHERE clause or via a WHERE IN clause for a group of users.

Note that there is a bit of a smell in your database design, because you have your user information strewn across multiple tables. My answer assumes that in general every user would appear in both tables, but maybe this is not the case.

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Use group by

select `users`.`uuid`,max(MaxDate) as maxdate, min(users.created_at) as createddate
from (
    select `moment`.`user_uuid`,max(`moment`.`created_at`) as MaxDate
    from `moment`
    group by `moment`.`user_uuid`
    union 
    select `module`.`user_uuid`,max(`module`.`updated_at`) as MaxDate 
    from `module` group by `module`.`user_uuid`

) as stuff inner join `users` on `users`.`uuid`=stuff.user_uuid
group by `users`.`uuid`

Upvotes: 0

Related Questions