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 -~----------~----~----~----~------~----~------~--~---
