Alex's Slip-box

These are my org-mode notes in sort of Zettelkasten style

ActiveRecord Querying

Created on April 17, 2018

Updated on August 19, 2024

Tags: rails, ruby

:ID: 28FC6AAD-4491-4DA5-BB3A-796E9EECD235

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:

Search Results