What if .select() and .pluck() could automatically create their own JOINs?

rubyonrails-talk
1

@etravassos had mentioned wanting to find Arel alias names, and it reminded me of something I’ve been keeping under my hat for awhile… had put a thing in The Brick about a year ago that greatly simplifies how we can query ActiveRecord. Have now battle-tested it enough that it’s time to make an announcement.

Imagine you have these three tables:

Screenshot 2024-03-22 at 08.40.45
Screenshot 2024-03-22 at 08.40.45828×658 32.8 KB

Say you would like to get a list of flights and include some detail about their departure and arrival airports. For both departures and arrivals, you want to show the airport code and city name. Because this requires these 4 JOINs:

Flight.joins(departure: :city, arrival: :city)

and also since you can’t easily predict the alias names that will get chosen for each of those four, normally you would need to first see what the resulting SQL would be. And then from this you can find the alias (correlation) names for airports and cities.

After investigating this resulting SQL, you would discover that the first time airports is JOINed in, it is called just that, and the second time, it gets called arrival_flights for whatever reason (even though it really is the airports table, and just JOINed another time). Similar for cities – first time when JOINed for departure airport it is just cities, and the second time JOINed to find the arrival city, it gets called cities_airports. Knowing this, you could write this final query:

3.3.0 :004 > Flight.joins(departure: :city, arrival: :city)
                   .pluck(:dt, 'airports.code AS departure_code',
                               'cities.name AS departure_city',
                               'arrivals_flights.code AS arrival_code',
                               'cities_airports.name AS arrival_city')
 => 
[[Wed, 27 Mar 2024, "LHR", "London", "BCN", "Barcelona"],
 [Fri, 29 Mar 2024, "AMS", "Amsterdam", "CDG", "Paris"]] 
3.3.0 :005 >

This is all a bit brittle. What if (God forbid) Arel were to change the way that it assigns correlation names? I mean, it probably won’t because so many people have now hard-coded these alias names in place in this kind of way, so there would be a full-on uprising if it started working differently. Then everyone would have to change those Arel-ish names around that they’d put into the .select() and .where() and .pluck() parts of their AR queries. Balderdash!

So imagine if you could write something like this instead, not using any of those weird hard-coded correlation names. Not even having to separately say what associations you’re JOINing across…

Flight.brick_pluck(:dt, 'departure.code', 'departure.city.name',
                        'arrival.code', 'arrival.city.name')

… almost looks like pseudocode, doesn’t it? But it’s real! Just put The Brick gem into your project, and you too can enjoy this kind of simpler querying. Across any level of complexity of belongs_to and has_many that you want. Throw it the works. See if it can take it. You can use both brick_select and brick_pluck – they both work in this kind of way. And there’s also brick_where that I’ll document a bit better later. But for now it’s 2:30am here in UK, so I need to rest.

Welcome to the magic of The Brick!

2 Likes
2

Pretty cool man, thank you for sharing!

1 Like
3

Flight.brick_pluck(:dt, 'departure.code', 'arrival.code')

I assume what happens on the high-lever: The Brick assumes the prefix before . is a relation name, so it joins :departure and :arrival and names (aliases) them accordingly.

I generally like the idea, the number of times it bothered me to depend on Arel’s aliases is not zero.

AFAIU pluck is supposed to work if I do Flight.join(stuff).pluck(). Does brick_pluck inspect current joins? Does it add new ones or change the existing ones so they have “better” aliases?

There might be some obscure join use cases where one would want dynamically generated aliases. I guess with a separate method (.brick_pluck), you can throw an exception and not support .join.brick_pluck. But adopting it in pluck is a whole different story.

4

@pawlik – you’d be surprised! It inspects existing JOINs (if any exist), and only adds any extra JOINs that are missing. There’s quite a bit going on under the hood to make this happen.

If you wanted to start with some INNER and some LEFT OUTER joins then you can do that, and ask Brick for the rest with brick_pluck or brick_select.

It goes across both has_many and belongs_to associations, so you can do something like this …

Category.brick_pluck('products.order_details.order.customer.company_name')

… and it understands what you mean, and JOINs 5 tables together, from categories to products to order_details to orders to customers in order to finally get the company_name info!

3.3.0 :008 > Category.brick_pluck(:category_name, 'products.order_details.order.customer.company_name')
  Category Pluck (4.5ms)  SELECT "categories"."category_name",
"customers"."company_name" AS "products_order_details_order_customer_company_name"
FROM "categories"
LEFT OUTER JOIN "products" ON "products"."category_id" = "categories"."id"
LEFT OUTER JOIN "order_details" ON "order_details"."product_id" = "products"."id"
LEFT OUTER JOIN "orders" ON "orders"."id" = "order_details"."order_id"
LEFT OUTER JOIN "customers" ON "customers"."id" = "orders"."customer_id"
 => 
[["Dairy Products", "Vins et alcools Chevalier"],
 ["Grains/Cereals", "Vins et alcools Chevalier"],
 ["Dairy Products", "Vins et alcools Chevalier"],
 ["Produce", "Toms Spezialitäten"],
 ["Produce", "Toms Spezialitäten"],
 ["Condiments", "Hanari Carnes"],
 ["Produce", "Hanari Carnes"],
...
1 Like
5

Sounds awesome, thanks for clarifying @Lorin_Thwaits

1 Like