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