StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POsqlalchemy sql composition
text
Body
copied!<p>I am working on a codebase that uses sqlalchemy extensively and I am new to it. I am having trouble composing a sqlalchemy expression for a query that I want to write.</p> <p>We have the following 3 tables:</p> <ol> <li>Product - (product_id, product_description, status, available)</li> <li>Category - (category_id, category_description)</li> <li>ProductCategoryLink - (product_id, category_id) [For many-to-many relationship]</li> </ol> <p>I am running a query that gives me a product count per category. Some products are not assigned to any category and I want those products as well (category will be null in that case). I came up with the following query</p> <pre><code>select c.category_name, count(p.product_id) from Product as p left join ProductCategoryLink as pc on p.product_id = pc.product_id left join Category as c on c.category_id = pc.category_id where p.store_id = 1111 and p.status = 'ACTIVE' and p.available = 1 group by c.category_name; </code></pre> <p>I have the following mappings in my orm file</p> <pre><code>class Product(Base): __tablename__ = 'Product' product_id = Column(Integer, primary_key=True, name='product_id') product_description = Column(UnicodeText, name='description') available = Column(Boolean, name='available') status = Column(Unicode(length=255), name='status') metadata = Base.metadata # association table between product and category product_category_link = Table('ProductCategoryLink', metadata, Column('product_id', Integer, ForeignKey('Product.product_id')), Column('category_id', Integer, ForeignKey('Category.category_id')) ) class Category(Base): __tablename__ = 'Category' category_id = Column(Integer, primary_key=True, name='category_id') category_name = Column(Unicode(length=255), name='category_name') products = relation('Product', secondary=product_category_link, backref='categories') </code></pre> <p>I came up with the following ORM expression</p> <pre><code> query = session.query(Category.category_name, func.count(Product.product_id)).join(product_category_link).\ join(Category).filter( and_(Product.store_id == self._store_id, and_(Product.status == 'ACTIVE', Product.available == 1))).\ group_by(Category.category_name).all() </code></pre> <p>The sql query that the above expression creates is not what I want.</p> <pre><code>sqlalchemy.exc.OperationalError: (OperationalError) (1066, "Not unique table/alias: 'Category'") 'SELECT `Category`.category_name AS `Category_category_name`, count(`Product`.product_id) AS count_1 \nFROM `Product`, `Category` INNER JOIN `ProductCategoryLink` ON `Category`.category_id = `ProductCategoryLink`.category_id INNER JOIN `Category` ON `Category`.category_id = `ProductCategoryLink`.category_id \nWHERE `Product`.store_id = %s AND `Product`.status = %s AND `Product`.available = %s GROUP BY `Category`.category_name' (1, 'ACTIVE', 1) </code></pre> <p>What am I doing wrong here?</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload