Order by query output column

Question

I'm using graphene with sqlalchemy and I have an output object that contains a computed field. The field is computed according to some input (query) parameter and more or less look like this (to simplify, lets consider I'm computing f(x)=ax+b where a and b are both columns in my Thing table):

import models

class Thing(SQLAlchemyObjectType):
    class Meta:
        model = models.Thing
        interfaces = (relay.Node, )

    f = graphene.Field(graphene.Float)

    def resolve_f(self, info):
        return self.a * info.context['x'] + self.b

In my query I have the following and I would like to sort fields according to the output of the function f:

class Query(graphene.ObjectType):
    best_points = graphene.List(lambda: Thing, x = graphene.Float())

    def resolve_best_points(self, info, x):
        query = Thing.get_query(info)
        return query.all()

At first I've tried sorting within resolve_best_points using something like query.order_by("f").all() but in vain since graphene seems to add this field outside the resolver (ie., query.all() only contains a and b but not f(a, b)).

Is there a way to achieve this in a clean way? Adding some option in Thing? Or maybe obtaining the output values within the resolver and then sorting? Or something a bit uglier like adding a middleware to sort outputs from resolve_best_points?

What is possible, what are the pros/cons of possible solutions to this?

Note that this is somewhat related to this question: Sqlalchemy order by calculated column, but this is also quite different because here I don't want a calculation based on database fields only (current solutions do not work when there are variables involved, like info.context['x'] in this toy example).

This is also my question. The thing is that we cannot access the data which are generated at the lower level, from the top level. — alireza, Aug 05 '20 at 11:43

score 0 · Accepted Answer · answered Aug 18 '20 at 16:09

You can use SQLAlchemy's orm.with_expression and orm.query_expression functions to apply ad-hoc expressions to your query, then use them in the ORDER_BY clause.

Add a query_expression attribute to your Thing model.

# models.py
from sqlalchemy import Float, Integer, orm

class Thing(Base):
    __tablename__ = 'things'
    id = Column(Integer, primary_key=True)
    a = Column(Float)
    b = Column(Float)

    expr = orm.query_expression()

You can now use with_expression to pass arbitrary SQL expressions to your queries. The value of the expr attribute, on Thing objects, will be the result of the expression.

# schema.py
import graphene

from graphene import relay
from sqlalchemy.orm import with_expression

from models import Thing as ThingModel


class Thing(SQLAlchemyObjectType):
    class Meta:
        model = ThingModel
        interfaces = (relay.Node,)


class Query(graphene.ObjectType)
    best_points = graphene.List(Thing, x=graphene.Float())

    def resolve_best_points(self, info, x):
        expr = ThingModel.a * x + ThingModel.b

        return (
            Thing.get_query(info)
            .options(with_expression(ThingModel.expr, expr))
            .order_by(expr)
            .all()
        )

More details and a list of caveats to be aware of are in this section of the SQLAlchemy docs: Query-time SQL expressions as mapped attributes.

Order by query output column

1 Answers1

Linked