Skip to content

Instantly share code, notes, and snippets.

@tachyondecay

tachyondecay/models.py

Last active February 5, 2016 01:02
Show Gist options
  • Save tachyondecay/cb380e426fc87a81dbf4 to your computer and use it in GitHub Desktop.
Save tachyondecay/cb380e426fc87a81dbf4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
models.py
class Supplier(db.Model):
"""A supplier of consignment items."""
__tablename__ = 'consignment_suppliers'
id = db.Column(db.Integer, primary_key=True)
supplier_name = db.Column(db.String(200),
unique=True,
info={'label': 'Business name',
'filters': [lambda x: x or None]})
person_id = db.Column(db.Integer, db.ForeignKey('person.id'))
person = db.relationship('Person', uselist=False)
products = db.relationship('Product',
backref=db.backref('supplier', lazy='joined'),
lazy='dynamic')
@property
def name(self):
"""Harmonizes suppliers with business names vs personal names."""
if self.supplier_name:
return self.supplier_name
return self.person.name
# Example call:
items = Product.query.join(Supplier).join(Person).order_by(asc(func.coalesce(Supplier.supplier_name, Person.name))).all()
Raw
output.sql
sqlalchemy.exc.OperationalError: (sqlite3.OperationalError) no such column: consignment_suppliers.supplier_name [SQL: 'SELECT consignment_products.id AS consignment_products_id, consignment_products.supplier_id AS consignment_products_supplier_id, consignment_products.description AS consignment_products_description, consignment_products.quantity AS consignment_products_quantity, consignment_products.created AS consignment_products_created, consignment_products.updated AS consignment_products_updated, consignment_products.price AS consignment_products_price, consignment_products.rate AS consignment_products_rate, consignment_suppliers_1.id AS consignment_suppliers_1_id, consignment_suppliers_1.supplier_name AS consignment_suppliers_1_supplier_name, consignment_suppliers_1.person_id AS consignment_suppliers_1_person_id, consignment_suppliers_1.created AS consignment_suppliers_1_created, consignment_suppliers_1.updated AS consignment_suppliers_1_updated, consignment_suppliers_1.active AS consignment_suppliers_1_active, consignment_suppliers_1.hst AS consignment_suppliers_1_hst, consignment_suppliers_1.rate AS consignment_suppliers_1_rate, consignment_suppliers_1.notes AS consignment_suppliers_1_notes, consignment_suppliers_1.earned AS consignment_suppliers_1_earned \nFROM consignment_products LEFT OUTER JOIN consignment_suppliers AS consignment_suppliers_1 ON consignment_suppliers_1.id = consignment_products.supplier_id ORDER BY coalesce(consignment_suppliers.supplier_name, person.name) ASC, lower(consignment_products.description) ASC\n LIMIT ? OFFSET ?'] [parameters: (50, 0)]
Raw
raw.sql
SELECT consignment_products.*,
COALESCE(consignment_suppliers.supplier_name, person.name) AS supplier_name
FROM consignment_products, person JOIN consignment_suppliers ON consignment_suppliers.id == consignment_products.supplier_id
WHERE person.id == consignment_suppliers.person_id;
@tachyondecay
Copy link
Author

Trying to order a query by a hybrid property attribute, but I can't get the expression right.

I want the query as seen in raw.sql but am currently getting a NotImplementedError. Not sure if I need to add a join on to line 26, or if I need to use a fancier select statement, or what.

Database is SQLite if that's important.

@tachyondecay
Copy link
Author

For reference of any people led here by a search:

Line 32 is an incorrect call—my view has two cases, one where we're looking a specific supplier and one where we're looking at all products from all suppliers. I needed to use a join. When I did this, everything works without a hybrid property.

The most recent revision of the gist has been updated to show the working code.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment