 | |
#1
 
| |||
| |||
|
Pagination of joined table -
01-25-2009
, 08:23 PM
#2
| |||
| |||
|
|
Mysql 5.0.22, I would think this is an ancient question but I haven't found an answer for it in my searches. Think of a weblog, with comments under each daily entry. (which isn't actually my use case, but same relationships.) So, Weblogs ------------------------------- weblogid | title | body | postdate Comments commentid | weblogid | title | comment What I want to do is display a page full of weblog posts, with the first 10 comments under each one. Seems simple enough, right? just do an inner join. But- what if the posts have 2000 comments each, and need to be paginated? (Again, I'm only using weblogs since it's familiar, this could be any similar case). Or 10000 comments per post. How do I do one select with an inner join, but only get the first 10 for each. So that each weblog post has the first 10 comments by date underneath it? OFFSET, LIMIT doesn't help me here, as far as I can see, unless this is somehow possible with a mapped subselect or something like that? I could do it without the join and just map over the results doing individual queries for each post, but isn't that the biggest no-no in database land? Also I'd rather not write stored functions (is that what they're called?), if possible, since this needs to be portable. (It's sad if the only way to do this is custom functions, it's such a common need of applications). thanks Your comments don't have a date attribute. How will you know which ones belong in the first 10? |
#3
| |||
| |||
|
|
"Flexor" <yugyu... (AT) hotmail (DOT) com> wrote in message news:886f0c70-01d8-4813-8f33-be9c88e38d1a (AT) v15g2000vbb (DOT) googlegroups.com... Mysql 5.0.22, I would think this is an ancient question but I haven't found an answer for it in my searches. Think of a weblog, with comments under each daily entry. (which isn't actually my use case, but same relationships.) So, Weblogs ------------------------------- weblogid | title | body | postdate Comments commentid | weblogid | title | comment What I want to do is display a page full of weblog posts, with the first 10 comments under each one. Seems simple enough, right? just do an inner join. But- what if the posts have 2000 comments each, and need to be paginated? (Again, I'm only using weblogs since it's familiar, this could be any similar case). Or 10000 comments per post. How do I do one select with an inner join, but only get the first 10 for each. So that each weblog post has the first 10 comments by date underneath it? OFFSET, LIMIT doesn't help me here, as far as I can see, unless this is somehow possible with a mapped subselect or something like that? I could do it without the join and just map over the results doing individual queries for each post, but isn't that the biggest no-no in database land? Also I'd rather not write stored functions (is that what they're called?), if possible, since this needs to be portable. (It's sad if the only way to do this is custom functions, it's such a common need of applications). thanks Your comments don't have a date attribute. How will you know which ones belong in the first 10? |
first 10 by id number. It doesn't matter, whatever.
#4
| |||
| |||
|
|
Mysql 5.0.22, I would think this is an ancient question but I haven't found an answer for it in my searches. Think of a weblog, with comments under each daily entry. (which isn't actually my use case, but same relationships.) So, Weblogs ------------------------------- weblogid | title *| body | postdate Comments commentid | weblogid | title | comment What I want to do is display a page full of weblog posts, with the first 10 comments under each one. Seems simple enough, right? just do an inner join. But- what if the posts have 2000 comments each, and need to be paginated? *(Again, I'm only using weblogs since it's familiar, this could be any similar case). *Or 10000 comments per post. How do I do one select with an inner join, but only get the first 10 for each. *So that each weblog post has the first 10 comments by date underneath it? OFFSET, LIMIT doesn't help me here, as far as I can see, unless this is somehow possible with a mapped subselect or something like that? I could do it without the join and just map over the results doing individual queries for each post, but isn't that the biggest no-no in database land? Also I'd rather not write stored functions (is that what they're called?), if possible, since this needs to be portable. *(It's sad if the only way to do this is custom functions, it's such a common need of applications). thanks |
HTH
/Lennart
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.