How to add multiple columns in Sequel

Question

In this example:

require 'rubygems'
require 'sqlite3'
require 'sequel'

db = SQLite3::Database.new "fruits.db"
db2 = Sequel.sqlite("fruits.db")

db.execute 'CREATE TABLE "fruit" ("box_id" INTEGER, "apples" INTEGER, "oranges" INTEGER, "total" INTEGER)'

db.execute "INSERT INTO fruit(box_id,apples,oranges) VALUES(1,2,2)"

thisBox = db2[:fruit][:box_id => 1]

This works in SQLite3:

db.execute "UPDATE fruit SET total = apples + oranges WHERE box_id=1"

But I cannot come up with the same in one line in Sequel:

thisBox.update(:total => :apples + :oranges)

which returns the error:

undefined method '+' for :unfollows:Symbol

The only way around it I've found is:

apples = thisBox[:apples]
oranges = thisBox[:oranges]

thisBox.update(:total => apples + oranges)

Please see "[ask]" and the linked pages and "[mcve](https://stackoverflow.com/help/minimal-reproducible-example). We need to see a minimal code example that demonstrates the problem you're seeing. — the Tin Man, Nov 04 '19 at 05:14
I apologize -- but I thought that's what the code example I included does? I've never had problems with my questions in the past, and I'm not sure what I should be doing different here. Thanks. — stephen, Nov 04 '19 at 12:00
Notice that in the mcve document, that code has to be the minimal runnable code that demonstrates the problem. While snippets are often jumped on and people write all sorts of code around it just to test and try to help, that doesn't help the site. Your code should create a simple DB populate it with some data, the show how it works and doesn't work. — the Tin Man, Nov 04 '19 at 19:47
You're trying to add two symbols `:apples + :oranges`, hence the error. It works in the SQLite3 SQL query because you're sending that to the DBM, which knows that `apples` and `oranges` are columns. The `update` statement needs to know what the values are, so your final example works because the values are retrieved by the previous two lines. Turn on logging and Sequel will show you exactly what it's trying to do as it talks to the DBM. https://sequel.jeremyevans.net/rdoc/files/doc/cheat_sheet_rdoc.html#label-Logging+SQL+statements — the Tin Man, Nov 04 '19 at 19:59
Thank you so much for your answers and your help with this. I've edited my original question to try to match what you suggested above. Please let me know if I'm more on the mark. — stephen, Nov 05 '19 at 03:07
Good changes. It's not hard to define a test suite used to demonstrate what you're running into, and the process often reveals what the problem was, or presents a work-around. — the Tin Man, Nov 05 '19 at 20:54

the Tin Man · Accepted Answer · 2019-11-05T20:50:43.470

I highly recommend reading through the Sequel cheat sheet, README and then the documentation for the different classes. It's extremely powerful and, in my opinion, a great ORM.

Meditate on this as a starting point for how to learn it. It'll also show a simple, but not the most efficient, way to do what you're asking about:

require 'sequel'

require 'logger'
DB = Sequel.sqlite(loggers: [Logger.new($stdout)])

DB.create_table :fruitbaskets do
  primary_key :id
  Integer :apple
  Integer :orange
  Integer :total
end

fruitbaskets = DB[:fruitbaskets]
fruitbaskets.insert(:apple => 1, :orange => 2, :total => 0)

fruit_basket = fruitbaskets.where(:id => 1)

fruit_basket.update(:total => fruit_basket.get([:apple, :orange]).sum) 

fruitbaskets.where(:id => 1).get([:apple, :orange, :total]) # => [1, 2, 3]
# >> I, [2019-11-04T19:30:20.524611 #8709]  INFO -- : (0.000188s) PRAGMA foreign_keys = 1
# >> I, [2019-11-04T19:30:20.524673 #8709]  INFO -- : (0.000013s) PRAGMA case_sensitive_like = 1
# >> I, [2019-11-04T19:30:20.525058 #8709]  INFO -- : (0.000228s) CREATE TABLE `fruitbaskets` (`id` integer NOT NULL PRIMARY KEY AUTOINCREMENT, `apple` integer, `orange` integer, `total` integer)
# >> I, [2019-11-04T19:30:20.525212 #8709]  INFO -- : (0.000060s) SELECT sqlite_version()
# >> I, [2019-11-04T19:30:20.525331 #8709]  INFO -- : (0.000036s) INSERT INTO `fruitbaskets` (`apple`, `orange`, `total`) VALUES (1, 2, 0)
# >> I, [2019-11-04T19:30:20.525477 #8709]  INFO -- : (0.000040s) SELECT `apple`, `orange` FROM `fruitbaskets` WHERE (`id` = 1) LIMIT 1
# >> I, [2019-11-04T19:30:20.525558 #8709]  INFO -- : (0.000023s) UPDATE `fruitbaskets` SET `total` = 3 WHERE (`id` = 1)
# >> I, [2019-11-04T19:30:20.525669 #8709]  INFO -- : (0.000037s) SELECT `apple`, `orange`, `total` FROM `fruitbaskets` WHERE (`id` = 1) LIMIT 1

Note:

fruit_basket = fruitbaskets.where(:id => 1)

fruit_basket.update(:total => fruit_basket.get([:apple, :orange]).sum)

Sequel lets us build statements incrementally if its useful to our code. I'm pointing to a record and can then reuse the variable in different ways. This is really powerful and covered in the documentation.

How to add multiple columns in Sequel

1 Answers1