How to Find the Latest Visit for Each User in MySQL with ActiveRecord?

How to Find the Latest Visit for Distinct Users in MySQL with ActiveRecord

Encountering a syntax error while attempting to use the DISTINCT ON function with MySQL in ActiveRecord? Here's a solution:

The initial query:

Event.order(time: :desc).select('DISTINCT ON(user_id) user_id, time')

returns an error because DISTINCT ON is not supported by MySQL. Instead, you can use a combination of GROUP BY and MAX to achieve the desired result.

Modified query:

Events.group(:user_id).maximum(:time)

This query will group the events by user_id and find the maximum time for each group, effectively giving you the latest visit for each distinct user.

Expected Output:

{ 21 => "2018-12-18 09:44:59", 42 => "2018-12-19 12:08:59"}

Note that the timestamp format may vary depending on your database configuration.

The above is the detailed content of How to Find the Latest Visit for Each User in MySQL with ActiveRecord?. For more information, please follow other related articles on the PHP Chinese website!