Mysql2::错误: View 的 SELECT 在 FROM 子句中包含子查询

Question

每当我尝试执行 rake db:migrate 和 rake db:migrate:redo 时，都会收到错误。我进行了搜索，但找不到与我遇到的问题类似的解决方案。任何帮助将不胜感激。

错误如下:

rake db:migrate:redo
==  CreateConversations: reverting ============================================
-- drop_table(:conversations)
   -> 0.0310s
==  CreateConversations: reverted (0.0569s) ===================================

==  CreateConversations: migrating ============================================
-- create_table(:conversations)
   -> 0.0683s
==  CreateConversations: migrated (0.0686s) ===================================

==  CreateConversationsSummaries: migrating ===================================
-- execute("     CREATE VIEW conversation_summaries AS\n         SELECT c.id,\n         s.name as sender_name,\n         r.name as recipient_name,\n         m.body as most_recent_message_body,\n         m.created_at as most_recent_message_sent_at,\n         (select count(*) from messages AS m2 where m2.conversation_id = c.id) - 1 as reply_count\n         FROM conversations AS c\n         inner join users AS s on s.id = c.sender_id\n         inner join users AS r on r.id = c.recipient_id\n         left outer join (\n           select distinct (conversation_id), body, created_at\n           from messages AS m1\n           order by conversation_id, created_at desc\n         ) AS m ON m.conversation_id = c.id\n")
rake aborted!
An error has occurred, all later migrations canceled:

Mysql2::Error: View's SELECT contains a subquery in the FROM clause:      CREATE VIEW conversation_summaries AS
         SELECT c.id,
         s.name as sender_name,
         r.name as recipient_name,
         m.body as most_recent_message_body,
         m.created_at as most_recent_message_sent_at,
         (select count(*) from messages AS m2 where m2.conversation_id = c.id) - 1 as reply_count
         FROM conversations AS c
         inner join users AS s on s.id = c.sender_id
         inner join users AS r on r.id = c.recipient_id
         left outer join (
           select distinct (conversation_id), body, created_at
           from messages AS m1
           order by conversation_id, created_at desc
         ) AS m ON m.conversation_id = c.id

我的迁移是:

    class CreateConversationsSummaries < ActiveRecord::Migration
  def up
    execute <<-SQL
     CREATE VIEW conversation_summaries AS
         SELECT c.id,
         s.name as sender_name,
         r.name as recipient_name,
         m.body as most_recent_message_body,
         m.created_at as most_recent_message_sent_at,
         (select count(*) from messages AS m2 where m2.conversation_id = c.id) - 1 as reply_count
         FROM conversations AS c
         inner join users AS s on s.id = c.sender_id
         inner join users AS r on r.id = c.recipient_id
         left outer join (
           select distinct (conversation_id), body, created_at
           from messages AS m1
           order by conversation_id, created_at desc
         ) AS m ON m.conversation_id = c.id
     SQL
     end

  def down
    execute 'DROP VIEW conversation_summaries'
  end
end

Answer 1

您忘记了 FROM 子句中表的别名 (AS)，但这不是重点:)。另外，我认为您在 INNER JOIN 用户中需要 s 和 r 而不是 t 和 f ，如果我没记错的话:

CREATE VIEW conversation_summaries AS
    SELECT c.id,
    s.name as sender_name,
    r.name as recipient_name,
    m.body as most_recent_message_body,
    m.created_at as most_recent_message_sent_at,
    ((select count(*) from messages AS m2 where m2.conversation_id = c.id) - 1) as reply_count
    FROM conversations AS c
    inner join users AS s on s.id = c.sender_id
    inner join users AS r on r.id = c.recipient_id
    left outer join (
      select conversation_id, MAX(created_at)
      from messages AS m1
      group by conversation_id
    ) AS m ON m.conversation_id = c.id
SQL
end

您正在执行 LEFT OUTER JOIN，但您选择了 3 个字段，即 conversation_id、body、created_at。只需选择最大的日期，即 MAX(created_at) 和 conversation_id。这应该够了吧。