1. Michael Bayer
  2. sqlalchemy
  3. Issues


Issue #3142 closed

with_entities improvement

Cyrex Cyborg
created an issue

Hello there! I'm looking the way how to access related object field with use of with_entities method. I want to use this like

lim_results = self.product.query.with_entities(models.Product.category.name).all()

Could i override this method?

Comments (6)

  1. Cyrex Cyborg reporter

    I think it must be

    SELECT product.category FROM product

    but idk how to select only category.name.Then it must be

    lim_results = self.product.query.with_entities(models.Category.name).all()

    I'm just trying to reduce unexpected columns. I saw a source code of with_entities, it really hard to implement what i've expect.

  2. Cyrex Cyborg reporter

    models.Products.category.name is related to models.Category.name.

    class Product(db.Model):
        __tablename__ = 'product'
        def __unicode__(self):
            return self.product_name
        id = db.Column(db.Integer, primary_key=True)
        product_name = db.Column(db.Unicode(100), nullable=False)
        category_id = db.Column(db.Integer, db.ForeignKey('category.id'))
        category = db.relationship('Category')
    class Category(db.Model):
        __tablename__ = 'category'
        def __unicode__(self):
            return self.category_name
        id = db.Column(db.Integer, primary_key=True)
        category_name = db.Column(db.Unicode(100), nullable=False)

    So when i querying values from product, i can type product.category.category_name like this

    result = models.Product.query.all()
    print result[0].category.category_name
  3. Michael Bayer repo owner

    yeah that implies a JOIN and that's not how this works, sorry. "category" isn't a database column so "SELECT product.category FROM product" isn't the SQL. "SELECT category_name FROM category JOIN product ON category.id = product.category_id" is what's called for, this is joining as introduced at http://docs.sqlalchemy.org/en/rel_0_9/orm/tutorial.html#querying-with-joins. SQLAlchemy is literal about SQL. if you want the super automatic stuff and not want to be exposed to SQL you'd need to use the django ORM.

  4. Log in to comment