Skip to content

Instantly share code, notes, and snippets.

@alassek
Last active November 19, 2023 11:53
Show Gist options
  • Save alassek/0fa0c861ce120c0027bdd11c5b291f15 to your computer and use it in GitHub Desktop.
Save alassek/0fa0c861ce120c0027bdd11c5b291f15 to your computer and use it in GitHub Desktop.
Extending Arel to support @> operator

I've been doing some work lately with JSON documents in PostgreSQL using jsonb columns. You can query these documents using a GIN index in PG, but the syntax can be a little cumbersome

SELECT "events".* FROM "events" WHERE "events"."body" @> '{"shift":{"user_id":2}}'

You have to construct the right side of the query as a JSON string, which is a bit annoying. So I wondered if I could adapt Arel to do the tedious stuff for me.

Essentially what I'm doing above is a standard infix notation, with a PG-specific operator.

module Arel
  module Nodes
    class Contains < Arel::Nodes::InfixOperation
      def initialize(left, right)
        super(:'@>', left, right)
      end
    end
  end
end

The SQL visitor for PostgreSQL needs to understand this new node, but it doesn't need any special functionality.

module Arel
  module Visitors
    class PostgreSQL < Arel::Visitors::ToSql
      alias_method :visit_Arel_Nodes_Contains, :visit_Arel_Nodes_InfixOperation
    end
  end
end

Finally I need a public API for this. You can query multiple document columns at once, and they will result in separate where queries.

module ActiveRecord::QueryMethods
  def contains(predicate)
    predicates = predicate.map do |column, predicate|
      column    = table[column]
      predicate = column.type_cast_for_database(predicate)
      predicate = Arel::Nodes.build_quoted(predicate)
      
      where Arel::Nodes::Contains.new(column, predicate)
    end

    return none if predicates.length == 0
    predicates.inject(:merge)
  end
end

That applies to scopes, but top-level helpers are delegated here:

module ActiveRecord::Querying
  delegate :contains, :to => :all
end

So let's say I have an Event with a body column containing a json document.

Event.contains(body: { shift: { user_id: 2 } })
SELECT "events".* FROM "events" WHERE "events"."body" @> '{"shift":{"user_id":2}}'

Or, perhaps we have a blog Post with a tags column containing an array of strings

Post.contains(tags: %w[subject-1 subject-3])
SELECT "posts".* FROM "posts" WHERE "posts"."tags" @> '{subject-1,subject-3}'
require 'arel/nodes/binary'
require 'arel/visitors/postgresql'
require 'active_record/querying'
require 'active_record/relation/query_methods'
module Arel
module Nodes
class Contains < Arel::Nodes::InfixOperation
def initialize(left, right)
super(:'@>', left, right)
end
end
end
end
module Arel
module Visitors
class PostgreSQL < Arel::Visitors::ToSql
alias_method :visit_Arel_Nodes_Contains, :visit_Arel_Nodes_InfixOperation
end
end
end
module ActiveRecord::QueryMethods
def contains(predicate)
predicates = predicate.map do |column, predicate|
column = table[column]
predicate = column.type_cast_for_database(predicate)
predicate = Arel::Nodes.build_quoted(predicate)
where Arel::Nodes::Contains.new(column, predicate)
end
return none if predicates.length == 0
predicates.inject(:merge)
end
end
module ActiveRecord::Querying
delegate :contains, :to => :all
end
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment