Ransack: PostgreSQL Array searches

[seanlinsley]

The postgres_ext gem defines Arel nodes that you can normally use like this:

User.where.contains roles: ['foo']
# or:
User.where User.arel_table[:roles].contains ['foo']

With a bit of hacking, you can get Ransack to recognize these.

# PostgreSQL Array predicates
Ransack.configure do |config|
  %w[
    contained_within
    contained_within_or_equals
    contains
    contains_or_equals
    overlap
  ].each do |p|
    config.add_predicate p, arel_predicate: p, wants_array: true
  end
end

Which gets us this:

User.search(roles_contains: [3,4]).result.to_sql

SELECT "users".* FROM "users"  WHERE ("users"."roles" @> '{"3","4"}')

Which is okay I suppose... except the equals predicate would have to have the wants_array setting set to true for it to work with Postgres arrays. And of course if it's set to true then it breaks for non-array objects.

I haven't tried delving into what's necessary to query hstore columns. I just figured I'd post my progress so far here to see if anyone had any feedback.

[seanlinsley]

Added problem: this blows away the existing contains predicate normally used for string comparison

[seanlinsley]

It seems like two changes would be necessary for contains to work for both arrays and strings:

1. wants_array would need to be conditional on the data type
2. Ransack's custom contains predicate should delegate to the Arel node provided by postgres_ext if the data type is an array

Number 2 could be changed to: a Ransack predicate needs the option to be specific to a given data type. In this case you'd have two contains predicates; one for strings, one for arrays.

[rilian]

thank you, works like a charm :+1:

[seanlinsley]

I'd love your thoughts on this, @radar & @jonatack

[jonatack]

Pretty cool! I'd like to give it a try in an app when I get the chance.

[seanlinsley]

:cat: Do have any thoughts on how this could be implemented?

[shir]

@seanlinsley how about rename predicates added to ransack:

Ransack.configure do |config|
  {
    contained_within_array: :contained_within,
    contained_within_or_equals_array: :contained_within_or_equals,
    contains_array: :contains,
    contains_or_equals_array: :contains_or_equals,
    overlap_array: :overlap
  }.each do |rp, ap|
    config.add_predicate rp, arel_predicate: ap, wants_array: true
  end
end

In this case it will not conflict with builtin. But still doesn't know how to fix equals

[seanlinsley]

That's certainly a more usable version of my initial monkeypatch, but I'd really like to be able to use the same predicate for strings and arrays.

[anthonylebrun]

+1 for this feature

Oh, and thanks @seanlinsley for that snippet.

[hmaack]

:+1: lets move it to the wiki

[seanlinsley]

In my own app I'm actually using this, which is @shir's example except moving array in the name to the beginning:

# https://github.com/activerecord-hackery/ransack/issues/321
Ransack.configure do |config|
  { array_contained_within:           :contained_within,
    array_contained_within_or_equals: :contained_within_or_equals,
    array_contains:                   :contains,
    array_contains_or_equals:         :contains_or_equals,
    array_overlap:                    :overlap
  }.each do |rp, ap|
    config.add_predicate rp, arel_predicate: ap, wants_array: true
  end
end

[FX-HAO]

How to achieve this without postgres_ext because it doesn't support Rails 5.

[seanfcarroll]

Re-opened, needs v5 support.

[seanlinsley]

I pinged the primary contributor to postgres_ext to see if it's possible to revive the gem: https://twitter.com/seanlinsley/status/958737183640137728

Oh I guess I could've pinged him here on GitHub instead: @danmcclain

[seanlinsley]

I've been using the now-closed but still perfectly operational https://github.com/DavyJonesLocker/postgres_ext/pull/187

gem 'postgres_ext', github: 'shaneog/postgres_ext', branch: 'rails-5', ref: '88d46f6c14'

[FX-HAO]

also @edpaget

[MrJoy]

Is using postgres_ext actually necessary/wise now that Rails 5 has support for array columns directly?

I.E. this is a thing that totally works in Rails 5:

SomeModel.where("array_column @> ARRAY[?]::varchar[]", ["value1", "value2"])

[seanlinsley]

@MrJoy I'd personally hate to use that syntax in my own application. For me that doesn't qualify as ActiveRecord supporting it, instead simply the SQL escaper supports it.

Also how would we use that with Ransack?

[MrJoy]

Not sure if there's an easy/elegant solution to using it with Ransack, unfortunately. I wound up slicing out pieces of that fork and just vendoring them into my initializer for the time being: I'm only interested in the handling of array columns, so I only needed a subset of the functionality and this let me keep it compartmentalized, associated with its intended consumer, etc. Not thrilled with where it landed, but it felt a little less messy than bringing in a dead-ended, GH-hosted dependency.

[rjsnh1522]

how to match the exact values in the arrays.

I have to perform a search on a database using ransack. A few columns in the database have data stored in serialised arrays. I want to match the exact data stored in the arrays with data sent by user to perform search (users' data are also arrays). For example, in database, one column has data as (c1, c2 are test cases):

c1.column_data = [1, 2, 3, 4, 5]
c2.column_data = []
User searches for data (t1, t2, t3 are test cases):

t1.user_data = [1]
t2.user_data = [1, 3]
t3.user_data = [1, 2, 3, 4, 5]
t4.user_data = []
For case c1 with t1, t2, t4, it should return no match found.
With t3, it should result with match found.
For case c2 with t1, t2, t3, it should return no match found.
With t4, it return `match found.

[seanfcarroll]

Moved to Wiki

[wvengen]

What if we could define predicates with more arel power (like ransackers can)? E.g. something like

# contains_array turns into `WHERE <value> = ANY(<array_column>)`
config.add_predicate :contains_array, wants_array: true do |arel_node, value|
  Arel::Nodes::Equality.new(
    Arel::Nodes::Quoted.new(value),
    Arel::Nodes::NamedFunction.new('ANY', [arel_node])
  )
end

I'm not sure if I'm fully understanding wants_array, but I guess you'll get the idea.

This would allow for much more flexibility (without having to patch arel like postgres_ext did), and solve this issue as well.

[wvengen]

As a workaround, I can get the previous idea to work right now as follows (for something like contains_array, though this version just accepts a single value, not an array):

module Arel
  module Predications
    def contains(other)
      Nodes::Equality.new(Nodes.build_quoted(other, self), Nodes::NamedFunction.new('ANY', [self]))
    end
  end
end

Ransack.configure do |config|
  config.add_predicate :contains_array, arel_predicate: :contains
end

[seanlinsley]

On the subject of the postgres_ext gem, while it's not actively maintained, the original author reclaimed Rubygems ownership and created a new repo: https://github.com/danmcclain/postgres_ext

I have an unfinished PR to support Rails 5.2 if anyone would like to pair on it: https://github.com/danmcclain/postgres_ext/pull/2

That said, it's definitely not ideal to be patching Arel. It'd be better to have something like @wvengen's idea, or to have the query logic upstreamed into ActiveRecord.

[ringofhealth]

I am also stuck on the same issue, but I found this gem

https://github.com/GeorgeKaraszi/ActiveRecordExtended

Which is an continued work of pg_ext that adds postgres support to AR. @seanlinsley, will this work?

[vanboom]

This issue was closed by adding a link to it in the Wiki?