I would do db(db.question.id.belongs(db(db.keyword.keyword=='this'))._select(db.keyword.question)) (db.question.id.belongs(db(db.keyword.keyword=='that'))._select(db.keyword.question)).select(db.problem.ALL)
It will be faster. On Apr 10, 8:31 am, Paul Wray <[email protected]> wrote: > Thanks for your reply > > The SQL I wish to produce is: > 'SELECT question.* FROM question, keyword as k2, keyword as k1 WHERE > ((k1.keyword="this") AND (k2.keyword="that") AND > (k1.question=k2.question) AND (k1.question=question.id))' > That is, find all questions that have both the keyword 'this' and the > keyword 'that' (for example). > > (I checked this query using executesql and it seems to work as > intended). > > The problem I am solving: > I have a table of questions called 'question' tagged with keywords > stored in a second table 'keyword': > > db.define_table('question', > # Fields not important to the problem > ) > > db.define_table('keyword', > Field('question', db.question), > Field('keyword') > ) > > I dont have deep knowledge of SQL, so its quite possible I am missing > a better way. > > Paul > > On Apr 9, 1:40 pm, mdipierro <[email protected]> wrote: > > > Inner joins in DAL do not support AS. Can you show an SQL example of > > how you would use it? > > > Massimo > > > On Apr 8, 10:08 pm, Paul Wray <[email protected]> wrote: > > > > Hello > > > > I'm attempting a simple self join and having trouble with the aliases. > > > The slightly simplified query is: > > > > k1 = db.qkeyword.with_alias('k1') > > > k1 = db.qkeyword.with_alias('k2') > > > print db( (k1.keyword == 'this') > > > & (k2.keyword == 'that') > > > & (k1.question == k2.question) > > > & (k1.question == db.question.id))._select(db.question.ALL) > > > > The generated query looks correct except that k1 and k2 are > > > interpreted as existing table names, not aliases (ie no AS clause is > > > present). > > > > The only examples of aliases I have seen have used the left join, so I > > > suspect I need to somehow use it here too? > > > > Also, I'd love to read a more detailed description of the DAL if one > > > exists. The book provides examples, but does not contain sufficient > > > detail to give you a mental model of what is going on, and so > > > generalise from the examples. -- To unsubscribe, reply using "remove me" as the subject.
