SQLAlchemy reference a subquery from a case expression

Question

I have a hybrid_property that returns a string based on some calculations made on a one-to-many relationship.

The raw sql for the hybrid_property expression is: Here's the raw sql:

SELECT
  CASE
    WHEN s.quantity_received = 0 THEN "unreceived"
    WHEN s.dif = 0.0  THEN "received"
    WHEN s.dif > 0.0  THEN "partially_received"
    WHEN s.dif < 0.0  THEN "over_received"
  END as status
FROM (
      SELECT li.quantity_received, sum(li.quantity - li.received) as 'dif'
      FROM line_items as li
      WHERE li.o_id = xxx
) as s

The Models

class LineItem(BaseModel):

    __table__ = Table('line_items', autoload=True)

   order = relationship("Order", backreef="line_itms", primaryjoin="Order.id == foregin(LineItem.o_id)")


class Order(BaseModel):

    __table__ = Table('orders', autoload=True)

    @hybrid_property
    def status(self):
        qty_received, qty_ordered = 0, 0

        for li in self.line_items:
            if li.status != "cancelled":
                qty_ordered += li.quantity
                qty_received += li.quantity_received

        if qty_received == 0:
            status = "unreceived"

        elif qty_received == qty_ordered:
            status = "received"

        elif qty_received < qty_ordered:
            status = "partially_received"

        elif qty_received > qty_ordered:
            status = "over_received"

        return status


    @status.expression
    def status(cls):
        line_items_calc = select([LineItem.quantity_received,
                    func.sum(LineItem.quantity - LineItem.quantity_received).label('dif')]) \
            .where(and_(LineItem.o_id == Order.id,
                   or_(LineItem.fulfillment_status != "cancelled",
                       LineItem.fulfillment_status == None))) \
            .alias()

        qq = select([
            case([
                 (qs.c.quantity_received == 0, "unreceived"),
                 (qs.c.dif == 0, "received"),
                 (qs.c.dif > 0, "partially_received"),
                 (qs.c.dif < 0, "over_received")]
             )]) \
             .select_from(line_items_calc) \
             .as_scalar()

        return qq

I have 2 orders, o1 and o2 with line items:

  LineItem(o_id=o1.id, quantity=1, quantity_received=1)
  LineItem(o_id=o2.id, quantity=1, quantity_received=0)
  LineItem(o_id=o2.id, quantity=2, quantity_received=1)

Order1 should have status "received" and Order2 should have "partially_received".

But when I query for "received" I get nothing and when querying for "partially_received" I get 2 results instead of one.

It looks like it is not filtering the LineItems by Order.id and so it uses all to calculate the status (since total_qty would be 4 and total received would be 2, which will give "partially_received")

Order.query().filter(Order.status == 'received').all()  # returns []
Order.query().filter(Order.status == 'partially_received').all()  # returns [Order1, Order2]

If add the .correlate_except(LineItem) to line_items_calc query, I get theh following error:

OperationalError: (_mysql_exceptions.OperationalError) (1054, "Unknown column 'orders.id' in 'where clause'") [SQL: u'SELECT count(*) AS count_1 \nFROM (SELECT * \nFROM orders \nWHERE orders.account_id = %s AND (SELECT CASE WHEN (a_3.quantity_received = %s) THEN %s WHEN (a_3.dif = %s) THEN %s WHEN (a_3.dif > %s) THEN %s WHEN (a_3.dif < %s) THEN %s END AS a_2 \nFROM (SELECT line_items.quantity_received AS quantity_received, sum(line_items.quantity - line_items.quantity_received) AS dif \nFROM line_items \nWHERE line_items.o_id = orders.id AND (line_items.fulfillment_status != %s OR line_items.fulfillment_status IS NULL)) AS a_3) = %s) AS a_1'] [parameters: (1L, 0, 'unreceived', 0, 'received', 0, 'partially_received', 0, 'over_received', 'cancelled', u'over_received')]

Answer 1

It would seem that you're trying to correlate the expression to the outermost query, but as it turned out the current nested subquery approach is not feasible in MySQL, because it does not allow correlated subqueries in FROM clause at all – compared to some other databases that simply do not allow correlating with previous FROM list items, except if using LATERAL.

On the other hand the nested subquery is redundant, since you can use aggregates in a CASE expression in the SELECT list, but in your current subquery you mix non-aggregate and aggregate expressions:

SELECT li.quantity_received, sum(li.quantity - li.received) as 'dif'

which is more than likely not what you wanted. Some other databases would not even allow such a query to execute, but MySQL silently picks a value for li.quantity_received from an unspecified row in the group, if ONLY_FULL_GROUP_BY is disabled. It is by default enabled in 5.7.5 and onwards, and you should consider enabling it. Looking at your hybrid property's other half it looks like you probably meant to take the sum of received quantity as well.

Below is a version of status expression that fulfills the 2 test cases you've presented in your question:

 @status.expression
 def status(cls):
     qty_received = func.coalesce(func.sum(LineItem.quantity_received), 0)
     qty_ordered = func.coalesce(func.sum(LineItem.quantity), 0)

     return select([case([
             (qty_received == 0, "unreceived"),
             (qty_received == qty_ordered, "received"),
             (qty_received < qty_ordered, "partially_received"),
             (qty_received > qty_ordered, "over_received")])]).\
         where(and_(func.coalesce(LineItem.fulfillment_status, "") != "cancelled",
                    LineItem.o_id == cls.id)).\
         correlate_except(LineItem).\
         as_scalar()

I believe it's a closer representation of the Python side approach than your original. Note the use of COALESCE for NULL handling.