How to sort by hybrid property on a one-on-one relationship in SQLAlchemy with a reference on the same table twice?

Admin 1
Viewed 543

I have a table called User and Document. The table User holds:

first_name
…

Document holds:

id
created_by_id
deleted_by_id
…

I want to sort the Document table by the first_name. Therefore, I created the relationships creation_user and deletion_user. Both foreign keys created_by_id and deleted_by_id reference the same table. The hybrid property created_by shall be sorted on:

creation_user = relationship("User", foreign_keys=[created_by_id], lazy="joined")
deletion_user = relationship("User", foreign_keys=[deleted_by_id], lazy="joined")

@hybrid_property
def created_by(self):
    if self.creation_user:
        return self.creation_user.first_name
    else:
        return None

@created_by.expression
    def created_by(cls):
        return User.first_name

Unfortunately, SQLAlchemy does not match the correct user and the resulting SQL query looks like this:

SELECT … 
FROM   document 
       LEFT OUTER JOIN [user] AS user_1 
                    ON user_1.id = document.created_by_id 
       LEFT OUTER JOIN [user] AS user_2 
                    ON user_2.id = document.deleted_by_id 
WHERE  document.id = ? 
ORDER  BY [user].first_name ASC

Is there a way to use the hybrid property or a hybrid expression (see sqlalchemy docs) so that the ORDER BY statement resolves to ORDER BY user_2.first_name ASC?

sqlalchemy python
2 Answers

A solution that is "semi SQLAlchemy" is to use text as following:

def sort_docs(ids, order)
   if order.lower() not in ['asc', 'desc']:
      return None
   statement = """
   SELECT documents.id AS documents_id, documents.created_by_id AS 
    documents_created_by_id, documents.deleted_by_id AS documents_deleted_by_id, 
    users_1.id AS users_1_id, users_1.first_name AS users_1_first_name, users_2.id AS 
    users_2_id, users_2.first_name AS users_2_first_name 
   FROM documents 
   LEFT OUTER JOIN users AS users_1 ON users_1.id = documents.created_by_id 
   LEFT OUTER JOIN users AS users_2 ON users_2.id = documents.deleted_by_id
   WHERE documents_id in :ids
   ORDER BY users_1.first_name {}
   """.format(order)
   sql_statement = text(statement)
   result = db.session.query(Document).from_statement(sql_statement.bindparams(ids=ids))
   return result

This way you will retrieve your required documents as an object of Document model sorted by the user's first name in the requested order.

user_46938740

Having the hybrid expression specify the required user name generates the desired ordering 

@created_by.expression
def created_by(cls):
    return sql.select([User.first_name]).where(User.id == cls.created_by_id)

Although the generated query adds an additional subquery rather than referencing the aliased table:

SELECT documents.id AS documents_id, documents.created_by_id AS documents_created_by_id, documents.deleted_by_id AS documents_deleted_by_id, users_1.id AS users_1_id, users_1.first_name AS users_1_first_name, users_2.id AS users_2_id, users_2.first_name AS users_2_first_name 
FROM documents 
LEFT OUTER JOIN users AS users_1 ON users_1.id = documents.created_by_id 
LEFT OUTER JOIN users AS users_2 ON users_2.id = documents.deleted_by_id 
ORDER BY (SELECT users.first_name 
          FROM users 
          WHERE users.id = documents.created_by_id)

Another approach would be to define the ordering on the relationship:

creation_user = relationship("User", foreign_keys=[created_by_id], lazy="joined", order_by="User.first_name")

This produced the SQL specified in the question, and removes the need for the hybrid attributes:

SELECT documents.id AS documents_id, documents.created_by_id AS documents_created_by_id, documents.deleted_by_id AS documents_deleted_by_id, users_1.id AS users_1_id, users_1.first_name AS users_1_first_name, users_2.id AS users_2_id, users_2.first_name AS users_2_first_name 
FROM documents 
LEFT OUTER JOIN users AS users_1 ON users_1.id = documents.created_by_id 
LEFT OUTER JOIN users AS users_2 ON users_2.id = documents.deleted_by_id 
ORDER BY users_1.first_name

However if you ever want a different order, you'll need to override the loading strategy - see this answer.

user_53209060
Related