joining two tables in rails 3

Question

i have problem with joining two tables and i am so confused, so i made new project as this example: http://guides.rubyonrails.org/association_basics.html#the-has_many-association i have two tables customers and orders.

My models

class Customer < ActiveRecord::Base
  has_many :orders
  attr_accessible :id, :name
end

class Order < ActiveRecord::Base
  belongs_to :customer
  attr_accessible :id, :count, :customer_id
end

In migration orders table, i have reference to customers table realized with:

t.references :customer

I fill tables with some sample data and run this sql query which is working.

select * from customers inner join orders on customers.id = orders.customer_id;

than i open rails console and run this query:

Customer.joins(:orders)

which give me result of only customers, but i hope for merge of two models and appropriate result. when i run

Order.joins(:customer)

it return me only result of orders.

Is there option for retrieve merge result of two models? Thanks in advice :)

Answer 1

To include related tables in a query, use :includes

Customer.includes(:orders)

To only utilize related tables in query conditions, use :joins

Customer.joins(:order).where(:unpaid => true)

But you have another issue, in that even when you use do 'Customer.includes(:orders)' in the console, it still will only show you Customers, because that is primary call. But, the data is available without additional calls to get the order information. Examine the logs on after a view has rendered using both 'includes' and 'joins' and you'll see the difference in the number of calls.

Assuming you make the different calls in your controller and put the following in your view.

<% @orders.each do |o| %>
  <%= customer.order.details %>

Answer 2

To access customers' orders do :

customers = Customer.joins(:orders)
customers.each do |customer|
  customer.orders #<= contains orders for this customer
end

The other way around :

orders = Order.joins(:customer)
orders.each do |order|
  order.customer #<= contains the customer
end

Note that using joins, you're doing an inner join and therefore exclude customers without orders ... if you want to include them, use includes(:orders) instead