Running Web2py 1.99.7 and using a model based on the wiki in the book (http://web2py.com/books/default/chapter/29/3#A-wiki). I want to report a unique list of comments for a particular webpage, for a particular tag (I've added a many-to-many tag table). I use a form to build a query, which ends up like this:
db_query = "(((((comment.id > 0) AND (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id)) AND (((comment.id = comment_tags.comment_id) AND (tag.id = comment_tags.tag_id)) AND (tag.label = 'Agree'))))" However, the select gives me an "psycopg2.ProgrammingError: missing FROM-clause entry" because only one of the required tables has been included in the SQL FROM clause: >>>db(db_query)._select(db.comment.body, distinct=True, orderby=db.comment.body) "SELECT DISTINCT comment.body FROM comment WHERE (((((comment.id > 0) AND (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id)) AND (((comment.id = comment_tags.comment_id) AND (tag.id = comment_tags.tag_id)) AND (tag.label = 'Agree')))) ORDER BY comment.body;" I can get rid of the error by including all the tables in the select, but that gives me duplicates: >>>db(db_query)._select(db.comment.body, db.comment_tags.id, db.tag.id, >>>db.webpage.id, distinct=True, orderby=db.comment.body) "SELECT DISTINCT comment.body, comment_tags.id, tag.id, webpage.id FROM comment, comment_tags, tag, webpage WHERE (((((comment.id > 0) AND (webpage.title = 'FQA')) AND (comment.page_id = webpage.id)) AND (((comment.id = comment_tags.comment_id) AND (tag.id = comment_tags.tag_id)) AND (tag.label = 'Agree')))) ORDER BY comment.body;" The SQL I want (which seems valid enough) is this: "SELECT DISTINCT comment.body FROM comment, comment_tags, tag, webpage WHERE (((((comment.id > 0) AND (webpage.title = 'FAQ')) AND (comment.page_id = webpage.id)) AND (((comment.id = comment_tags.comment_id) AND (tag.id = comment_tags.tag_id)) AND (tag.label = 'Agree')))) ORDER BY comment.body;" How do I get the DAL to do this? What am I missing here?
