Forum: Ruby on Rails Paginate - Count(*) Broken or Am I Cheating?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
lists (Guest)
on 2005-12-10 02:25
(Received via mailing list)
I have the following paginator setup:

@merch_pages, @merch = paginate :merch_item, :per_page => 15, :select =>
'DISTINCT mi.id, mi.merch_image, mi.created_at, mi.artist_id,
mi.item_description, art.sort_name', :join => 'as mi JOIN merch_formats
mf
ON mi.id = mf.merch_item_id JOIN artists art ON mi.artist_id = art.id',
:order => 'art.sort_name', :conditions => ["mf.active_status = 1 AND
format_name = ? AND format_name <> 'Poster'", params[:id]]

This part works like a charm... Problem is that the COUNT(*) query that
is
executed doesn't include the DISTINCT, so the paginator thinks there are
7
pages of results but there are only three.

Is this a bug? It seems strange that the two operations would have
different
limiting factors.

Perhaps I am cheating by putting DISTINCT into the :select option. If
so, is
there another way to handle this?

You can see the generated queries below:

LOG:  statement: SELECT COUNT(*) FROM merch_items  as mi JOIN
merch_formats
mf ON mi.id = mf.merch_item_id JOIN artists art ON mi.artist_id = art.id
WHERE (mf.active_status = 1 AND format_name = 'T-Shirt' AND format_name
<>
'Poster')

LOG:  statement: SELECT DISTINCT mi.id, mi.merch_image, mi.created_at,
mi.artist_id, mi.item_description, art.sort_name FROM merch_items  as mi
JOIN merch_formats mf ON mi.id = mf.merch_item_id JOIN artists art ON
mi.artist_id = art.id WHERE (mf.active_status = 1 AND format_name =
'T-Shirt' AND format_name <> 'Poster')  ORDER BY art.sort_name  LIMIT 15
OFFSET 45

Cheers.
This topic is locked and can not be replied to.