如何使用ActiveRecord在MySQL上使用DISTINCT ON [英] how to use DISTINCT ON with mysql using ActiveRecord

问题描述

想要所有不同用户的所有最新访问.

Want all the latest visit of all the distinct user.

为此，我正在使用以下查询

For this I am using below query

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

获取SQL语法错误.

ActiveRecord::StatementInvalid (Mysql2::Error: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ON(user_id) user_id, time FROM `events` ORDER BY `events`.`time` DESC ' at line 1: SELECT  DISTINCT ON(user_id) user_id, time FROM `events` ORDER BY `events`.`time` DESC LIMIT 11)

事件表列名称

["id", "visit_id", "user_id", "name", "properties", "time"] 

有人可以帮我吗

期望这样的输出

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

21是user_id，值是上次访问时间

21 is user_id and value is last visit time

使用mysql作为数据库

推荐答案

因此，您希望所有用户访问都具有上次访问时间.

So you want all user visits with last visited time.

您可以将GROUP与MAX功能一起使用，而不是使用DISTINCT功能.

Instead of use DISTINCT function, you can use GROUP with MAX function.

查询看起来像

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

这将输出您想要的结果

{21=>Tue, 18 Dec 2018 11:15:24 UTC +00:00, 23=>Thu, 20 Dec 2018 06:42:10 UTC +00:00}

希望这对您有用.

仅供参考 DISTINCT ON(列).是PostgreSQL语法.

FYI DISTINCT ON(columns). is PostgreSQL Syntax.

这篇关于如何使用ActiveRecord在MySQL上使用DISTINCT ON的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！