Alex's Slip-box

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

ActiveRecord Querying has_many Associations

: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

# from method (sub-querying)

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

Search Results