Re: [sqlalchemy] need help with query
I don't understand that error - you're asking it to join along predefined
relationships, so it shouldn't need to search for foreign keys at query
time. "Category.products" is a relationship set up as the backref of
Product.categories.
Here's a working example. I had to add the definition of the many-to-many
table.
import sqlalchemy as sa
from sqlalchemy import Column, Integer, UnicodeText, ForeignKey
import sqlalchemy.orm as saorm
from sqlalchemy.orm import relationship
from sqlalchemy.ext.declarative import declarative_base
Base = declarative_base()
class Brand(Base):
__tablename__ = 'brand'
id = Column(Integer, primary_key=True)
name = Column(UnicodeText, nullable=False)
slug = Column(UnicodeText, nullable=False, unique=True)
class Category(Base):
__tablename__ = 'category'
id = Column(Integer, primary_key=True)
name = Column(UnicodeText, nullable=False)
product_category = sa.Table(
'product_category',
Base.metadata,
Column('product_id', ForeignKey('product.id')),
Column('category_id', ForeignKey('category.id')),
)
class Product(Base):
__tablename__ = 'product'
id = Column(Integer, primary_key=True)
brand_id = Column(Integer, ForeignKey('brand.id'), nullable=False)
name = Column(UnicodeText, nullable=False)
brand = relationship('Brand', backref='products')
categories = relationship('Category', backref='products',
secondary=product_category) # many-to-many relationship
if __name__ == '__main__':
engine = sa.create_engine('sqlite://')
Base.metadata.create_all(bind=engine)
Session = saorm.sessionmaker(bind=engine)
session = Session()
brand_slug = 'some_slug'
q = session.query(Category).join('products',
'brand').filter(Brand.slug==brand_slug)
print q
print q.all()
On Wed, Mar 2, 2016 at 6:09 AM, sector119 wrote:
> With that query I get:
> InvalidRequestError: Could not find a FROM clause to join from. Tried
> joining to , but got: Can't find any
> foreign key relationships between 'category' and 'product'.
>
> Product and category model has many to many relationship
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
>
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] need help with query
With that query I get: InvalidRequestError: Could not find a FROM clause to join from. Tried joining to , but got: Can't find any foreign key relationships between 'category' and 'product'. Product and category model has many to many relationship -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at https://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] need help with query
http://docs.sqlalchemy.org/en/rel_1_0/orm/tutorial.html#querying-with-joins
You want something like:
DBSession.query(Category).join(‘products’,
‘brand’).filter(Brand.slug==brand_slug)
Hope that helps,
Simon
> On 1 Mar 2016, at 20:11, sector119 wrote:
>
> It works, but it's not pretty ))
>
>brand = DBSession.query(Brand).filter_by(slug=brand_slug).one()
> return
> DBSession.query(Category).filter(Category.overviews.any(brand_id=brand.id)).order_by(Category.name)
>
> вторник, 1 марта 2016 г., 21:52:47 UTC+2 пользователь sector119 написал:
> Hello,
>
> I need your help with one query, I want to get Category items that some
> Product's of some Brand have.
>
> I try DBSession.query(Category).filter(Category.overviews.any(Brand.slug ==
> brand_slug)).order_by(Category.name)
> But it doesn't work as expected because there is no relation between Brand
> and other tables
>
>
> class Brand(Base):
> __tablename__ = 'brand'
>
> id = Column(Integer, primary_key=True)
> name = Column(UnicodeText, nullable=False)
> slug = Column(UnicodeText, nullable=False, unique=True)
>
>
> class Category(Base):
> __tablename__ = 'category'
>
> id = Column(Integer, primary_key=True)
> name = Column(UnicodeText, nullable=False)
>
>
> class Product(Base):
> __tablename__ = 'product'
>
> id = Column(Integer, primary_key=True)
> brand_id = Column(Integer, ForeignKey('brand.id'), nullable=False)
>
> name = Column(UnicodeText, nullable=False)
>
> brand = relationship('Brand', backref='products')
> categories = relationship('Category', backref='products',
> secondary=product_category) # many-to-many relationship
>
>
>
>
> --
> You received this message because you are subscribed to the Google Groups
> "sqlalchemy" group.
> To unsubscribe from this group and stop receiving emails from it, send an
> email to sqlalchemy+unsubscr...@googlegroups.com.
> To post to this group, send email to sqlalchemy@googlegroups.com.
> Visit this group at https://groups.google.com/group/sqlalchemy.
> For more options, visit https://groups.google.com/d/optout.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
