Skip to content

Latest commit

 

History

History
240 lines (179 loc) · 6.24 KB

querying.md

File metadata and controls

240 lines (179 loc) · 6.24 KB
Raw

Common Table Expressions (CTEs)

Postgres_ext adds CTE expression support to ActiveRecord via two methods:

with

We can add CTEs to queries by chaining #with off a relation. Relation#with accepts a hash, and will convert Relations to the proper SQL in the CTE.

Let's expand a #with call to it's resulting SQL code:

Score.with(my_games: Game.where(id: 1)).joins('JOIN my_games ON scores.game_id = my_games.id')

The following will be generated when that relation is evaluated:

WITH my_games AS (
SELECT games.*
FROM games
WHERE games.id = 1
)
SELECT *
FROM scores
JOIN my_games
ON scores.games_id = my_games.id

You can also do a recursive with:

Graph.with.recursive(search_graph:
  "  SELECT g.id, g.link, g.data, 1 AS depth
     FROM graph g
   UNION ALL
     SELECT g.id, g.link, g.data, sg.depth + 1
     FROM graph g, search_graph sg
     WHERE g.id = sg.link").from(:search_graph)

from_cte

Model.from_cte is similiar to Model.find_by_sql, taking the CTE passed in, but allowing you to chain off of it further, instead of just retrieving the results.

Take the following ActiveRecord call:

Score.from_cte('scores_for_game', Score.where(game_id: 1)).where(user_id: 1)

The following SQL will be called:

WITH scores_for_game AS (
SELECT *
FROM scores
WHERE game_id = 1
)
SELECT *
FROM scores_for_game
WHERE scores_for_game.user_id = 1

And will be converted to Score objects

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

# Execute the query
User.where(user_arel[:tags].any('test'))
#=> SELECT \"users\".* FROM \"users\" WHERE 'test' = ANY(\"users\".\"tags\")

The ALL version of this same predicate can be generated by swapping #any() for #all().

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'