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 <paul.w...@det.nsw.edu.au> 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 <mdipie...@cs.depaul.edu> 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 <paul.w...@det.nsw.edu.au> 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 <mdipie...@cs.depaul.edu> 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 <paul.w...@det.nsw.edu.au> 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.
