Any luck on the matter? THis 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, (SELECT mb_posts3.id, mb_posts3.title FROM mb_posts3 WHERE mb_posts3.reply_to=mb_posts.id ORDER BY mb_posts3.id DESC LIMIT 1), COUNT(mb_posts2.id) 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;
On Apr 5, 12:43 pm, TheDude <[email protected]> wrote: > 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 <[email protected]> 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 [email protected] To unsubscribe from this group, send email to [email protected] For more options, visit this group at http://groups.google.com/group/web2py?hl=en -~----------~----~----~----~------~----~------~--~---
