yes and no. this NO
thisq = db(db.keyword.keyword == 'this')._select(db.keyword.question) result = db(db.question.id.belongs(thisq)).select(db.question.ALL) but this YES thisq = [r.question for r in db(db.keyword.keyword == 'this').select(db.keyword.question)] result = db(db.question.id.belongs(thisq)).select(db.question.ALL) You can use the other trick on other places but I am not sure how bug thisq can get. On Apr 11, 8:09 pm, Paul Wray <[email protected]> wrote: > Please disregard last question - nested select does seem to work using > the same chaining technique: > > thisq = db(db.keyword.keyword == 'this')._select(db.keyword.question) > thatq = db(db.keyword.keyword == 'that') > (db.keyword.belongs(thisq))._select(db.keyword.question) > result = db(db.question.id.belongs(thatQ)).select(db.question.ALL) > > I guess none of these would work on GAE though, would they? > > Paul > > On Apr 12, 10:40 am, Paul Wray <[email protected]> wrote: > > > Thank you Massimo > > > I didnt know about the chained queries like this ie db(q1)(q2) . > > Is this feature documented in the book? > > > I like how this allows a neat construction for n keywords: > > > q = db() > > for k in keywords: > > q = q(db.question.id.belongs(db(db.keyword.keyword = > > k).select(db.qkeyword.question))) > > > result = q.select(db.question.ALL) > > > I am surprised that the IN would be more efficient than a join. Are > > you sure about this? > > > One last question on this if I may: could I do this in web2py using > > nested selects? > > The following does not work, but I'd like to be able to do something > > like: > > > thisq = db(db.keyword.keyword == 'this')._select(db.keyword.question) > > thatq = db((db.keyword.keyword == 'that') & > > db.keyword.belongs(thisq))._select(db.keyword.question) > > result = db(db.question.id.belongs(thatQ)).select(db.question.ALL) > > > Thanks again > > > On Apr 11, 12: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.
