Don't know if the google groups posted this already but lets try again.. Models: http://pastie.org/private/kgrvhedmnq1mm9jivc1h3w
Controller: http://pastie.org/private/9sycnfi3b4xz4ij5m1d7na Problem: Trying to select the last mb_posts3.reply_to==mb_posts.id..JUST the last one. Massimo, That SQL I posted was from me, not generated by Web2Py. On Apr 5, 12:10 pm, Michal Jursa <mic...@jursa.cz> wrote: > Repost from IRC conversation: > > 16:27:47 <draginx> basically im getting a list of topics with a specific > board_id > 16:28:06 <draginx> within the same query, I'm trying to retrieve the > latest reply to each topic > > Michal alias Plysak > > mdipierro wrote: > > It is very hard for me (and everybody) to debug this without having a > > copy of the model and without understanding what this is supposed to > > do. Could you explain what you are trying to do? > > > Is the querly below in SQL the one that "you are trying to perform" or > > the one that you are actually performing and does not work? > > Is it the output of db(...)._select()? > > > Massimo > > > On Apr 5, 10:47 am, TheDude <officed...@gmail.com> wrote: > >> Thanks guys :) It's been really bogging down my productivity within > >> the website, but here is the query that I am trying to perform.. > > >> SELECT mb_posts.id, mb_posts.title, mb_posts.message, > >> mb_posts.posted_by, mb_posts.date_posted, mb_posts.locked, > >> mb_posts.sticky, mb_posts.last_edited, mb_posts.board_id, > >> mb_posts.reply_to, mb_posts.views, auth_user.id, auth_user.first_name, > >> auth_user.last_name, auth_user.email, auth_user.password, > >> auth_user.registration_key, COUNT(mb_posts2.id), (SELECT mb_posts.id > >> FROM mb_posts AS mb_posts3 WHERE mb_posts3.reply_to=mb_posts.id ORDER > >> BY > >> mb_posts3.id DESC LIMIT 1) FROM mb_posts LEFT JOIN > >> auth_user ON auth_user.id=mb_posts.posted_by LEFT JOIN mb_posts AS > >> mb_posts2 ON mb_posts2.reply_to=mb_posts.id WHERE (mb_posts.board_id=1 > >> AND mb_posts.reply_to IS NULL) GROUP BY mb_posts.id > > >> Hope that makes a bit more sense. :) Everything in the qery works fine > >> except for mb_posts3 info. I'm trying to select the latest reply > >> created to that thread. > > >> On Apr 5, 11:26 am, Michal Jursa <mic...@jursa.cz> wrote: > > >>> I already tried that removement of the semicolon directly in the query > >>> and that still doesn't work. To say true i cannot get the sense of the > >>> query so i cannot clearly debug it coz i never used subselects in field > >>> list. > >>> Michal alias Plysak > >>> mdipierro wrote: > >>>> I see the problem. > >>>> The structure of that query is > >>>> db(....).select(...,db(...)._select(...),...) # wrong > >>>> The DAL is not designed to do this because it is not a good idea (as > >>>> Michal points out). You may be able to do it anyway > >>>> db(....).select(...,db(...)._select(...)[:-1],...) # wrong? > >>>> But it may still not work. nested selects should go in the query, not > >>>> in the list of fields to be selected. You may need to refactor your > >>>> select. Perhaps if you explain in words what you are trying to select > >>>> we can help more. > >>>> Massimo > >>>> On Apr 5, 9:27 am, Michal Jursa <mic...@jursa.cz> wrote: > >>>>> I'm getting interested in this, coz that query is strange, but what that > >>>>> DAL call generates is: > >>>>> SELECT mb_posts.id, mb_posts.title, mb_posts.message, > >>>>> mb_posts.posted_by, mb_posts.date_posted, mb_posts.locked, > >>>>> mb_posts.sticky, mb_posts.last_edited, mb_posts.board_id, > >>>>> mb_posts.reply_to, mb_posts.views, auth_user.id, auth_user.first_name, > >>>>> auth_user.last_name, auth_user.email, auth_user.password, > >>>>> auth_user.registration_key, COUNT(mb_posts2.id), SELECT mb_posts3.id > >>>>> FROM mb_posts3, mb_posts WHERE mb_posts3.reply_to=mb_posts.id ORDER BY > >>>>> mb_posts3.id DESC LIMIT 1 OFFSET 0; FROM mb_posts, mb_posts3 LEFT JOIN > >>>>> auth_user ON auth_user.id=mb_posts.posted_by LEFT JOIN mb_posts AS > >>>>> mb_posts2 ON mb_posts2.reply_to=mb_posts.id WHERE (mb_posts.board_id=1 > >>>>> AND mb_posts.reply_to IS NULL) GROUP BY mb_posts.id > >>>>> So the first thing is there is a ';' sighn after OFFSET statement in the > >>>>> middle of query and the second thing is that this is not the way > >>>>> subselects are supposed to be used. Try to examine it further, i'm > >>>>> getting a bit lost in it. I think it is terribly complicated for the > >>>>> functionality it should provide. > >>>>> Plysak --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "web2py Web Framework" group. To post to this group, send email to web2py@googlegroups.com To unsubscribe from this group, send email to web2py+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
