ActiveRecord with Arel in a Composable Query Builder
By: Kyler Stole
Are you a car shopper but don’t know which car fits your needs? TrueCar’s Model Search can help narrow it down. It consists of a search results page (SRP) of vehicle models along with a slew of combinable filters. But what powers such a thing?? Well, Elasticsearch, because relational databases struggle with the aggregations to display counts for each filter (check out this post on the topic). But, initially it was implemented in Postgres using ActiveRecord interwoven with chunks of Arel to create a composable query builder!
The Postgres Model Search service was actually first put to use in Vehicle Rankings pages, which also filter down a list of vehicles but then rank the results using TrueCar’s proprietary scoring system. These pages remain powered by Postgres and the composable Model Search query service enables us to easily add new Rankings pages with different combinations of filters and sorting methods.
You’re almost not a full Rails user if you’re not working with ActiveRecord models to query a database. As the built-in Rails ORM framework, its query methods and implementation of associations between models are crucial to how most apps function. We build on an instance of ActiveRecord::Relation, which is a naturally composable object. Various query clauses can be added out-of-order and the relation can still generate valid SQL in the end. When we want to do something that ActiveRecord doesn’t know how to handle, we can often pass in a raw SQL string, but building the clause in Arel can make for a stronger, more flexible input.
If you’re new to Arel, you can check out these 6 Useful Arel Techniques.
Advanced Where Conditions
Probably, one of the most common uses of Arel in ActiveRecord is to build complex WHERE conditions without resorting to passing in a string.
AR’s hash syntax can handle basic equality and some greater than/less than conditions:
where(users: { name: 'Henry' }) # users.name = 'Henry'
where('users.name': 'Henry') # users.name = 'Henry'
where(created_at: 1.month.ago..) # users.created_at >= '2022–12–18 20:47:56.377437'
where(age: ...21) # users.age < 21
where(age: 35..56) # users.age BETWEEN 35 AND 56)
where(age: [45, 67]) # users.age IN (45, 67)That’s about it. If we want to go beyond those simple statements, we can try a string with variable replacement:
where('users.name ILIKE ?', '%vic')But better if we can keep it in Arel:
where(User.arel_table[:name].matches('%vic'))
# users.name ILIKE '%vic'We can also apply modifiers such as lowercasing the column:
where(User.arel_table[:name].lower.eq('mcdonald'))
# LOWER(users.name) = 'mcdonald'For Model Search, this comes in useful when we need to build conditions on data from multiple places. For example, when handling pricing, we always include destination charge in the MSRP, but the two values are stored in separate columns. With Arel, we can create a node that represents the combined value and use that for filtering by price.
total_msrp = Vehicle.arel_table[:msrp] + Vehicle.arel_table[:destination_charge]
where(total_msrp.between(params[:price])) # params[:price] = 11_000..19_000
# (vehicles.msrp + vehicles.destination_charge) BETWEEN 11000 AND 19000Advanced Select Statements
When writing pure Arel, the counterpart to the SELECT clause is the #project method.
User.arel_table.project(User.arel_table[:name])
# => SELECT users.name FROM usersJust like the #where method, AR’s #select method can take an Arel node instead of a string or symbol argument. This allows us to build much more complex selects in our AR relations.
In Model Search, we look up the types of some values stored in string columns and use Arel to type cast them in the SELECT:
Arel::Nodes::NamedFunction.new('CAST', [specs[:value].as(type.upcase)])
# => CAST(specs.value AS INTEGER)We also use #as on an Arel node to select columns with an alias that we can use for sorting Vehicle Rankings results.
query.select(sort.as(sort_key)).order(sort_key => order)Repeated, Complex Joins
Most of the complexity in Model Search comes from handling vehicle features and specifications data. Values for a variety of data points are stored in the same table (we’ll call this specs), so composing a query that correctly filters based on multiple specifications is not trivial.
You could, for example, search for SUVs with a surround view camera, automatic braking, and 8 or more seats. Filtering by just one of those specifications is probably a job that ActiveRecord could handle:
Vehicle.joins(:specs).where(specs: { name: 'Seats', value: 8.. })
# FROM vehicles JOIN specs ON specs.vehicle_id = vehicles.id
# WHERE specs.name = 'Seats' AND specs.value >= 8Joining with an AR association doesn’t provide an alias, so if we try to add conditions for the other specifications, we won’t get the results we hoped for.
Vehicle
.joins(:specs).where(specs: { name: 'Seats', value: 8.. })
.joins(:specs).where(specs: { name: 'Automatic Braking', value: true})
.joins(:specs).where(specs: { name: 'Surround View Camera', value: true})
# FROM vehicles JOIN specs ON specs.vehicle_id = vehicles.id
# WHERE specs.name = 'Seats' AND specs.value >= 8
# AND specs.name = 'Automatic Braking' and specs.value = 't'
# AND specs.name = 'Surround View Camera' and specs.value = 't'Here is where we can use Arel to overcome the limitations of AR associations. We will write the join conditions manually, and this time, we’ll give each table a dynamic alias.
def join_specs(query, spec_name)
spec_key = spec_name.parameterize(separator: '_')
vehicles_table = Vehicle.arel_table
specs_table = Spec.arel_table.alias(spec_key)
conditions = [
specs_table[:vehicle_id].eq(vehicles_table[:id]),
specs_table[name].eq(spec_name),
specs_table[:disabled].eq(false)
].reduce(:and)
query.joins(
vehicles_table.join(specs_table).on(conditions).join_sources
)
endGreat! Now whenever we want to filter vehicles by a different specification, we can create a new join and we’ll have an alias to refer to the table.
params = { 'Seats' => 8..,
'Automatic Braking' => true,
'Surround View Camera' => true }
params.reduce(query) do |q, (spec_name, value)|
spec_key = spec_name.parameterize(separator: '_')
join_specs(q, spec_name).where(spec_key: { value: value })
end
# FROM vehicles
# JOIN specs seats ON seats.vehicle_id = vehicles.id AND seats.name = 'Seats' AND seats.disabled = FALSE
# JOIN specs automatic_braking ON automatic_braking.vehicle_id = vehicles.id AND automatic_braking.name = 'Automatic Braking' AND automatic_braking.disabled = FALSE
# JOIN specs surround_view_camera ON surround_view_camera.vehicle_id = vehicles.id AND surround_view_camera.name = 'Surround View Camera' AND surround_view_camera.disabled = FALSE
# WHERE seats.value >= 8 AND automatic_braking.value = 't' AND surround_view_camera.value = 't'With all of the join conditions specified in the JOIN clause rather than the WHERE, we could easily use an outer join if we needed to make a specification filter optional. Arel’s #join method takes a second argument where we can specify the node type Nodes::OuterJoin (the default is Nodes::InnerJoin).
Stacking Subqueries
As cumbersome as it can be to write complex SQL, it ultimately excels in its flexibility. Arel, similarly, being so extensible, is flexible to structure a query in ways beyond AR’s capabilities. That flexibility generally comes at the cost of convenience, so it can be helpful to use Arel only where necessary and not abandon ActiveRecord entirely.
In Model Search, we have the concept of a “model family”: a collection of vehicles with different model names that have only slight differences. We use it to collapse similar vehicles into a single result with Postgres’ DISTINCT ON selecting at most one result per model family. DISTINCT ON requires rows to be sorted according to the distinct column, so if we want to sort results by a specific metric (e.g. price), we have to isolate the model family layer to a subquery wedged between layers that sort results how we actually want. At this point, a mix of ActiveRecord, Arel, and a touch of raw SQL can get the job done.
family_vehicles = VehicleCollection.joins(:collection)
.where(collections: { type: 'Family', active: true })
.arel
.distinct_on(Collection.arel_table[:vehicle_id])
.as('family_vehicles')
coalesce = Arel::Nodes::NamedFunction.new(
'COALESCE',
[model_family_styles[:collection_id], styles[:model_id]]
)
Vehicle.from(query, 'vehicles').joins(
Arel::Table.new('vehicles').outer_join(family_vehicles)
.on(family_vehicles[:vehicle_id].eq(vehicles[:id]))
.join_sources
)
.select("DISTINCT ON (#{coalesce.to_sql}) vehicles.*")
.order(coalesce)This uses a few tricks to combine frameworks. The #arel method lets us grab the Arel AST from our AR relation where we added a join with an association. We can then call the Arel method #distinct_on on the Arel node and give it an alias with #as. Since not every vehicle is assigned a model family and we don’t want to lose records that don’t have one, we use NamedFunction to create a COALESCE statement and later #outer_join to create a LEFT JOIN between the two subqueries. Finally, using #to_sql on our Arel coalesce node allows us to interpolate it into a raw SQL string for a selection.
The result is still an ActiveRecord::Relation, but it produces a complex, multilevel query that fits our needs.
SELECT
DISTINCT ON (COALESCE(family_vehicles.collection_id, vehicles.id))
vehicles.*
FROM (SELECT ...) vehicles
LEFT JOIN (
SELECT DISTINCT ON (vehicle_collections.vehicle_id) vehicle_collections.*
FROM vehicle_collections
JOIN collections ON collections.id = vehicle_collections.collection_id
WHERE collections.type = 'Family' AND collections.active = TRUE
) family_vehicles ON family_vehicles.vehicle_id = vehicles.id
ORDER BY COALESCE(family_vehicles.collection_id, vehicles.id)Tying it Together
If you count the example Arel usages, you may think it falls a bit short of the logic needed for a composable query builder, but that’s really about as much Arel as we need. Arel shines brightest when replacing a string for a simple node (wrapping with a function like CAST or COALESCE, applying an alias, etc.) or building up complex pieces like we did for model families; the rest relies primarily on composable ActiveRecord methods.
We are hiring! If you love solving problems, please apply here. We would love to have you join us!
