Sequel response to https://gist.github.com/hadees/cff6af2b53d340b9b4b2

sequel.rb

require "sequel"

DB = Sequel.postgres("arel")
DB.create_table!(:movies) { primary_key :id }

class Movie < Sequel::Model
end

# Asterisk (I agree this one isn't ideal)
Movie.select{count{}.*} # SELECT count(*) FROM "movies"

# Greatest
Movie.select{greatest(title)} # SELECT greatest("title") FROM "movies"

# Least
Movie.select{least(title)} # SELECT least("title") FROM "movies"

# Cast
Movie.select{title.cast(:text)} # SELECT CAST("title" AS text) FROM "movies"

# Null if
Movie.select{NULLIF(one, two)} # SELECT NULLIF("one", "two") FROM "movies"

# Predicate
Movie.select{Sequel.case({a: 1}, 0)} # SELECT (CASE WHEN "a" THEN 1 ELSE 0 END) FROM "movies"

# Tsrange
Movie.select{tsrange(Time.now, Time.now)} # SELECT tsrange('2016-03-05 14:22:28.409835+0700', '2016-03-05 14:22:28.409836+0700') FROM "movies"

# Tstzrange
Movie.select{tstzrange(Time.now, Time.now)} # SELECT tstzrange('2016-03-05 14:22:28.410012+0700', '2016-03-05 14:22:28.410013+0700') FROM "movies"

# Overlap
Sequel.extension :pg_array_ops
Movie.select{a.pg_array.overlaps(b)} # SELECT ("a" && "b") FROM "movies"

# Coalesce
Movie.select{coalesce(title)} # SELECT coalesce("title") FROM "movies"

# Hstore key
Sequel.extension :pg_hstore_ops
Movie.select{data.hstore['key']} # SELECT ("data" -> 'key') FROM "movies"

# Concat
Movie.select{concat('a', 'b')} # SELECT concat('a', 'b') FROM "movies"

# Mod (no nice equivalent)
Movie.select{Sequel.lit('a % b')} # SELECT a % b FROM "movies"

# To char
Movie.select{to_char(title)} # SELECT to_char("title") FROM "movies"

# String agg
Movie.select{string_agg(title)} # SELECT string_agg("title") FROM "movies"

# Array agg
Movie.select{array_agg(title)} # SELECT array_agg("title") FROM "movies"

# ORDER BY in *_agg functions (no good alternative)
Movie.select(Sequel.lit("array_agg(title ORDER BY id)")) # SELECT array_agg(title ORDER BY id) FROM "movies"

# Between (can we use operators instead?)
Movie.select(rating: 0.6..1.0) # "SELECT (("rating" >= 0.6) AND ("rating" <= 1.0)) FROM "movies""

# Unnest
Movie.select{unnest(foo)} # SELECT unnest("foo") FROM "movies"

# Lower
Movie.select{lower(foo)} # SELECT lower("foo") FROM "movies"

# Accumulative OR
Movie.select{a | b | c} # SELECT ("a" OR "b" OR "c") FROM "movies"

# Array intersect
c1 = DB.select{lower(unnest(a1).cast(:text))}
c2 = DB.select{lower(unnest(a2).cast(:text))}
DB.select{ARRAY(c1.intersect(c2))}
# SELECT ARRAY(
#   (SELECT * FROM
#     (SELECT lower(CAST(unnest("a1") AS text)) INTERSECT
#     (SELECT lower(CAST(unnest("a2") AS text)))) AS "t1")
# )

# Sorting
Movie.order(:title) # SELECT * FROM "movies" ORDER BY "title"
Movie.reverse(:title) # SELECT * FROM "movies" ORDER BY "title" DESC


# Descendants search

DB.extension :pg_array

dataset = DB[:descendants_search]
  .exclude(id: 13)
  .order{[array_length(path, 1), path]}
  .select(:id)

nonrecursive = DB[:table]
  .where(id: 13)
  .select(:id, Sequel.pg_array([:id]))

recursive = DB[:descendants_search]
  .join(:table, :reports_to_id => :id)
  .exclude{table__id =~ ANY(path)}
  .exclude{array_length(path, 1) > 999}
  .select(:table__id, Sequel.join([:path, :table__id]))

search = dataset.with_recursive(:descendants_search, nonrecursive, recursive, args: [:id, :path])

Movie.where(id: search)
# SELECT * FROM "movies"
# WHERE ("id" IN (
#   WITH RECURSIVE "descendants_search"("id", "path") AS (
#       SELECT "id", ARRAY["id"]
#       FROM "table"
#       WHERE ("id" = 13)
#     UNION ALL (
#       SELECT "table"."id", ("path" || "table"."id")
#       FROM "descendants_search"
#       INNER JOIN "table"
#       ON ("table"."reports_to_id" = "descendants_search"."id")
#       WHERE (("table"."id" != ANY("path"))
#         AND (array_length("path", 1) <= 999))
#     )
#   )
#   SELECT "id"
#     FROM "descendants_search"
#     WHERE ("id" != 13)
#     ORDER BY array_length("path", 1), "path"
# ))


# Ancestor search

DB.extension :pg_array

dataset = DB[:ancestor_search]
  .exclude(id: 13)
  .order{[array_length(path, 1), path]}
  .select(:id)

nonrecursive = DB[:table]
  .where(id: 13)
  .select(:id, :reports_to_id, Sequel.pg_array([:id]))

recursive = DB[:ancestor_search]
  .join(:table, :id => :reports_to_id)
  .exclude{table__id =~ ANY(path)}
  .exclude{array_length(path, 1) > 999}
  .select(:table__id, :table__reports_to_id, Sequel.join([:path, :table__id]))

search = dataset.with_recursive(:ancestor_search, nonrecursive, recursive, args: [:id, :reports_to_id, :path])

Movie.where(id: search)
# SELECT * FROM "movies"
# WHERE ("id" IN (
#   WITH RECURSIVE "ancestor_search"("id", "reports_to_id", "path") AS (
#       SELECT "id", "reports_to_id", ARRAY["id"]
#       FROM "table"
#       WHERE ("id" = 13)
#     UNION ALL (
#       SELECT "table"."id", "table"."reports_to_id", ("path" || "table"."id")
#       FROM "ancestor_search"
#       INNER JOIN "table"
#       ON ("table"."id" = "ancestor_search"."reports_to_id")
#       WHERE (("table"."id" != ANY("path"))
#         AND (array_length("path", 1) <= 999))
#     )
#   )
#   SELECT "id"
#     FROM "ancestor_search"
#     WHERE ("id" != 13)
#     ORDER BY array_length("path", 1), "path"
# ))