Writing a complex case statement in Sequel?

Question

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?

Electromotor

Have you asked on Sequel-talk? That's where Jeremy Evans and the gurus hang out. – Encaenia

Answer 1

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.

Hodgepodge

Thank you for your pains! I'm inclined to agree with your last statement - though I had set out to write everything in Sequel for thoroughness, nothing is quite as readable as the original. – Electromotor

!~ can be used for not in and not eq. Sequel.case({ (Sequel[:class] !~ 'a') => 'b' }, nil) -> CASE WHEN ("class" != 'a') THEN 'b' .... – Cockaigne

Answer 2

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"

Cockaigne