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.
