Sorry, try this
db(db.question.id == db.keyword.id)(db.keyword.keyword ==
'this')(db.keyword.keyword == 'that').select()
-Thadeus
On Sun, Apr 11, 2010 at 7:46 PM, Thadeus Burgess <thade...@thadeusb.com> wrote:
> 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 <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.
>>
>
