在ruby2.7中显示Sqlite3视图的问题

Question

我在显示使用ruby创建的视图(在sqlite3中成功创建)时遇到了问题。

下面是sqlite视图

CREATE VIEW members_ytd_totals AS
    SELECT id AS MemberID,
    first_name,
    last_name,
    ipu_dues.total AS TotalDues,
    ipu_levy.total AS TotalLevy,
    ipu_fines.total AS TotalFines,
    ipu_donation.total AS TotalDonations,
    ipu_registration.total AS TotalRegistrations

FROM ipu_profile
       INNER JOIN
       ipu_dues ON ipu_dues.id = ipu_profile.id
       INNER JOIN
       ipu_levy ON ipu_levy.id = ipu_profile.id
       INNER JOIN
       ipu_fines ON ipu_fines.id = ipu_profile.id
       INNER JOIN
       ipu_donation ON ipu_donation.id = ipu_profile.id
       INNER JOIN
       ipu_registration ON ipu_registration.id = ipu_profile.id;

通常，我应该能够在ruby中使用一个简单的select语句来显示以下视图

require 'sqlite3'
require 'sqlite_print_table'
db = SQLite3::Database.open 'database.db'
db.print_table("SELECT * FROM members_ytd_totals", guide_tolerance: 4, width_tolerance: 5, margin: 10)

然而，我得到一个数据库异常发生-模棱两可列名: id

但是在sqlite3内部，这个视图是正确创建的，没有错误，我可以在studio中看到这个视图。我只是不能用红宝石来展示。有什么好主意吗？

谢谢

Answer 1

当您说我可以在studio中看到视图时，您的意思是视图的创建没有任何错误。

但是你有没有试着用它，比如说：

SELECT * FROM members_ytd_totals;

你也会犯同样的错误。

使用别名，特别是在涉及多个联接的情况下，总是用表的名称/别名对列名进行限定，以避免歧义：

CREATE VIEW members_ytd_totals AS 
SELECT 
  p.id AS MemberID, p.first_name, p.last_name, 
  d.total AS TotalDues, l.total AS TotalLevy, 
  f.total AS TotalFines, n.total AS TotalDonations, 
  r.total AS TotalRegistrations 
FROM ipu_profile p
INNER JOIN ipu_dues d ON d.id = p.id 
INNER JOIN ipu_levy l ON l.id = p.id 
INNER JOIN ipu_fines f ON f.id = p.id 
INNER JOIN ipu_donation n ON n.id = p.id 
INNER JOIN ipu_registration r ON r.id = p.id;