Search code examples
rubysequel

Using equality comparison in Ruby Sequel library's "having" block

I am trying to construct a SQL "having" clause via Sequel, in Ruby (Ruby 2.5.7, Sequel 5.28.0), but having issues with using an equality comparison.

All of the documentation and examples show that constructing a having block works for operators like <, >, <=, and >=. However, = throws a SyntaxError.

If I construct a having statement using >, I get the SQL out correctly:

query.having do sum(Sequel.case({{column => nil} => 1}, 0)) > count.function.* end
=> #<Sequel::Postgres::Dataset: "SELECT * FROM \"schema\".\"table\" GROUP BY \"id\" HAVING (sum((CASE WHEN (\"column\" IS NULL) THEN 1 ELSE 0 END)) > count(*))">

But if I change that > to =, I get a SyntaxError:

query.having do sum(Sequel.case({{column => nil} => 1}, 0)) = count.function.* end
SyntaxError: unexpected '=', expecting keyword_end
...nil} => 1}, 0)) = count.function.* end
...                              ^
SyntaxError: unexpected keyword_end, expecting end-of-input
... 1}, 0)) = count.function.* end
...                            ^~~

What is a valid = operator in this case? I can't find mention in the Sequel documentation or specs that show this usage. If I've missed this, apologies, and am happy to re-read if someone points me to where that information is.

== and <=> both lead to undesired and inaccurate SQL, and I also can't hack it via a combination of <= and >= since the having block doesn't seem to recognize && as and-ing multiple conditions (it just takes the last condition).

query.having do sum(Sequel.case({{column => nil} => 1}, 0)) == count.function.* end
=> #<Sequel::Postgres::Dataset: "SELECT * FROM \"schema\".\"table\" GROUP BY \"id\" HAVING false">

query.having do sum(Sequel.case({{column => nil} => 1}, 0)) <=> count.function.* end
=> #<Sequel::Postgres::Dataset: "SELECT * FROM \"schema\".\"table\" GROUP BY \"id\" HAVING false">

query.having do sum(Sequel.case({{column => nil} => 1}, 0)) >= count.function.* && sum(Sequel.case({{column => nil} => 1}, 0)) <= count.function.* end
=> #<Sequel::Postgres::Dataset: "SELECT * FROM \"schema\".\"table\" GROUP BY \"id\" HAVING (sum((CASE WHEN (\"column\" IS NULL) THEN 1 ELSE 0 END)) <= count(*))">
Solution

  • According to this this discussion, this should work, wrapping it in a virtual row and using a hash with =>

    query.having do 
  { sum(Sequel.case({{column => nil} => 1}, 0)) => count.function.* }
end

    or using =~

    query.having do 
  sum(Sequel.case({{column => nil} =~ 1}, 0)) == count.function.* 
end