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.
