with_entities improvement

[Cyrex Cyborg]

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?

[Mike Bayer]

if Product.category.name implies some kind of automatic JOIN or correlated subquery, then no. What is the SQL emitted by the above?

[Cyrex Cyborg]

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.

[Mike Bayer]

if "category" is the name of a database column, then what is "models.Product.category.name" ?

[Cyrex Cyborg]

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

[Mike Bayer]

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.

[Cyrex Cyborg]

Due to incorrect use of function