CODEWITHSUNDEEP
mysqlsqlrubysequel
user1706938
user1706938

Reputation: 131

Writing a complex case statement in Sequel?

I have a fairly complex case statement that works in MySQL:

SELECT # rest of code omitted
CASE WHEN code = 'a' THEN 'x'
  WHEN code IN ('m', 'n') THEN 'y'
  WHEN class IN ('p', 'q') AND amount < 0 THEN 'z'
  ELSE NULL END AS status
FROM # rest of code omitted

However, all attempts to write this in Sequel have failed. I am using this as a template:

Sequel.case([[:c, 1], [:d, 2]], 0) # (CASE WHEN "c" THEN 1 WHEN "d" THEN 2 ELSE 0 END)

(from Jeremy Evans' Github)

My best guess would be:

dataset.select( # rest of code omitted...
[[(:code => 'a'), 'x'],
[(:code => 'b'), 'y'],
[(:class => ['p', 'q'], :amount < 0), 'z']].case(nil).as(:status))

Any ideas?

Upvotes: 6

Views: 3116

Answers (2)

Jacob
Jacob

Reputation: 96

Since this is the top post when googling for complex sequel case statement here is an answer that I think is cleaner then the other ones.

Sequel.case(
  {
    { code: 'a' } => 'x',
    { code: ['m', 'n'] } => 'y',
    ((Sequel[:class] =~ ['p', 'q']) & (Sequel[:amount] < 0)) => 'z'
  },
  nil
)

SELECT (
CASE WHEN ("code" = 'a') THEN 'x'
WHEN ("code" IN ('m', 'n')) THEN 'y'
WHEN (("class" IN ('p', 'q')) AND ("amount" < 0)) THEN 'z'
ELSE NULL END) FROM "test"

Upvotes: 0

dan
dan

Reputation: 106

After having a play with this, I have concluded that although the sequel gem aims to be be "simple, flexible and powerful", its syntax gets quite convoluted when things get a bit tricky.

Here is my best attempt at your query:

DB[:testtable].select( 
  Sequel.case([
  [{code: 'a'}, 'x'],
  [{code: ['m', 'n']}, 'y'], 
  [{class: ['p', 'q'], (Sequel.expr(:amount) > 0) => true}, 'z']], 
  nil).
  as(:status)
)

This produces the following (almost correct) SQL:

SELECT (
CASE WHEN (`code` = 'a') THEN 'x' 
WHEN (`code` IN ('m', 'n')) THEN 'y' 
WHEN ((`class` IN ('p', 'q')) AND ((`amount` > 0) IS TRUE)) THEN 'z' 
ELSE NULL END) AS `status` FROM `testtable`

I could not figure out how to use the inequality operator within a case statement. Maybe you'll have more luck with that.

My suggestion is that you just write the query in SQL, it will be significantly easier to read, and more maintainable.

Upvotes: 7

Related Questions