title: Eager loading associations with complex query
body: <p>Rails 3.2.13 with PostgreSQL</p>
  
  <p>We are trying to optimize the below model code, which retrieves a set of e-commerce Orders from the database, based on user defined filtering criteria.</p>
  
  <p>The user can select to filter orders based on Order Status, Payment Status &amp; Shipping Status. They can set these to either be <code>==</code> or <code>&lt;&gt;</code> a particular value for each.</p>
  
  <p>After this we filter out any digital orders then paginate the results.</p>
  
  <p>This all works well, except that the Order has many associations, such as products, addresses, payments etc. This causes N+1 queries and makes the process extremely slow.</p>
  
  <p>Order.rb:</p>
  
  <pre><code>belongs_to :shop
  has_many :shipping_lines , :dependent =&gt; :destroy
  has_many :line_items , :dependent =&gt; :destroy
  has_many :taxlines , :dependent =&gt; :destroy
  has_many :fulfillments , :dependent =&gt; :destroy
  has_many :notes , :dependent =&gt; :destroy
  has_many :discounts , :dependent =&gt; :destroy
  has_one :billing_address , :dependent =&gt; :destroy
  has_one :shipping_address , :dependent =&gt; :destroy
  has_one :payment_detail, :dependent =&gt; :destroy
  has_many :order_histories, :dependent =&gt; :destroy
  </code></pre>
  
  <p>Any ideas on how we can include the associations eager loaded in the below query?</p>
  
  <p>Example Input: The user could want to see Any (Open or Closed), Paid orders that are not yet Shipped</p>
  
  <pre><code>Order Status == 'open'
  
  Payment Status == 'any'
  
  Shipping Status &lt;&gt; 'shipped'
  </code></pre>
  
  <p>The shop is identified by the subdomain</p>
  
  <pre><code>def by_filter(subdomain, filter,page)
    shop_condition = "shop_id = #{subdomain.shops[0].id}"
    if filter.name != "All"
      if filter.order_status == "any"
        status_condition =  "status in (?)"
        order_status = Filter::ORDER_STATUS.values - ["any"]
      else
        status_condition =  "status #{Filter::STATUS_OPERATORS_MAPPING[filter.order_status_operator]} ?"
        order_status = filter.order_status
      end
  
      if filter.payment_status == "any"
        payment_status_condition =  "payment_status in (?)"
        payment_status = Filter::PAYMENT_STATUS.values - ["any"]
      else
        payment_status_condition =  "payment_status #{Filter::STATUS_OPERATORS_MAPPING[filter.payment_status_operator]} ?"
        payment_status = filter.payment_status
      end
  
      if filter.fulfillment_status == "any"
        fulfillment_status_condition =  "fulfillment_status in (?)"
        fulfillment_status = Filter::FULFILLMENT_STATUS.values - ["any"]
      else
        fulfillment_status_condition = "fulfillment_status #{Filter::STATUS_OPERATORS_MAPPING[filter.fulfillment_status_operator]} ?"
        fulfillment_status = filter.fulfillment_status
      end
  
      conditions = "#{status_condition} AND #{payment_status_condition} AND #{fulfillment_status_condition} AND #{shop_condition} "
    else
      conditions = shop_condition
    end
  
  
    orders = self.where([conditions, order_status, payment_status, fulfillment_status]).order("order_created_at #{filter.sort_by}")
  
  
    if filter.digital_orders
      orders_arr = ((orders.all.collect(&amp;:shipping_address) - [nil]).collect(&amp;:order)).flatten
      orders = Kaminari.paginate_array(orders_arr).page(page).per(filter.show)
    else
      orders = orders.page(page).per(filter.show)
    end
    return orders
  end
  </code></pre>
  
  <p>The SQL Query Generated looks like this:</p>
  
  <pre><code>SELECT "orders".* FROM "orders" WHERE (status = 'open' AND payment_status in ('authorized', 'pending', 'paid', 'partially_paid', 'partially_refunded', 'refunded', 'voided') AND fulfillment_status &lt;&gt; 'shipped' AND shop_id = 58 ) ORDER BY order_created_at DESC
  </code></pre>
  
  <p>We have tried to do the regular .includes but this seems to have no effect. For example:</p>
  
  <pre><code>orders = self.includes(:shipping_address).where([conditions, order_status, payment_status, fulfillment_status]).order("order_created_at #{filter.sort_by}")
  </code></pre>