FindByAssociation

This module extends the behavior of ActiveRecord::Base to support using association names directly in conditions hashes for finders. Because this patches sanitize_sql_hashforconditions] it “extends elegantly to private the means of combination” (to quote Hal Abelson) meaning, among other benefits, that conditions hashes may therefore be nested as deep as your database engine’s support for nested subqueries allows. One of the greatest benefits of an Object-Relational-Mapping system like ActiveRecord, is the ability to express the associations between objects. We’ve all written code like this before: class Category < ActiveRecord::Base has_many :posts end class Post < ActiveRecord::Base belongs_to :author, :class_name => ‘User’, :foreign_key => :author_id belongs_to :category end class User < ActiveRecord::Base has_many :posts, :foreign_key => :author_id end Assume you have two instance variables: @category and @user. If you wanted to find all the Posts for the @user, it would be as simple as referencing @user.posts. If you wanted to find all the Posts for @category, you reference @category.posts. But how do you get an intersection of the two collections? The find_by_association plugin is designed to make it easy to reference associations in finder conditions hashes and dynamic finders. For example, to get all the Posts for a specific category and author, you can use a dynamic finder, a conditions hash, or a combination: Post.find(:all, :conditions => {:author => @author, :category => @category}) # <= hash Post.find_all_by_category_and_author(@category, @author) # <= dynamic finder Post.find_all_by_category(@category, :conditions => {:author => @author}) # <= combo Pretty neat trick, but it gets a lot neater. What if you wanted to find all Posts where the author’s first name was ‘Joe’? find_by_association gives us the ability to include references to the associated objects’ attributes: Post.find(:all, :conditions => {:author => {:first_name => ’Joe’}}) # <= hash Post.find_all_by_author_having_first_name(‘Joe’) # <= dynamic finder Notice in the Dynamic Finder example, the keyword having expresses that we are looking for at the first_name attribute on the referenced author object. Cool! We can even nest our conditions further — Lets say we wanted to find all the Categories that Users with first_name of ‘Joe’ have written Posts about: Category.find(:all, :conditions => { :posts => { :author => {:first_name => ’Joe’}}}) # <= hash Category.find_all_by_posts_having_author_having_first_name(‘Joe’) # <= dynamic finder Notice that we referenced a has_many association, posts, in the same way we referenced the belongs_to association author. find_by_association can handle all the standard association macros: has_many, has_many :through, has_and_belongs_to_many, belongs_to, and has_one. The only place where you run into some trickiness, is trying to nest conditions that reference a belongs_to association that with a :polymorphic => true option. You can do so, but you have to provide a foreign_type value. For example: class Authorship < ActiveRecord::Base belongs_to :author, :class_name => ‘User’, :foreign_key => :author_id belongs_to :work, :polymorphic => true end class Article < ActiveRecord::Base has_many :authorships, :as => :work has_many :authors, :through => :authorships end class Book < ActiveRecord::Base has_many :authorships, :as => :work has_many :authors, :through => :authorships end Notice I must provide the :work_type key to the conditions to inform find_by_association what model to include in the subquery to find a title attribute. Authorship.find :all, :conditions => { :work => {:title => ‘Writing Cool Rails Plugins’}, :work_type => ‘Book’ } Below is a schema, with some model definitions, and some additional example uses, which I will leave here as documentation, until such time as I am able to provide actual documentation. Please forgive me. Migration/Schema.rb table definitions: create_table :customers do |t| t.string :name end create_table :products do |t| t.string :name end create_table :orders do |t| t.references :customer end create_table :order_items do |t| t.references :product, :order t.integer :quantity end Define the Model Classes: class Customer < ActiveRecord::Base has_many :orders end class Order < ActiveRecord::Base belongs_to :customer has_many :items, :class_name => ‘OrderItem’ has_many :products, :through => :items end class OrderItem < ActiveRecord::Base belongs_to :order belongs_to :product end class Product < ActiveRecord::Base has_many :order_items has_many :orders, :through => :order_items end Create a dataset: banana = Product.create(:name => “Electric Banana”) flower = Product.create(:name => “Squirting Flower”) wocka = Product.create(:name => “Wocka-Wocka-Wocka”) fozzy = Customer.create(:name => “Fozzy”) order1 = fozzy.orders.create order1.items.create :product => banana, :quantity => 1 order1.items.create :product => wocka, :quantity => 3 order2 = fozzy.orders.create order2.items.create :product => wocka, :quantity => 5 order2.items.create :product => flower, :quantity => 1 Lets “Find-by-association”! Find all of the Customers who ordered a Product named “Squirting Flower”… Customer.find(:all, :conditions => {:orders => {:products => {:name => “Squirting Flower”}}}) # <= hash Customer.find_all_by_orders_having_products_having_name(“Squirting Flower”) # <= dynamic finder => [#<Customer id: 1, name: "Fozzy">] Whoa— did you see what happened there? We got nested a reference to the :name column in a has_many :through inside a has_many association on Customer. And we delegated all the work to the database in a single statement! Yee-haw. What’s the SQL look like? (indented for readability.) SELECT * FROM customers WHERE ( customers.“id” IN ( SELECT customer_id FROM orders WHERE ( orders.“id” IN ( SELECT order_id FROM order_items WHERE ( order_items.“product_id” IN ( SELECT id FROM products WHERE (products.“name” = ‘Squirting Flower’))))))) You think that’s nice, you should see what happens when you combine FindByAssociation with the rest of Entrails’ ActiveRecord extensions, like… [[BetterConditions]] The subqueries generated need to be optimized to reference the id column outside the subquery so that the temporary tables that many db engines use to produce the subset references are significantly reduced. coming soon —Brendan 2008-03-13T10:31:00-07:00 4318 findbyassociation

3563

FindByAssociation

2008-03-15T15:45:41-07:00

578