# Querying PostgreSQL datatypes

  * [Arrays](#arrays)
  * [INET/CIDR](#inetcidr-queries)

## Arrays

  * [&& - Array Overlap operator](#---array-overlap-operator)
  * [ANY or ALL functions](#any-or-all-functions)

### && - 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.

```sql
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:

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

Postgres\_ext defines `array_overlap`, an [Arel](https://github.com/rails/arel)
predicate for the `&&` operator. This is utilized by the `where.overlap`
call above.

```ruby
user_arel = User.arel_table

# Execute the query
User.where(user_arel[:tags].array_overlap(['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

```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:

```ruby
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](https://github.com/rails/arel)
and generating the Node manually. We would use the following to
accompish this:

```ruby
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. 

```sql
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:

```ruby
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](https://github.com/rails/arel)
predicate for the `<<` operator.  This is utilized by the
methods above.

```ruby
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'
```