SQLite到Postgres（Heroku）GROUP BY [英] SQLite to Postgres (Heroku) GROUP BY

问题描述

以下SQLite代码组通过conversation_id消息：

  @ messages = Message.where（messages.sender_id =（？ ）或messages.recipient_id =（？）
，current_user.id，current_user.id）.group（messages.conversation_id）
  

在移植到Heroku时，Postgres无法识别此代码。查看日志，我被告知将所有消息列添加到GROUP BY - 让我看看这个功能代码：

  @ messages.where（messages.sender_id =（？）OR messages.recipient_id =（？）
，current_user.id，current_user.id）.group（messages.conversation_id 
，messages。 updated_at messages.id messages.sender_id 
 messages.recipient_id messages.sender_deleted 
 messages.recipient_deleted messages.body message.read_at 
 messages.ancestry messages.ancestry_depth， messages.created_at）
  

只有这段代码没有按conversation_id正确分组。它只是输出符合WHERE条件的所有消息。任何想法？

解决方案

我使用 DISTINCT ON $ b $ pre $ @messages = Message.select（DISTINCT ON（messages.conversation_id）*）
。（messages.sender_id =（？）OR messages.recipient_id =（？），current_user.id，current_user.id）
.group（messages.conversation_id，messages.updated_at，messages.id，messages .sender_id，messages.recipient_id，messages.sender_deleted，messages.recipient_deleted，messages.body，messages.read_at，messages.ancestry，messages.ancestry_depth，messages.created_at）


但是，这不会在SQLite中起作用。下载Postgres并直接使用它，而不必在开发中使用SQLite代码，在生产中使用Postgres代码（Heroku）。

The following SQLite code groups Messages by conversation_id:

@messages=Message.where("messages.sender_id = (?) OR messages.recipient_id = (?)"
          , current_user.id, current_user.id).group("messages.conversation_id")

In moving over to Heroku, this code isn't recognized by Postgres. Looking at the logs, I'm told to add all Message columns to GROUP BY - getting me to this functional code:

@messages=Message.where("messages.sender_id = (?) OR messages.recipient_id = (?)"
          , current_user.id, current_user.id).group("messages.conversation_id
          , messages.updated_at, messages.id, messages.sender_id
          , messages.recipient_id, messages.sender_deleted
          , messages.recipient_deleted, messages.body, messages.read_at
          , messages.ancestry, messages.ancestry_depth, messages.created_at")

Only this code doesn't group by conversation_id correctly. It simply outputs all messages that meet the WHERE condition. Any ideas?

解决方案

I arrived at a functional solution with the use of DISTINCT ON:

@messages = Message.select("DISTINCT ON (messages.conversation_id) * ")
                   .where("messages.sender_id = (?) OR messages.recipient_id = (?)", current_user.id, current_user.id)
                   .group("messages.conversation_id, messages.updated_at, messages.id, messages.sender_id, messages.recipient_id, messages.sender_deleted, messages.recipient_deleted, messages.body, messages.read_at, messages.ancestry, messages.ancestry_depth, messages.created_at")

However, this wont work in SQLite. Downloading Postgres and using it directly rather than having to use SQLite code in development and Postgres code in production (Heroku) is recommended.

这篇关于SQLite到Postgres（Heroku）GROUP BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！