Skip to content

Latest commit

 

History

History
127 lines (107 loc) · 3.77 KB

20180417025641-activerecord_querying_has_many_associations.org

File metadata and controls

127 lines (107 loc) · 3.77 KB

ActiveRecord Querying

See also ActiveRecord query objects

ActiveRecord Querying has_many Associations

joining a associations

Location.joins(:people)
Location.joins(people: :roles)

merging scopes

Place.select("places.*, communes.*")
     .joins(:commune)
     .merge(Commune.where(:id => [1,2,3])

Joins with Where

Location.joins(people: :roles).where(roles: { billable: true } )

Joins with Distinct

Location.joins(people: :roles).where(roles: { billable: true } ).distinct
SELECT DISTINCT * from locations
INNER JOIN people on people.location_id = location.id
INNER JOIN roles on role.id = people.role_id
WHERE role.billable = TRUE

Where subquery

Compose a SQL string and interpolate it into the where clause.

Asteroid.where('period > (:avg)', avg: Asteroid.select('avg(period)'))

See also https://pganalyze.com/blog/active-record-subqueries-rails#the-where-subquery

from method (sub-querying)

See also https://pganalyze.com/blog/active-record-subqueries-rails#the-from-subquery

Using distinct and order may require you to pay attention to the order of operations of the query. For example, select the distinct records, then join another table and order by a column on the joined table. You’ll get this error:

PG::InvalidColumnReference: ERROR: for SELECT
DISTINCT, ORDER BY expressions must appear in select list from can
create a subquery that is executed first.
  • Put the distinct in the subquery.
  • Scopes can be used as the sub query with a name provided
# 2nd arg to `from` is the subquery alias which needs to match what the
# outer query will generate.
Location.from(Location.billable, :locations)
        .joins(:region)
        .merge(Region.order(:name))
        .merge(Location.order(:name))

It will create this SQL:

SELECT * FROM (
  SELECT DISCTINCT * FROM locations
  INNER JOIN people on people.location_id = location.id
  INNER JOIN roles on people.role_id = role.id
  WHERE role.billable = TRUE
) locations
INNER JOIN regions on region.id = location.region_id
ORDER BY region.name ASC, location.name ASC

CTE / with

See also https://api.rubyonrails.org/classes/ActiveRecord/QueryMethods.html#method-i-with

Basic Example:

Asteroid.with(recently_discovered: Asteroid.where.not(rarity: 0).where(created_at: 30.days.ago..))
        .from('recently_rated_discovered AS asteroids')
        .count

Batches

There’s a few ways of doing batch operations, here’s several: