Does the following provide the results that you are looking for? db(db.question.id > 0)(db.keyword.keyword == 'this')(db.keyword.keyword == 'that').select()
-Thadeus On Sat, Apr 10, 2010 at 9:36 AM, mdipierro <[email protected]> wrote: > 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. >
