I''m very tempted to just give up and use find_by_sql, but I want to
know the
correct way to do this:
I have two tables, Blogs and Users. Blogs.user_id is a FK to
Users.id<http://Users.id>.
To properly show all blogs or just those for a given user, I have this code
in my blogs_controller (note I''m using paginate):
def list
if params[:id]
@blog_pages, @blogs = paginate :blog, :per_page => 10,
:conditions=>"user_id
= #{params[:id]}", :order_by => ''blogs.created_at
desc'' #, :join=>''inner
join users on blogs.user_id = users.id <http://users.id>''
else
@blog_pages, @blogs = paginate :blog, :per_page => 10 , :order_by =>
''
users.last_name, users.first_name, blogs.created_at desc'',
:join=>''inner
join users on blogs.user_id = users.id <http://users.id>''
end
end
Now, my problem is when a user id is specified (params[:id]) by which to
trim the list -- i.e. the "true" portion of the conditional. I have a
"Read
More..." link created for each entry (some html stripped for brevity):
<% for blog in @blogs -%>
<%= blog.body.slice(0,500) %>
<%= link_to(''Read more...'', :action =>
''show'', :id => blog.id<http://blog.id>)
if blog.body.length > 200%><br /><br />
<% end -%>
The problem is, in the link_to, blog.id <http://blog.id> returns the value
stored in the Users.id <http://Users.id> table (as a result of the join)
instead of the value from Blogs.id <http://Blogs.id> as I need. My
assumption is if ActiveRecord sees a duplicated column name, it simply uses
the last value. How do I get at the Blogs.id <http://Blogs.id> value?
--
Brock Weaver
brockweaver-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org
/* you are not expected to understand this */
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails
Brock Weaver wrote:> I''m very tempted to just give up and use find_by_sql, but I want to > know the correct way to do this: > > I have two tables, Blogs and Users. Blogs.user_id is a FK to Users.id > <http://Users.id>. To properly show all blogs or just those for a > given user, I have this code in my blogs_controller (note I''m using > paginate): > > def list > if params[:id] > @blog_pages, @blogs = paginate :blog, :per_page => 10, > :conditions=>"user_id = #{params[:id]}", :order_by => > ''blogs.created_at desc'' #, :join=>''inner join users on blogs.user_id = > users.id <http://users.id>'' > else > @blog_pages, @blogs = paginate :blog, :per_page => 10 , > :order_by => ''users.last_name, users.first_name, blogs.created_at > desc'', :join=>''inner join users on blogs.user_id = users.id > <http://users.id>'' > end > end > > Now, my problem is when a user id is specified (params[:id]) by which > to trim the list -- i.e. the "true" portion of the conditional. I > have a "Read More..." link created for each entry (some html stripped > for brevity): > > <% for blog in @blogs -%> > <%= blog.body.slice(0,500) %> > <%= link_to(''Read more...'', :action => ''show'', :id => blog.id > <http://blog.id>) if blog.body.length > 200%><br /><br /> > <% end -%> > > The problem is, in the link_to, blog.id <http://blog.id> returns the > value stored in the Users.id <http://Users.id> table (as a result of > the join) instead of the value from Blogs.id <http://Blogs.id> as I > need. My assumption is if ActiveRecord sees a duplicated column name, > it simply uses the last value. How do I get at the Blogs.id > <http://Blogs.id> value? > > -- > Brock Weaver > brockweaver-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org <mailto:brockweaver-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> > > /* you are not expected to understand this */ > >------------------------------------------------------------------------ > >_______________________________________________ >Rails mailing list >Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org >http://lists.rubyonrails.org/mailman/listinfo/rails > >Brock, I had a couple of tables which match your relationship, and put in an inner join, and it DOES work for me like it does for you. The thing is, I''m not sure you NEED the join at all! I assume you are doing that to get to the fields of the user associated with the blog(s). Without the inner join, you should be able to access blog.id - the id of the blog (for your link) blog.body (for your display) blog.user (the user object for the blog entry) blog.user.id (the id for the user for the blog entry) blog.user.name (or whatever fields exist for your user table/class) If you remove the join but retain the :conditions, can''t you access the fields you need via the "magic" of ActiveRecord? If not, I guess I don''t understand what the join is providing you in this context. H.B. _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Brock Weaver wrote:> Using the join allows me to order the results by the user''s last name > / first name. If I remove the join, I can get to the blog.id > <http://blog.id> fine. But then it creates invalid SQL, as I am > unable to say "order by users.last_name, users.first_name, > blogs.created_at". I really want the output sorted in this fashion, > and do not know of the "rails" way of doing this. > > But you''re right, the "rails" way of pulling dependent data is to > leave out the join, and have it re-hit the db for each record to > lookup the user record as it goes. But how do I order my output > correctly if I do it this way? >Brock, Ah. I wondered if there was something else involved. I am able to use sort_by for my needs, because I''m not using paginate. I''m sorry to admit that I haven''t ever run into that to date in my meager experience with Rails. I fiddled around with an example from the docs, but it didn''t work as you would want it to. Maybe someone who has tackled this in the past will come up with a solution for you. Good luck, H.B. _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Brock Weaver wrote:> Using the join allows me to order the results by the user''s last name > / first name. If I remove the join, I can get to the blog.id > <http://blog.id> fine. But then it creates invalid SQL, as I am > unable to say "order by users.last_name, users.first_name, > blogs.created_at". I really want the output sorted in this fashion, > and do not know of the "rails" way of doing this. > > But you''re right, the "rails" way of pulling dependent data is to > leave out the join, and have it re-hit the db for each record to > lookup the user record as it goes. But how do I order my output > correctly if I do it this way?I apologize if this gets double-posted. I thought I sent it earlier but there may have been a problem. I had one other thought. I don''t know if it will work for you, but have you tried putting an ":order" in the app/models/blog.rb? I put this into my "Team" model and it correctly sorted the players for each team. class Team < ActiveRecord::Base has_many :players, :order => "last_name, first_name" has_many :games end Would that work for your needs and still allow you to use paginate? It generated the correct SQL for me, and allowed me to get the players in the correct order without having to fiddle with joins in the paginate method. H.B. _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Doing that can make the output ordering correct, but I can''t seem to get it to work with pagination. Adding the explicit join to pagination works, but isn''t very "rails-ish". My problem is I want the dependent table (in this case, users) to drive the ordering over the parent table (blogs), which I think isn''t as common as the parent driving ordering over the child -- which I think is your case (Teams are ordered, and within a team order by player names). At any rate, I''m going to punt right now and just use the join. Thank you for your time! Hopefully I can eventually repay your kindness... On 9/23/05, H.B. Taylor <HBTaylor-rphTv4pjVZMJGwgDXS7ZQA@public.gmane.org> wrote:> > Brock Weaver wrote: > > Using the join allows me to order the results by the user''s last name / > first name. If I remove the join, I can get to the blog.id<http://blog.id>fine. But then it creates invalid SQL, as I am unable to say "order by > users.last_name, users.first_name, blogs.created_at". I really want the > output sorted in this fashion, and do not know of the "rails" way of doing > this. > > But you''re right, the "rails" way of pulling dependent data is to leave > out the join, and have it re-hit the db for each record to lookup the user > record as it goes. But how do I order my output correctly if I do it this > way? > > > I apologize if this gets double-posted. I thought I sent it earlier but > there may have been a problem. > > I had one other thought. I don''t know if it will work for you, but have > you tried putting an ":order" in the app/models/blog.rb? I put this into my > "Team" model and it correctly sorted the players for each team. > > class Team < ActiveRecord::Base > has_many :players, :order => "last_name, first_name" > has_many :games > end > > Would that work for your needs and still allow you to use paginate? It > generated the correct SQL for me, and allowed me to get the players in the > correct order without having to fiddle with joins in the paginate method. > > H.B. > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > > >-- Brock Weaver brockweaver-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org /* you are not expected to understand this */ _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails