[storm] Problem with EXISTS clause

[Jamu Kakar]

Hi Edoardo,

On Mon, Sep 27, 2010 at 6:07 PM, Edoardo Serra <edoardo at serra.to.it> wrote:
> I tried the following python code
>
> accounts = store.find(Account,
>    SQL.Not(SQL.Exists(SQL.Select(Subscription.id,
>        Subscription.account_id==Account.id)))).count()
>
> but it gets translated to the following SQL:
>
> SELECT COUNT(*) FROM accounts WHERE NOT EXISTS (SELECT subscriptions.id FROM accounts, subscriptions WHERE subscriptions.account_id = accounts.id)
>
> which does not give me the expected result, the accounts table should be removed from the FROM clause in the subquery.
>
> Any suggestion?

I believe you need to pass an explicit list of tables to the Select
expression, to override the logic that automatically determines
which tables to include in the FROM clause:

subselect = Select(Subscription.id,
                   Subscription.account_id == Account.id,
                   [Subscription])
accounts = store.find(Account, Not(Exists(subselect))).count()

Thanks,
J.