Alex's Slip-box

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

ActiveRecord Querying

Created on April 17, 2018

Updated on October 20, 2023

Tags: rails, ruby

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

# 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
Location.from(Location.billable, :locations).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

Use any model .with 

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

Search Results