Ruby - How to filter SQLite rows based on column conditions

Question

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.

engineersmnky · Accepted Answer · 2023-02-01 20:28:22Z

2

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)

edited Feb 1, 2023 at 20:28

answered Feb 1, 2023 at 19:55

engineersmnky

30.5k2 gold badges42 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nvercher Over a year ago

Many thanks for the super clear answer, it worked like a charm (I used option 1)! Really appreciate it!

Collectives™ on Stack Overflow

Ruby - How to filter SQLite rows based on column conditions

1 Answer 1

Error

Solutions

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Error

Solutions

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related