Hi all, Does Rails offer a simple way of ordering by a joined table in a belongs_to model? I''ve just run through the "4 Days on Rails" tutorial and I have some problems with the way the author ordered by Categories in his Item controller. He uses find_by_sql to query the database directly using SQL. Doesn''t this defeat the whole purpose of creating a model in the first place? One would think that when defining the model Category as "has_many :items" and the model Item as "belongs_to :category" that Rails would automatically do the join with the category when you grab an item. Does Rails offer this functionality? If not can someone explain why? Thanks for your help, Mike
Mike, I was just working on this myself. Here''s what I''ve
found,
unfortunately, it doesn''t seem to work well with pagination.
I''m not
sure if it''s because I''m using sqlserver or not. The
sqlserver
adapter has to jump through hoops to emulate the offset and limit
functions of mysql.
def list_by_category
@item_pages = Paginator.new self, Item.count, 2,
@params[''page'']
@items = Item.find(:all, :include=> :category,
:order=>''category'',
:limit=>@item_pages.current.to_sql)
render_action ''list''
end
On 9/12/05, Mike Elkink <melkink-a84/LA3/q2c@public.gmane.org>
wrote:> Hi all,
>
> Does Rails offer a simple way of ordering by a joined table in a belongs_to
> model?
>
> I''ve just run through the "4 Days on Rails" tutorial and
I have some
> problems with the way the author ordered by Categories in his Item
> controller. He uses find_by_sql to query the database directly using SQL.
> Doesn''t this defeat the whole purpose of creating a model in the
first
> place?
>
> One would think that when defining the model Category as "has_many
:items"
> and the model Item as "belongs_to :category" that Rails would
automatically
> do the join with the category when you grab an item. Does Rails offer this
> functionality? If not can someone explain why?
>
> Thanks for your help,
>
> Mike
>
> _______________________________________________
> Rails mailing list
> Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>
--
Marlon
"Now watch what you say or they''ll be calling you a radical,
liberal, fanatical, criminal. "
On 12.9.2005, at 20.15, Mike Elkink wrote:> Hi all, > > Does Rails offer a simple way of ordering by a joined table in a > belongs_to > model? > > I''ve just run through the "4 Days on Rails" tutorial and I have some > problems with the way the author ordered by Categories in his Item > controller. He uses find_by_sql to query the database directly > using SQL. > Doesn''t this defeat the whole purpose of creating a model in the first > place? > > One would think that when defining the model Category as > "has_many :items" > and the model Item as "belongs_to :category" that Rails would > automatically > do the join with the category when you grab an item. Does Rails > offer this > functionality? If not can someone explain why?Not automatically, it would be too much overhead for many cases. If you want to grab associated objects in one query, use the include parameter: Item.find(:all, :include => :category) See the docs for eager loading for more info: http:// rails.rubyonrails.com/classes/ActiveRecord/Associations/ ClassMethods.html //jarkko> > Thanks for your help, > > Mike > > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails > >_______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails
Thanks Marlon and Jarkko, that''s exactly the info I was looking for. Mike
Mike,
As others have written, :include will help you to do a lot of what
you want to do. You may also find sometimes that :joins may be helpful.
.find(:all, :include => [:table2, :table3]) # LEFT OUTER JOIN
.find(:all, :joins =>[:table2, :table3] # LEFT JOIN (rarely
needed)
But--and someone please correct me if I''m wrong--it is not possible
to daisy-chain your tables together. The parameters :include
and :joins will accept more than one table as a value, but each table
listed is joined to a foreign_key in :table1 and not to each other in
series. You can also search those two joined tables for the
foreign_key of a third table which allows you to effectively
"traverse" a maximum of three tables when performing a find as long
as you know the value of the key.
For example, you can find the grandchildren of an item in :table1 with:
@grandchildren = Table3.find(:all,
:include => ":table2",
:conditions => ["table2.table1_id = ?",
grandfather_id])
Or find the grandfather of an item in :table3 with:
@grandfather = Table1.find(:all,
:include => ":table2",
:conditions => ["table2.table3_id = ?",
grandchild_id])
But the problem I run into is: what if you add a :table4 which is
related to :table3? Or even more commonly, if you want to search the
grandchild for a certain attribute instead of just looking for its
key? Is the only solution find_by_sql? It seems like Rails should
be able to look further up or down the belongs_to/has_many chain to
accommodate this since it handles the first LEFT OUTER JOIN so nicely.
Kevin Skoglund
kevin-WGmuFPN42W8gMxX8nMqP6gC/G2K4zDHf@public.gmane.org
I''d be really interested in an answer to this as well.
I''ve got Project, task, and tasktime
Project has_many :tasks
Task has_many :tasktimes
Tasktime belongs_to :task
Task belongs_to :project
So, with that in mind, I cannot simply retrive all the tasktimes for a
project without doing find_by_sql because rails has no way of doing, for
example:
@tasktimes = @project.tasks.tasktimes
Unless I am completely missing something here.
-----Original Message-----
From: rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
[mailto:rails-bounces-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org] On
Behalf Of Kevin Skoglund
Sent: Monday, September 12, 2005 4:13 PM
To: Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
Subject: [Rails] Re: Alternatives to find_by_sql
Mike,
As others have written, :include will help you to do a lot of what
you want to do. You may also find sometimes that :joins may be helpful.
.find(:all, :include => [:table2, :table3]) # LEFT OUTER JOIN
.find(:all, :joins =>[:table2, :table3] # LEFT JOIN (rarely
needed)
But--and someone please correct me if I''m wrong--it is not possible
to daisy-chain your tables together. The parameters :include
and :joins will accept more than one table as a value, but each table
listed is joined to a foreign_key in :table1 and not to each other in
series. You can also search those two joined tables for the
foreign_key of a third table which allows you to effectively
"traverse" a maximum of three tables when performing a find as long
as you know the value of the key.
For example, you can find the grandchildren of an item in :table1 with:
@grandchildren = Table3.find(:all,
:include => ":table2",
:conditions => ["table2.table1_id = ?",
grandfather_id])
Or find the grandfather of an item in :table3 with:
@grandfather = Table1.find(:all,
:include => ":table2",
:conditions => ["table2.table3_id = ?",
grandchild_id])
But the problem I run into is: what if you add a :table4 which is
related to :table3? Or even more commonly, if you want to search the
grandchild for a certain attribute instead of just looking for its
key? Is the only solution find_by_sql? It seems like Rails should
be able to look further up or down the belongs_to/has_many chain to
accommodate this since it handles the first LEFT OUTER JOIN so nicely.
Kevin Skoglund
kevin-WGmuFPN42W8gMxX8nMqP6gC/G2K4zDHf@public.gmane.org
_______________________________________________
Rails mailing list
Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org
http://lists.rubyonrails.org/mailman/listinfo/rails
On 9/12/05, Marlon Moyer <marlon.moyer-Re5JQEeQqe8AvxtiuMwx3w@public.gmane.org> wrote:> > Mike, I was just working on this myself. Here''s what I''ve found, > unfortunately, it doesn''t seem to work well with pagination. I''m not > sure if it''s because I''m using sqlserver or not. The sqlserver > adapter has to jump through hoops to emulate the offset and limit > functions of mysql. > > def list_by_category > @item_pages = Paginator.new self, Item.count, 2, @params[''page''] > @items = Item.find(:all, :include=> :category, :order=>''category'', > :limit=>@item_pages.current.to_sql) > render_action ''list'' > end >Look in the docs for pagination, it uses :order_by and not :order. Chris _______________________________________________ Rails mailing list Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org http://lists.rubyonrails.org/mailman/listinfo/rails