How to use foreign key to access record in foreign table

Rails
1

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:

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

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

2

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

On 9/11/06, Richard L.

3

Chris H. wrote:

without having to make additional DB queries.

%>

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

4

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

5

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

Ciao,
Richard

6

Gareth A. wrote:

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

7

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

8

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

9

Richard wrote:

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.

Since you say you’re new at this, here’s a hint that might be useful:

Use script/console! Then you can try out this stuff on your own, and get
fast answers.

i = Invoice.find(1)
i.customer.name

It’ll give you a name or it’ll give you an error – either way, you know
what you need to know!

–Al Evans

10

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

11

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

12

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”

  1. 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

13

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