Issue: Mixing hybrid properties with relationships
Author: LVerneyPEReN
State: closed
Created: 2019-12-03 14:20:43

Hi,

I'd like to have something similar to SQLAlchemy's hybrid with relationships example in Peewee. Typically, given the following simple models,

```
class Housing(BaseModel):
    id = peewee.AutoField()
 
    @hybrid_property
    def nb_nights(self):
        return sum(x.nb_nuitees for x in self.nuitees)

    @nb_nights.expression
    def nb_nights(cls):
        return peewee.fn.SUM(Nuitees.nb_nights)


class Nights(BaseModel):
    nb_nights = peewee.IntegerField()

    housing = peewee.ForeignKeyField(
        Housing, backref='nights'
    )
```

I'd like to be able to do queries such as `Housing.select().where(Housing.nb_nights == 2)` which would ideally result in a SQL query such as
```
SELECT housing.id, SUM(nights.nb_nights) AS nb_nights FROM housing JOIN nights ON nights.housing_id=housing.id WHERE nb_nights = 2 GROUP BY housing.id
```

I cannot find a way to do it with Peewee currently, not sure whether I am missing something or this is a bug somewhere. Is it achievable with Peewee?

Thanks!

EDIT: Note that the `@nb_nights.expression` is just provided here as a hint of what I'm expecting to find. I tried a couple of variations, including running subqueries etc without being able to come up with anything working fine.

It's pretty simple, I added a test-case showing how to do it:

```python
class Order(TestModel):
    name = TextField()

    @hybrid_property
    def quantity(self):
        return sum([item.qt for item in self.items])

    @quantity.expression
    def quantity(cls):
        return fn.SUM(Item.qt).alias('quantity')

class Item(TestModel):
    order = ForeignKeyField(Order, backref='items')
    qt = IntegerField()

data = (
    ('a', (4, 3, 2, 1)),
    ('b', (1000, 300, 30, 7)),
    ('c', ()))
for name, qts in data:
    o = Order.create(name=name)
    for qt in qts:
        Item.create(order=o, qt=qt)

query = Order.select().order_by(Order.name)
# NOTE: this loop issues an additional query for each order in the loop
# because it has to gather up all the order's items and count the quantities.
self.assertEqual([o.quantity for o in query], [10, 1337, 0])

query = (Order
         .select(Order.name, Order.quantity.alias('sql_qt'))
         .join(Item, JOIN.LEFT_OUTER)
         .group_by(Order.name)
         .order_by(Order.quantity.desc()))
self.assertEqual([o.sql_qt for o in query], [None, 1337, 10])
```

You probably omitted the join / group-by when using it in a query.

Note in case it is not obvious, but resolving the property on an order instance will issue a query to grab all that order's items and sum them in python. **It's hugely inefficient and should never be done.**

Thanks for the (very!) quick answer on this, this solves perfectly my need!

It would probably be a nice addition to http://docs.peewee-orm.com/en/latest/peewee/hacks.html (or any better place in the doc), as it deserves more emphasis from within the doc, in my opinion!

Thanks!

Glad to help. This is not something I would encourage simply because it can imply additional queries in a non-obvious way.