[Sqlalchemy-users] Select_by on a special mapper

[Koen Bok]

Hi all,






Thanks!
Koen


#/bin/python

from sqlalchemy import *
import sqlalchemy.pool as pool

engine = create_engine('sqlite://:memory:', echo=False)
session = create_session(bind_to=engine)

metadata = DynamicMetaData()

products = Table('products', metadata,
    Column('id', Integer, Sequence('products_id_seq',optional=False), primary_key=True),
    Column('code', Unicode(255), nullable=False),
    Column('name', Unicode(255), nullable=False)
)

stocks = Table('stocks', metadata,
    Column('id', Integer, Sequence('stocks_id_seq',optional=False), primary_key=True),
    Column('name', Unicode(255), nullable=False)
)

stockproducts = Table('stockproducts', metadata,
    Column('id', Integer, Sequence('stockproducts_id_seq',optional=False), primary_key=True),
	Column('product_id', Integer, ForeignKey("products.id"), nullable=False),
	Column('stock_id', Integer, ForeignKey("stocks.id"), nullable=False)
)

metadata.create_all(engine=engine)

class Product(object):
	pass

class Stock(object):
	pass

class StockProduct(object):
	pass

mapper(StockProduct, stockproducts)

mapper(Product, products, properties={
	'stockproducts': relation(StockProduct, lazy=False, cascade="all, delete-orphan", backref='product')})

mapper(Stock, stocks, properties={
    'stockproducts': relation(StockProduct, lazy=False, cascade="all, delete-orphan", backref='stock')})


p1 = Product()
p1.code = 'Test123'
p1.name = 'Testproduct'
session.save(p1)

s1 = Stock()
s1.name = 'Main'
session.save(s1)

s2 = Stock()
s2.name = 'Returned'
session.save(s2)

sp1 = StockProduct()
sp1.product = p1
sp1.stock = s1
session.save(sp1)

sp2 = StockProduct()
sp2.product = p1
sp2.stock = s2
session.save(sp1)

session.flush()
session.clear()

# Define a mapper to add a stock_count column
s = select([products, 
	func.count(stockproducts.c.id).label('stock_count')],
	and_(stockproducts.c.product_id==products.c.id,
	stockproducts.c.stock_id==stocks.c.id),
	group_by=[c for c in products.c]).alias('count_select')

product_stock_mapper = mapper(Product, s, non_primary=True)

result = session.query(product_stock_mapper).select()

# We expect to get the number of total stockproducts back: 2
print "Total stock (expected: 2):", result[0].stock_count

result = session.query(product_stock_mapper).select_by(stocks.c.id==s1.id)

# We expect to get the number of stockproducts for stock 1 back: 1
print "Stock in stock 2 (expected: 1):", result[0].stock_count

# Why does the above not return one? And how can I make it do that?

metadata.drop_all(engine=engine)

I have three tables. Products, StockProducts and Stocks. For each product stock a stock product is created. To get the total stock for a product we do a count() for the related stockproducts. I got this to work with a mapper object. Now I'd like to filter these by a certain stock, but I can't get it to work. I do a select_by on the mapper, but it keeps returning the total stockproducts. I included a fully working test script which exactly describes my problem. What am I doing wrong!?

smime.p7s
Description: S/MIME cryptographic signature

_______________________________________________
Sqlalchemy-users mailing list
Sqlalchemy-users@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/sqlalchemy-users