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