Home > database >  Ruby - How to filter SQLite rows based on column conditions
Ruby - How to filter SQLite rows based on column conditions

Time:02-02

I have a variable session[:pet_profile_tags] that returns an array like ["adult", "activity_low", "overweight"].

I then have an SQLite database, called balto.db, which contains the table pet_tips. That table contains 2 columns, "ID" (Integer) and "C1_inclusion" (VARCHAR).

For each row of the pet_tips table, I need to check if the value contained in the C1_inclusion column contains one of the values of the session[:pet_profile_tags] array variable. If that is the case, I need to check the row's ID and store it inside another array variable, named pet_tips.

I tried the below code but I am getting the following error: TypeError - no implicit conversion of String into Integer: index.rb:428:in '[]' , line 428 being if (session[:pet_profile_tags].include?(row["C1_inclusion"].to_s))

        # Assign pet tips
        pet_tips = []

        # Query the pet_tips table
        db = SQLite3::Database.open "balto.db"
        rows = db.execute("SELECT * FROM pet_tips")

        # Iterate through each row of the table
        rows.each do |row|
          # Check if the row matches the C1_inclusion column
          if (session[:pet_profile_tags].include?(row["C1_inclusion"].to_s))
            # If the row matches, add the ID to the pet_tips array
            pet_tips << row["ID"]
          end
        end
        session[:pet_tips] = pet_tips
        db.close

I have been stuck for hours, any help would be really appreciated!

First I tried returning the value of the session[:pet_profile_tags] variable to make sure I was getting the correct array. Then, I made sure to check that the different column and variable names where correctly referenced in my code.

CodePudding user response:

Error

Your error is here: row["C1_inclusion"]

  • row is an Array of values in column order e.g. [1,"adult"].

  • Array#[] expects an Integer (index) but you are calling it with a String ("C1_inclusion")

Solutions

To solve this you can

Option 1: Use Integer indexes based on column order

if (session[:pet_profile_tags].include?(row[1]))
  pet_tips << row[0]
end

Option 2: convert the row to Hash:

Always:

db = SQLite3::Database.open "balto.db"
db.results_as_hash
rows = db.execute("SELECT * FROM pet_tips")
rows.each do |row|
  if (session[:pet_profile_tags].include?(row["C1_inclusion"]))

Just for this loop:

rows.each_hash do |row|
  if (session[:pet_profile_tags].include?(row["C1_inclusion"]))

Option 3: Query just the data you want.

# Query the pet_tips table
db = SQLite3::Database.open "balto.db"
session[:pet_tips] = db.prepare("SELECT ID FROM pet_tips WHERE pet_tips.C1_inclusion IN (?)")
  .execute!(session[:pet_profile_tags].map {|s| "'#{s}'"}.join(","))
  .flatten
db.close

This uses session[:pet_profile_tags] as a WHERE condition against pet_tips.C1_inclusion and assumes you have control over this variable (e.g. does not perform escaping)

  • Related