I can''t see a neat way of using the pagination helper with habtm
relationships.
Example: I have two tables, "folders" and "messages", with a
habtm
relationship between them. Now lets say that I want to do a paginated
display of all the messages in a particular folder.
First I need to get the count of messages in the folder to construct
the paginator. The best I can come up with is something like:
count = Message.count(["message_id = id AND folder_id = ?",
@folder.id], "folders_message")
Not too graceful given I''m basically generating my own SQL for the
relationship.
The paginator looks like this:
@message_pages = Paginator.new self, count, 10, @params["page"]
Then I want to find all the messages in the given folder for the
paginator''s current page. This is where I get really stuck! I
can''t
use Message.find without (again) constructing my own SQL to enforce
the habtm relationship (which seems to require a different join
syntax to the count call just to make things difficult), and I can''t
see a way of using @folder.messages that doesn''t retrieve all the
messages.
Any advice or ideas?
Cheers,
Pete Yandell
On 9 May 2005, at 7:39, Pete Yandell wrote:> I can''t see a neat way of using the pagination helper with habtm > relationships. > > <snip> > > Then I want to find all the messages in the given folder for the > paginator''s current page. This is where I get really stuck! I can''t > use Message.find without (again) constructing my own SQL to enforce > the habtm relationship (which seems to require a different join > syntax to the count call just to make things difficult), and I > can''t see a way of using @folder.messages that doesn''t retrieve all > the messages.This is why I use the pagination system in a back-to-front way. I do the LIMIT and OFFSET calculation manually, but then use the pagination helper to do all the HTML work in the view. I''ve some code demonstrating this. I didn''t clean out all my implementation specific stuff, but check it out: http://rafb.net/paste/results/GS5iKq55.html What you really want to look at is how I use my own SQL query, run a find_by_sql on it, and a COUNT() version of the same query, and then feed those into the pagination helper. At the very least, you can have a good laugh at my code ;-) or see one way to go about putting together complex SQL statements easily. Cheers, Pete
I use the exact same approach, and I have some more code to show. I put all that messy SQL stuff in my models, so the controller is kept relatively clean. http://dev.comiclog.com/file/trunk/app/models/creator.rb http://dev.comiclog.com/file/trunk/app/controllers/creators_controller.rb (look at the by_company index). On 5/9/05, Peter Cooper <peter-kB4nxRGAR8jpCd0h4Hqbjlpr/1R2p/CL@public.gmane.org> wrote:> On 9 May 2005, at 7:39, Pete Yandell wrote: > > > I can''t see a neat way of using the pagination helper with habtm > > relationships. > > > > <snip> > > > > Then I want to find all the messages in the given folder for the > > paginator''s current page. This is where I get really stuck! I can''t > > use Message.find without (again) constructing my own SQL to enforce > > the habtm relationship (which seems to require a different join > > syntax to the count call just to make things difficult), and I > > can''t see a way of using @folder.messages that doesn''t retrieve all > > the messages. > > This is why I use the pagination system in a back-to-front way. > > I do the LIMIT and OFFSET calculation manually, but then use the > pagination helper to do all the HTML work in the view. I''ve some code > demonstrating this. I didn''t clean out all my implementation specific > stuff, but check it out: > > http://rafb.net/paste/results/GS5iKq55.html > > What you really want to look at is how I use my own SQL query, run a > find_by_sql on it, and a COUNT() version of the same query, and then > feed those into the pagination helper. At the very least, you can > have a good laugh at my code ;-) or see one way to go about putting > together complex SQL statements easily. > > Cheers, > Pete > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- rick http://techno-weenie.net
On 9 May 2005, at 22:02, Rick Olson wrote:> I use the exact same approach, and I have some more code to show. > > I put all that messy SQL stuff in my models, so the controller is kept > relatively clean.Nice work. I can definitely learn some style from that, although in my case I need to sort and search by an arbitrary number of fields at the same time, so it''d be a little more complex. I''ve been tossing up whether to move things into the model or not, as I only need to do any/all of this stuff from one controller, ever.. but it wouldn''t hurt to keep things clean I guess. Anyway, I definitely like the style. :) Cheers, Pete
I have a more complex version in use in another model: http://dev.comiclog.com/file/trunk/app/models/issue.rb For instance, I can specify an array of IDs to ignore using the get_dupe_sql method. Let me know what you come up with. I was hoping someone could show me a better way. This just seems hackish to me. But it works :) On 5/9/05, Peter Cooper <peter-kB4nxRGAR8jpCd0h4Hqbjlpr/1R2p/CL@public.gmane.org> wrote:> On 9 May 2005, at 22:02, Rick Olson wrote: > > > I use the exact same approach, and I have some more code to show. > > > > I put all that messy SQL stuff in my models, so the controller is kept > > relatively clean. > > Nice work. I can definitely learn some style from that, although in > my case I need to sort and search by an arbitrary number of fields at > the same time, so it''d be a little more complex. I''ve been tossing up > whether to move things into the model or not, as I only need to do > any/all of this stuff from one controller, ever.. but it wouldn''t > hurt to keep things clean I guess. Anyway, I definitely like the > style. :) > > Cheers, > Pete > _______________________________________________ > Rails mailing list > Rails-1W37MKcQCpIf0INCOvqR/iCwEArCW2h5@public.gmane.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-- rick http://techno-weenie.net
Pete Yandell wrote: <snip>> First I need to get the count of messages in the folder to construct > the paginator. The best I can come up with is something like: > > count = Message.count(["message_id = id AND folder_id = ?", > @folder.id], "folders_message") > > Not too graceful given I''m basically generating my own SQL for the > relationship.I can''t speak to your paginator issue, but can''t the above be reduced to: count = Folder.find(@folder.id).messages.size Cheers, Lee
Updating my last post. (and pardon if this went through twice) Lee O''Mara wrote:> I can''t speak to your paginator issue, but can''t the above be reduced to: > > count = Folder.find(@folder.id).messages.sizeor (if @folder is a real Folder object): count = @folder.messages.size -- Lee
On 10/05/2005, at 2:04 PM, Lee O''Mara wrote:> Pete Yandell wrote: > <snip> > >> First I need to get the count of messages in the folder to >> construct the paginator. The best I can come up with is something >> like: >> count = Message.count(["message_id = id AND folder_id = ?", >> @folder.id], "folders_message") >> Not too graceful given I''m basically generating my own SQL for >> the relationship. >> > > I can''t speak to your paginator issue, but can''t the above be > reduced to: > > count = Folder.find(@folder.id).messages.sizeBeware! This will actually retrieve all the messages into an array and then take the size of the array. Try it and watch your developer logs. Has-many relationships have a count method, but there is no equivalent for habtm relationships. Pete Yandell
Thanks Rick and Peter for your examples. They were a big help. Below
is the code that I''ve settled on for now, with some comments about
rails along the way.
My Folder model class now looks like this.
class Folder < ActiveRecord::Base
has_and_belongs_to_many :messages, :order => "date DESC"
def count_messages
Message.count("id = folders_messages.message_id AND
folders_messages.folder_id = #{self.id}", "folders_messages")
end
def find_messages(*args)
options = if args.last.is_a?(Hash) then args.pop else {}
end
options[:joins] = "JOIN folders_messages ON messages.id
= folders_messages.message_id"
options[:conditions] = "folders_messages.folder_id = #
{self.id}" + (options[:conditions] ? " AND
#{options[:conditions]}" :
"")
Message.find(args.size == 1 ? args.first : args, options)
end
end
As pointed out by Lee O''Mara, the count_messages method is equivalent
to @folder.messages.size, but unfortunately the latter queries all
the messages into that array and then takes the size of the array.
Using Message.count generates a much more efficient SELECT COUNT(*)
query. Note that has_many relationships include a count method which
does a SELECT COUNT(*) query, but habtm relationships do not.
It seems confusing that the join argument to the count method and
the :join option to the find method have completely different
syntaxes. They also end up generating stylistically different SQL as
a result.
The find_messages method takes the usual set of find options and
modifies them with the necessary join to only query objects that are
part of the habtm association. This is a good first step towards a
generic version for integration into habtm, although it currently
ignores any other joins that are passed in as arguments.
All this means that my controller code can look like this:
def index
@folder = Folder.find @params["id"]
@message_pages = Paginator.new self, @folder.count_messages,
10, @params["page"]
@messages = @folder.find_messages :all, :offset =>
@message_pages.current_page.offset, :limit =>
@message_pages.items_per_page, :order => "date DESC"
end
This generates the following relatively neat SQL:
Folder Load (0.001301) SELECT * FROM folders WHERE folders.id =
''16'' LIMIT 1
Folder Columns (0.001694) SHOW FIELDS FROM folders
Message Count (0.001880) SELECT COUNT(*) FROM messages m ,
folders_messages WHERE id = folders_messages.message_id AND
folders_messages.folder_id = 16
Message Load (0.012449) SELECT * FROM messages JOIN
folders_messages ON messages.id = folders_messages.message_id WHERE
folders_messages.folder_id = 16 ORDER BY date DESC LIMIT 0, 10
Note that eager loading of the associated records would be very
difficult here because the outer joins used for this would not
interact well the LIMIT and OFFSET. Having one query for the folder
and another for the messages is about the best you can do.
Cheers,
Pete Yandell