Skip to content

Latest commit

 

History

History
170 lines (126 loc) · 4.88 KB

querying.md

File metadata and controls

170 lines (126 loc) · 4.88 KB
Raw

Querying PostgreSQL datatypes

Arrays

&& - Array Overlap operator

PostgreSQL implements the && operator, known as the overlap operator, for arrays. The overlap operator returns t (true) when two arrays have one or more elements in common.

ARRAY[1,2,3] && ARRAY[4,5,6]
-- f

ARRAY[1,2,3] && ARRAY[3,5,6]
-- t

Postgres_ext extends the ActiveRecord::Relation.where method similar to the Rails 4.0 not clause. The easiest way to make a overlap query would be:

User.where.overlap(:nick_names => ['Bob', 'Fred'])

Postgres_ext defines overlap, an Arel predicate for the && operator. This is utilized by the where.overlap call above.

user_arel = User.arel_table

# Execute the query
User.where(user_arel[:tags].overlap(['one','two']))
# => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"tags\" && '{one,two}'

@> - Array Contains operator

PostgreSQL has a contains (@>) operator for querying whether all the elements of an array are within another.

ARRAY[1,2,3] @> ARRAY[3,4]
-- f

ARRAY[1,2,3] @> ARRAY[2,3]
-- t

Postgres_ext extends the ActiveRecord::Relation.where method by adding a contains method. To make a contains query, you can do:

User.where.contains(:nick_names => ['Bob', 'Fred'])

Postgres_ext overrides contains, an Arel predicate, to use the @> operator for arrays. This is utilized by the where.contains call above.

user_arel = User.arel_table

# Execute the query
User.where(user_arel[:tags].contains(['one','two']))
# => SELECT "users".* FROM "users" WHERE "users"."tags" @> '{"one","two"}'

ANY or ALL functions

When querying array columns, you have the ability to see if a predicate apply's to either any element in the array, or all elements of the array. The syntax for these predicates are slightly different then the normal where syntax in PostgreSQL. To see if an array contains the string 'test' in any location, you would write the following in SQL

SELECT *
FROM users
WHERE 'test' = ANY(users.tags)

Notice that the column is on the right hand side of the predicate, instead of the left, because we have to call the ANY function on that column.

Postgres_ext provides a ActiveRecord::Relation.where.any() method. The easiest way to make a ANY query would be:

User.where.any(:nick_names => 'Bob')

There is also an ActiveRecord::Relation.where.all() call as well. This method utilizes the following code to create the query:

We can generate the above query using Arel and generating the Node manually. We would use the following to accompish this:

user_arel = User.arel_table

any_tags_function = Arel::Nodes::NamedFunction.new('ANY', [user_arel[:tags]])
predicate = Arel::Nodes::Equality.new('test', any_tags_function)

# Execute the query
User.where(predicate)
#=> SELECT \"users\".* FROM \"users\" WHERE 'test' = ANY(\"users\".\"tags\")

The ALL version of this same predicate can be generated by swap 'ANY' for 'ALL' in the named function.

INET/CIDR Queries

PostgreSQL defines the <<, or contained within operator for INET and CIDR datatypes. The << operator returns t (true) if a INET or CIDR address is contained within the given subnet.

inet '192.168.1.6' << inet '10.0.0.0/24'
-- f

inet '192.168.1.6' << inet '192.168.1.0/24'
-- t

In addition to contained within, there is also:

  • <<= - Contained within or equals
  • >> - Contains
  • >>= - Contains or equals

Postgres_ext extends the ActiveRecord::Relation.where method similar to the Rails 4.0 not clause. The easiest way to make a overlap query would be:

User.where.contained_within(:ip => '192.168.1.1/24')
User.where.contained_within_or_equals(:ip => '192.168.1.1/24')
User.where.contains(:ip => '192.168.1.14')
User.where.contains_or_equals(:ip => '192.168.1.14')

Postgres_ext defines contained_within, an Arel predicate for the << operator. This is utilized by the methods above.

user_arel = User.arel_table

# Execute the query
User.where(user_arel[:ip_address].contained_within('127.0.0.1/24'))
# => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"ip_address\" << '127.0.0.1/24'
User.where(user_arel[:ip_address].contained_within_or_equals('127.0.0.1/24'))
# => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"ip_address\" <<= '127.0.0.1/24'
User.where(user_arel[:ip_address].contains('127.0.0.1'))
# => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"ip_address\" >> '127.0.0.1'
User.where(user_arel[:ip_address].contains_or_equals('127.0.0.1'))
# => SELECT \"users\".* FROM \"users\" WHERE \"users\".\"ip_address\" >>= '127.0.0.1'