How to use foreign key to access record in foreign table

Hi All,

I'm generating a table of invoice items (Number. Original. Balance and
Customer)

My problem is the fourth column. I have the customer_id, which I can
display as link (which links to the correct custom record). But I want to
display each customer's name rather than his ID as the text of the link.

How can I modify the code for this forth column:

   <td> <%= link_to(i.customer_id, # really want customer's name here
     :controller => 'customer',
     :action => 'show',
     :id => i.customer_id)
    %>
   </td>

I've got a customers table in the DB and a Customer model.

Thanks in Advance,
Richard

assuming invoices and customers are related

class Invoice < ActiveRecord::Base
  belongs_to :customer
  ...
end

class Customer < ActiveRecord::Base
  has_many :invoices
end

@invoices = Invoice.find(:all, :include => :customer)

then in your view

<% @invoices.each do |i| -%>
  link_to i.customer.name, ...
<% end -%>

using :include in the find call 'includes' all the customer records in
the query as well, so you have access to the customer information
without having to make additional DB queries.

Chris

Chris Hall wrote:

assuming invoices and customers are related

class Invoice < ActiveRecord::Base
  belongs_to :customer
  ...
end

class Customer < ActiveRecord::Base
  has_many :invoices
end

@invoices = Invoice.find(:all, :include => :customer)

then in your view

<% @invoices.each do |i| -%>
  link_to i.customer.name, ...
<% end -%>

using :include in the find call 'includes' all the customer records in
the query as well, so you have access to the customer information
without having to make additional DB queries.

Chris

>
> Hi All,
>
> I'm generating a table of invoice items (Number. Original. Balance and
> Customer)
>
> My problem is the fourth column. I have the customer_id, which I can
> display as link (which links to the correct custom record). But I want to
> display each customer's name rather than his ID as the text of the link.
>
> How can I modify the code for this forth column:
>
> <td> <%= link_to(i.customer_id, # really want customer's name here
> :controller => 'customer',
> :action => 'show',
> :id => i.customer_id)
> %>
> </td>
>
> I've got a customers table in the DB and a Customer model.
>
> Thanks in Advance,
> Richard
> ---
> Env.: WinXP-Pro/SP2, MS VisualStudio.NET Eclipse 3.1.2
> Ruby 1.8.2-15 Rails 1.1.4 Java JDK 1.5.0_06
>
>
>
>
> >
>

Hi Chris,

All your assumptions were correct. So adding the :include and using
i.customer.name did the job.

Thank you very much for your perfect response. With the Rails gurus
like you I'll eventually learn this stuff.

Best wishes,
Richard

Just for the record, I meant: With the help of Rails gurus like you
I'll eventually learn this stuff.

Ciao,
Richard

Richard <RichardDummyMailbox58407@...> writes:

Hi Chris,

All your assumptions were correct. So adding the :include and using
i.customer.name did the job.

For the record, the :include isn't needed. Invoice#customer will load the
Customer details from the database if they haven't already been loaded. What the
:include does is preload the customer data in the same SQL query as the invoices

Gareth

Gareth Adams wrote:

Richard <RichardDummyMailbox58407@...> writes:
>
> Hi Chris,
>
> All your assumptions were correct. So adding the :include and using
> i.customer.name did the job.

For the record, the :include isn't needed. Invoice#customer will load the
Customer details from the database if they haven't already been loaded. What the
:include does is preload the customer data in the same SQL query as the invoices

Gareth

Hi Gareth,

Thanks for the add'l info. I had I hunch the the customer name should
have been available easily from an invoice instance since "invoice
belongs to customer" and "customer has many invoices." I just lacked
the wit to write "i.customer.name", which worked great without the
join, i.e. include.

I was going to look into that later because I was concerned about
perfomance and memory if we got a large number of customers and I was
paginating the table consisting of independent custormer and invoice
sub-tables, which I'm planning to add shortly.

Best wishes,
Richard

yes, the :include is not necessary, however, instead of just 1 query,
you will now have 1+n queries, where n = # of invoices.

so you will want to use :include, unless you like the extra queries

Chris

Hi Al,

Thanks very much for that bit of info. It worked fine for me.

It was especially helpful because I recogized the console's
presentation as similar to what I had gotten in the past with
breakpoints. Recently, I was unable to get breakpoints working, so I
carried on without them. Your hint led me to retry breakpoints,
whereupon trial-and-error showed me that my failure was the lack of <%
... %> around the breakpoint.

All in all, a very satisfactory day!!

Best wishes,
Richard

Al Evans wrote:

Hi Chris,

I agree about the "n+1" joins, but did you see my response to Gareth,
namely:

"I was going to look into that [avoiding the "join"] later because I
was concerned about
perfomance and memory if we got a large number of customers and I was
paginating the table consisting of independent custormer and invoice
sub-tables, which I'm planning to add shortly."?

Do you agree that my concern (about the resource requirements about a
join on large tables) is legitimate?

Regards,
Richard

Chris Hall wrote:

my advice is

1) make sure your tables are indexed correctly (ie, index all your *_id columns)
2) if you are going to require customer information for every invoice
you list, you'll save on queries by using :include and avoid the n+1
issue.

i'd seriously doubt you'd run into any performance (remember, index is
your friend) or memory constraints by eager loading the associations.
you can always run unit/integration tests with several thousand
invoices and customers to see.

I just had an issue today where an app we are working on was first
doing a find on a list of objects, then looping through those objects
and loading the associations. because we didn't use :include, we were
getting n*2+1 (loading 2 associations after the fact) queries which
was causing the request to sit and work through all the queries, which
took several seconds (we had also forgotten to index the *_id columns
as well)...when we indexed and changed the find to use :include, it
only took a couple of seconds to get the data and render it.

Chris

Hi Chris,

Thanks for expanding your ideas on this subject.

1) make sure your tables are indexed correctly (i.e., index all your *_id
columns)

A definite "must do"

2) if you are going to require customer information for every invoice
you list, you'll save on queries by using :include and avoid the no+1
issue.

My concern is two-fold. First, response time when I have several large
tables joined but I'm only displaying a small fragment at any moment because
I'm paginating the response. Second, being able to run on a client system
with less RAM than my development system.

you can always run unit/integration tests with several thousand
invoices and customers to see.

I think that's the key!! That's what I've got to learn to do in Ruby/Rails
after I finish the initial development effort.

Again, thanks for you additional advice.

Best wishes,
Richard