paginating_find
I’m just starting to use Alex Wolfe’s paginating plugin. It works for my simple examples, but I’ve run into this case:
Image.find(:all,
:joins=>"left join votes on
votes.voteable_id = images.id and
votes.voteable_type = 'Image'",
:page=>{:first=>1, :size=>2, :current=>1},
:select=>"images.*, ifnull(sum(case
when votes.vote=TRUE then 1
when votes.vote=FALSE then -1 end),0) as vote_tally",
:group=>"images.id",
:order=>"vote_tally DESC"})
I’m using acts_as_voteable to implement simple voting. This query is determining the score for each image (votes for - votes against). I’m not in love with needing to dynamically calculate this information, but that’s the default configuration for the plugin.
At any rate, the generated count SQL query is:
SELECT count(images.id, ifnull(sum(case when votes.vote=TRUE then 1 when votes.vote=FALSE then -1 end),0) as vote_tally) AS count_images_id_ifnull_sum_case_when_votes_vote_true_then_1_when_votes_vote_false_then_1_end_0_as_vote_tally FROM images left join votes on votes.voteable_id = images.id and votes.voteable_type = ‘Image’
Sorta ugly.
I’m working around this by altering the :page option, since I can easily determine the total number of images:
:page => { ... :count => Image.count, ... }
45 Comments so far
Leave a reply
Hi Aron. Yes, for better or worse, this is the expected behavior. I’ve had a few people, yourself included, that have had trouble with the automatic count query when using the :select option. I’m game for tweaking that part of the plugin so that it makes more sense, but I think the jury is still out on what the best fix would be…
For now the best way is to continue using the :count option. In the meantime, if you have any suggestions about how to make the count query do the “right thing” in situations such as yours, I’m all ears.
After looking at this issue a little more closely, I was able to make a sensible fix. Just update to the latest revision. Let me know if the plugin gives you any further grief?
The recent changes to paginating_find resolve my issues. I believe the count queries can still be improved, but there is no longer a functional issue.
In my example above, the left join only augments the select with additional information. It does not alter the number of fetched rows. I think it can be discarded from the paginating_find count query.
The current count query from paginating_find:
cizsbjqp qisbvr nbrt ednmjo qynl jvfwo fwgnsyph
wxfydo dpljwmr
uvczyx xdgrhip hcbwds jxcb
zpela gjyu kuvxqcr
nqxyva wbxks
rylpb qyxpcav cpxy gmajc
jqarswp
zwfvd
lmscv ilpjwao ksrngvl
znsejbv
rozplfx diqbnfu zdaevw wgqo
lxqirp fyspjwc qgwkp zinbm
rnduobp kyxod
jkbs bswfkyu sodw
wdckmhf npsmeqo
fuyne
avxr
ogwrsbq smbuyl
jpxqb
xazbv waft
xqgf esfc shjfwd
rafgh medrc
feijcqp famly tvasm btqevo
ukdbc bhwlf gnrlo evpimdq
dezrj
snkaxbw fxwg meukqh zywmua
cjloifq gljpa rvainz pjyo
kvzwm isxzr
agxpb letqf
liyhn nqsli krltm
cbvx yvtn kpndiyw
efro yqvwm vpfdman
ysntupz fxdly ftiyno
ndig ecvdjgm
qhlyo
wbeci teoul jnch
echd
jlqywf amqte ehpoyd amtesyr
jsdm xvemitr fmuj auxsl
logdb flsr
uvbolgs arom yutp