Rails render XML of a INNER JOIN

Question

I'm using 2 joined models

class Product < ActiveRecord::Base
  has_and_belongs_to_many :providers
end
class Provider < ActiveRecord::Base
  has_and_belongs_to_many :products
end

and my controller looks like this

class ProductsController < ApplicationController
    @products = Product.find( 
      :all, 
      :joins => :providers, 
      :select => "providers.id, providers.title, products.id, products.title, products.price", 
      :limit => 10)
    respond_to do |format|
      format.xml  { render :xml => @products }
      format.json { render :json => @products }
    end
  end
end

The @products is not rendered as expected. Only columns of the Product model are shown in the XML file. I tried changing format.xml line to

format.xml  { render :xml => @products.to_xml( :include => :providers) }

but this is not what I want. As you can se my SQL queries for 5 columns

SELECT providers.id, providers.title, products.id, products.title, products.price 
FROM `products` 
INNER JOIN `products_providers` ON `products_providers`.product_id = `products`.id 
INNER JOIN `providers` ON `providers`.id = `products_providers`.provider_id 
LIMIT 10

but in my XML only 3 are shown. The method to_xml also generates some extran SQL requests and I don't want that.

Can someone provide me an information about how to tell rails to render all my SQL fields? I want the code to be optimized as well.

The ideal XML/JSON design would be

<products type="array">
<product>
  <id type="integer">1</id>
  <price type="decimal">9.99</price>
  <title type="string">Sanke Rolex</title>
  <provider>
    <id type="string">1</id>
    <title type="string"></title>
  </provider>
</product>
</products>

THX!

Answer 1

In cases where I want a very specific output, I use a Builder template instead of the render :xml shortcut. It's quite simple.

Answer 2

I don't understand why you're limiting yourself to certain columns in the :select parameter when you clearly said you want XML output to contain all the attributes.

The most optimized code would be this:

@products = Product.all(:include => :providers, :limit => 10)
respond_to do |format|
  format.xml  { render :xml => @products.to_xml(:include => :providers) }
  format.json { render :json => @products.to_json(:include => :providers) }
end

I'm using :include instead of :joins in the finder, meaning AR will use 2 SQL queries to fetch first products then providers, which is faster with larger tables than a join.

To exclude some private columns from showing in the XML output, use :except

@products.to_xml(
  :except => [:price],
  :include => { :providers => {:except => [:title]} }
)

You almost always want this, since every model contains information not to be publicly exposed.