Rails - Jun 2006 - has_many, through, want to store email in mysql

Hi,

I''m still a new with ROR, but it''s really cool :) I just have
this
problem with a project of mine.

I want to store email in a database. I already wrote a simple import 
script, that works, so that''s not a problem. Showing the email 
(efficiently) in a ROR site is.

These tables are in the database:

emails
  id
  email_id (foreign key -> emails.id)
  seq
  preamble
  body
  epilogue

headers
  id
  email_id (foreign key -> emails.id)
  headerkeys_id (foreign key -> headerkeys.id)
  seq
  value

headerkeys
  id
  name

First, I thought I could use a HABTM (has and belongs to many), but 
since the join table (headers) also contains extra information, like seq 
and value, this shouldn''t be used, from what I read..

So, in ROR, I have these models defined:

email.rb:
class Email < ActiveRecord::Base
       has_many :headers
       has_many :headerkeys, :through => :headers
end

headerkey.rb:
class Headerkey < ActiveRecord::Base
   has_many :emails
   has_many :headers, :through => :headers
end

header.rb:
class Header < ActiveRecord::Base
      belongs_to :email
      belongs_to :headerkey
end

What I want to achieve, is to list all emails (a list like in every mail 
client, with columns from, subject, date, etc), though a scaffold.

In the email_controller.rb, I have this:

def list
    @email_pages, @emails = paginate :emails, :per_page => 10, 
:conditions => ["email_id is NULL"]
end

And in the list view this (in short - only from colunn):

<% for email in @emails %>
<tr>
<td><%= email.headers.find(:first, :conditions =>
[''headerkey_id = ?'',
6]).value %></td>
</tr>
<% end %>

This means that for 30 emails, in the case of 3 columns (from, subject, 
date), a total of 32 SELECTS have to be done.. That doesn''t look too 
efficient :)

What would be the best way to do this ? Should I use a predefined JOIN 
statement in the controller (with the paginate function) ? Or is it 
possible to do this with the current controller and with some changes in 
the view ?

Thanks in advance..

regards,

Leon







-- 
Posted via http://www.ruby-forum.com/.

Hi Leon, sounds like a job for eager loading

look in ActiveRecord#associations

basically use :include to join in the associated columns

hth




On 6/6/06, Leon de Rooij <leon@toyos.nl> wrote:
> Hi,
>
> I''m still a new with ROR, but it''s really cool :) I just
have this
> problem with a project of mine.
>
> I want to store email in a database. I already wrote a simple import
> script, that works, so that''s not a problem. Showing the email
> (efficiently) in a ROR site is.
>
> These tables are in the database:
>
> emails
>   id
>   email_id (foreign key -> emails.id)
>   seq
>   preamble
>   body
>   epilogue
>
> headers
>   id
>   email_id (foreign key -> emails.id)
>   headerkeys_id (foreign key -> headerkeys.id)
>   seq
>   value
>
> headerkeys
>   id
>   name
>
> First, I thought I could use a HABTM (has and belongs to many), but
> since the join table (headers) also contains extra information, like seq
> and value, this shouldn''t be used, from what I read..
>
> So, in ROR, I have these models defined:
>
> email.rb:
> class Email < ActiveRecord::Base
>        has_many :headers
>        has_many :headerkeys, :through => :headers
> end
>
> headerkey.rb:
> class Headerkey < ActiveRecord::Base
>    has_many :emails
>    has_many :headers, :through => :headers
> end
>
> header.rb:
> class Header < ActiveRecord::Base
>       belongs_to :email
>       belongs_to :headerkey
> end
>
> What I want to achieve, is to list all emails (a list like in every mail
> client, with columns from, subject, date, etc), though a scaffold.
>
> In the email_controller.rb, I have this:
>
> def list
>     @email_pages, @emails = paginate :emails, :per_page => 10,
> :conditions => ["email_id is NULL"]
> end
>
> And in the list view this (in short - only from colunn):
>
> <% for email in @emails %>
> <tr>
> <td><%= email.headers.find(:first, :conditions =>
[''headerkey_id = ?'',
> 6]).value %></td>
> </tr>
> <% end %>
>
> This means that for 30 emails, in the case of 3 columns (from, subject,
> date), a total of 32 SELECTS have to be done.. That doesn''t look
too
> efficient :)
>
> What would be the best way to do this ? Should I use a predefined JOIN
> statement in the controller (with the paginate function) ? Or is it
> possible to do this with the current controller and with some changes in
> the view ?
>
> Thanks in advance..
>
> regards,
>
> Leon
>
>
>
>
>
>
>
> --
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>

-- 
Rodney
http://www.pinupgeek.com
http://www.dutchrailers.org