Good afternoon,
So far so good, but I''m a bit confused. I have a table consisting of
clients, each client has multiple email accounts, each email account has
multiple spam prefs. Everything I''ve done so far is working, up to the
part where I am now trying to display the spam prefs for each email account.
** SQL
"clients"
id
name
"emails"
id
client_id
pw_name
pw_domain
pw_clear_passwd
"userprefs"
prefid
preference
value
** Controller
class EmaillistController < ApplicationController
def show
@emails = Email.find(:all,
:conditions => [''client_id = ?'',
@params[:client_id]],
:order => ''pw_name'', :include => :userpref)
end
end
** Models
class Client < ActiveRecord::Base
has_many :emails
end
class Email < ActiveRecord::Base
belongs_to :client
has_many :userpref, :foreign_key => "prefid"
end
class Userpref < ActiveRecord::Base
belongs_to :email
set_primary_key "prefid"
end
** View/show.rb
<table cellpadding="2" cellspacing="2"
border="0">
<tr><th>Email Accounts</th></tr>
<% for email in @emails %>
<tr><td><strong>Email: </strong></td>
<td><%=h email.pw_name %>@<%=h email.pw_domain
%></td>
<td><strong>Password: </strong></td>
<td><%=h email.pw_clear_passwd %></td></tr>
<% end %>
</table>
At this point everything is fine, I am using type ahead to select the
client, generating links for client email accounts, and displaying the
account information. My issue is I can''t figure out why I have no
userprefs! After rereading the docs again I believe I have the models
correct.
Apparently I need more than ":include => :userpref" in my show
controller. But I am at a loss as to what.
Thanks,
DAve
On 5/9/05, DAve <dave.list-+JIuMJIPudMuIF41do6k7w@public.gmane.org> wrote:> > Good afternoon, > > So far so good, but I''m a bit confused. I have a table consisting of > clients, each client has multiple email accounts, each email account has > multiple spam prefs. Everything I''ve done so far is working, up to the > part where I am now trying to display the spam prefs for each email > account. > > ** SQL > "clients" > id > name > > "emails" > id > client_id > pw_name > pw_domain > pw_clear_passwd > > "userprefs" > prefid > preference > value >When you set belongs_to :email on the UserPrefs model, Rails expects to find a "email_id" column in the SQL for userprefs. Without one, it can''t tell what userpref to connect to what. That seems to be all you''re missing. -- ______________ Mike Sugarbaker Super Geiniuis _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Mike Sugarbaker wrote:> On 5/9/05, DAve <dave.list-+JIuMJIPudMuIF41do6k7w@public.gmane.org> wrote: > >>Good afternoon, >> >>So far so good, but I''m a bit confused. I have a table consisting of >>clients, each client has multiple email accounts, each email account has >>multiple spam prefs. Everything I''ve done so far is working, up to the >>part where I am now trying to display the spam prefs for each email >>account. >> >>** SQL >>"clients" >>id >>name >> >>"emails" >>id >>client_id >>pw_name >>pw_domain >>pw_clear_passwd >> >>"userprefs" >>prefid >>preference >>value >> > > > When you set belongs_to :email on the UserPrefs model, Rails expects to find > a "email_id" column in the SQL for userprefs. Without one, it can''t tell > what userpref to connect to what. That seems to be all you''re missing. >Isn''t that taken care of by the model? class Email < ActiveRecord::Base belongs_to :client has_many :userpref, :foreign_key => "prefid" end class Userpref < ActiveRecord::Base belongs_to :email set_primary_key "prefid" end Looking at the SQL that is output I can''t map userprefs to emails. I believe I need a emails_userprefs table to map emails.id to userprefs.prefid. Time to go read docs again. Thanks, DAve
On 9.5.2005, at 22:25, DAve wrote:> > Isn''t that taken care of by the model?No, AR models are just a way to map relational database models to the object world.> > class Email < ActiveRecord::Base > belongs_to :client > has_many :userpref, :foreign_key => "prefid"This assumes that there is a prefid field in userprefs table that is a foreign key referencing emails.id. However, as prefid is the primary key of userprefs table, you can''t build a one-to-many relationship like this. If one email has many userprefs, you should have a field called email_id in userprefs table that is a foreign key referencing emails.id. Change the has_many row to "has_many :userprefs" and add a field called "email_id" to userprefs table and you should be all set.> end > > class Userpref < ActiveRecord::Base > belongs_to :email > set_primary_key "prefid" > end > > Looking at the SQL that is output I can''t map userprefs to emails. I > believe I need a emails_userprefs table to map emails.id to > userprefs.prefid.No, you don''t, unless you need a many-to-many relationship between emails and prefs, which I very much doubt.> Time to go read docs again.I would recommend some tutorial on relational database design (e.g. [1]). Amy Hoy''s AR association cheat sheet [2] is an excellent resources for remembering how the different models are mapped in Rails. //jarkko [1] http://dev.mysql.com/tech-resources/articles/intro-to- normalization.html [2] http://slash7.com/cheats/activerecord_cheatsheet.pdf> > Thanks, > > DAve > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- Jarkko Laine http://jlaine.net http://odesign.fi _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Jarkko Laine wrote:> > On 9.5.2005, at 22:25, DAve wrote: > >> >> Isn''t that taken care of by the model? > > > No, AR models are just a way to map relational database models to the > object world. > >> >> class Email < ActiveRecord::Base >> belongs_to :client >> has_many :userpref, :foreign_key => "prefid" > > > This assumes that there is a prefid field in userprefs table that is a > foreign key referencing emails.id. However, as prefid is the primary > key of userprefs table, you can''t build a one-to-many relationship like > this. If one email has many userprefs, you should have a field called > email_id in userprefs table that is a foreign key referencing emails.id. > > Change the has_many row to "has_many :userprefs" and add a field called > "email_id" to userprefs table and you should be all set.The plural in "has_many :userprefs" was a typo on my part. I cannot add a field. The task is a matter of replacing several different internal applications, written by different people, for different purposes. Ala 5 years of intranet tools, all stand alone. I have thrown out all of the past DBs and started from scratch with a single DB, but some tables can''t be modified, userprefs is such a table. Currently the userprefs table is accessed by it''s application via the username field, which is the complete email address. I normally get that information in other applications by selecting as CONCAT(email.pw_name,''@'',email.pw_domain) = userprefs.username from the email table when needed, such as webmail auth, prefs editing, etc. I have one other table, valias which is not able to be modified as well. I have several applications all different sources which rely on it. Rows are selected as email.pw_name = valias.alias AND email.pw_domain = valias.domain>> end >> >> class Userpref < ActiveRecord::Base >> belongs_to :email >> set_primary_key "prefid" >> end >> >> Looking at the SQL that is output I can''t map userprefs to emails. I >> believe I need a emails_userprefs table to map emails.id to >> userprefs.prefid. > > No, you don''t, unless you need a many-to-many relationship between > emails and prefs, which I very much doubt.I do not, I have a one to many relationship of emails to userprefs, and emails to valias.> >> Time to go read docs again. > > I would recommend some tutorial on relational database design (e.g. > [1]). Amy Hoy''s AR association cheat sheet [2] is an excellent > resources for remembering how the different models are mapped in Rails. > > //jarkko > > [1] http://dev.mysql.com/tech-resources/articles/intro-to- > normalization.html > [2] http://slash7.com/cheats/activerecord_cheatsheet.pdf >[1] The majority of the database is in tnf. We did slouch to a couple of lookup tables for the pull down menus ;^) I''ll continue reading the examples and API. Thanks, DAve
DAve wrote:> Jarkko Laine wrote: >> >> On 9.5.2005, at 22:25, DAve wrote: >>> >>> Isn''t that taken care of by the model? >> >> No, AR models are just a way to map relational database models to the >> object world. >> >>> class Email < ActiveRecord::Base >>> belongs_to :client >>> has_many :userpref, :foreign_key => "prefid" >> >> This assumes that there is a prefid field in userprefs table that is >> a foreign key referencing emails.id. However, as prefid is the >> primary key of userprefs table, you can''t build a one-to-many >> relationship like this. If one email has many userprefs, you should >> have a field called email_id in userprefs table that is a foreign key >> referencing emails.id. >> >> Change the has_many row to "has_many :userprefs" and add a field >> called "email_id" to userprefs table and you should be all set. > > The plural in "has_many :userprefs" was a typo on my part. > > I cannot add a field. The task is a matter of replacing several > different internal applications, written by different people, for > different purposes. Ala 5 years of intranet tools, all stand alone. I > have thrown out all of the past DBs and started from scratch with a > single DB, but some tables can''t be modified, userprefs is such a table. > > Currently the userprefs table is accessed by it''s application via the > username field, which is the complete email address. I normally get that > information in other applications by selecting as > > CONCAT(email.pw_name,''@'',email.pw_domain) = userprefs.username > > from the email table when needed, such as webmail auth, prefs editing, etc. > > I have one other table, valias which is not able to be modified as well. > I have several applications all different sources which rely on it. Rows > are selected as > > email.pw_name = valias.alias AND email.pw_domain = valias.domain > >>> end >>> >>> class Userpref < ActiveRecord::Base >>> belongs_to :email >>> set_primary_key "prefid" >>> end >>> >>> Looking at the SQL that is output I can''t map userprefs to emails. I >>> believe I need a emails_userprefs table to map emails.id to >>> userprefs.prefid. >> >> No, you don''t, unless you need a many-to-many relationship between >> emails and prefs, which I very much doubt. > > I do not, I have a one to many relationship of emails to userprefs, and > emails to valias. >> >>> Time to go read docs again. >> >> I would recommend some tutorial on relational database design (e.g. >> [1]). Amy Hoy''s AR association cheat sheet [2] is an excellent >> resources for remembering how the different models are mapped in Rails. >> >> //jarkko >> >> [1] http://dev.mysql.com/tech-resources/articles/intro-to- >> normalization.html >> [2] http://slash7.com/cheats/activerecord_cheatsheet.pdf >> > [1] The majority of the database is in tnf. We did slouch to a couple of > lookup tables for the pull down menus ;^) > > I''ll continue reading the examples and API.OK, I am an idiot. Late nights and lack of coffee are a poor excuse I know. We made some adjustments, looked at some source code. We can add fields to our legacy tables without recompiling all the apps that use them and have done so. Minor adjustments to libs and a conf file solved the problem. So starting over I have everything working as it should and I understand much better just how Rails works. Ajax is working, eager loading is working, life is good. On a side note I found a simple solution to the issue of tables with a singular name ending with an "s" as in "alias". I left the table name as is and changed the model name to "valia" and let Rails have it''s way. Simple solutions rock. Thanks, DAve
On 5/10/05, DAve <dave.list-+JIuMJIPudMuIF41do6k7w@public.gmane.org> wrote:> On a side note I found a simple solution to the issue of tables with a > singular name ending with an "s" as in "alias". I left the table name as > is and changed the model name to "valia" and let Rails have it''s way. > Simple solutions rock.I''d personally prefer to use "set_table_name ''alias'' " rather than munge the name of my model, but maybe that''s just me ;) Jason
Jason Foreman wrote:> On 5/10/05, DAve <dave.list-+JIuMJIPudMuIF41do6k7w@public.gmane.org> wrote: > >>On a side note I found a simple solution to the issue of tables with a >>singular name ending with an "s" as in "alias". I left the table name as >>is and changed the model name to "valia" and let Rails have it''s way. >>Simple solutions rock. > > > I''d personally prefer to use "set_table_name ''alias'' " rather than > munge the name of my model, but maybe that''s just me ;)I would too, didn''t work though. Rails turns "valias" into "valiase" when eager loading. I tried each HowTo/Example. No joy. DAve> > > Jason > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > >