CODEWITHSUNDEEP
rubysqlitesqlite3-ruby
Charlie_oscar
Charlie_oscar

Reputation: 57

Problem to display Sqlite3 View in ruby-2.7

I am having problems displaying a view I created (successfully in sqlite3) using ruby.

Below is the sqlite view

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;

normally i should be able to use a simple select statement in ruby to display this view like

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)

however I get A database Exception occurred - ambiguous column name: id

however inside sqlite3, this view was created properly, with no errors and I can see the view in Sqlite studio. I just cannot display it in ruby. Any ideas what the problem can be?

Thanks

Upvotes: 1

Views: 181

Answers (1)

forpas
forpas

Reputation: 164214

When you say I can see the view in Sqlite studio you mean that the View was created without any errors.
But did you tried to use it, say with:

SELECT * FROM members_ytd_totals;

you would get the same error.
Use aliases, especially in cases like this where multiple joins are involved and always qualify the column names with the table's name/alias to avoid ambiguouities:

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;

Upvotes: 0

Related Questions