Re: [SQLObject] Help with SQLBuilder NOTIN

[Oleg B. <ph...@ph...>]

On Wed, Mar 31, 2010 at 08:37:32AM +0800, Jason Heeris wrote:
> my_selection = dbase.Stock.select(
>                     sqlobject.sqlbuilder.AND(
>                         dbase.Stock.q.dbStockCode.startswith("XXX"),
>                         sqlobject.sqlbuilder.NOTIN(
>                                 sqlobject.sqlbuilder.Outer(dbase.Stock).q.id,
> 
> sqlobject.sqlbuilder.Select(dbase.ContItem.q.dbStock)))
> 
> ... gives exactly the same error though.

   The following code

from sqlobject import *
from sqlobject.sqlbuilder import *

__connection__ = "sqlite:/:memory:?debug=1"

class Stock(SQLObject) :
    dbStockCode = StringCol()

class ContItem(SQLObject) :
    dbStock = ForeignKey('Stock')
    dbQuantity = IntCol()

Stock.createTable()
ContItem.createTable()

print list(Stock.select(
    AND(Stock.q.dbStockCode.startswith("XXX"),
    NOTIN(Outer(Stock).q.id,
        Select(ContItem.q.dbStock)))))

   works for me; the query is

 3/QueryR  :  SELECT stock.id, stock.db_stock_code FROM stock WHERE ((stock.db_stock_code LIKE ('XXX%') ESCAPE '\') AND (stock.id NOT IN (SELECT cont_item.db_stock_id FROM cont_item)))

Oleg.
-- 
     Oleg Broytman            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.