sqlalchemy column_property in self-referential

I can't describe the column "invited_name" (column_property). I don't know how to do this correctly.

class Worker(declarative_base()):
    __tablename__ = "staff_worker_info"

    id = Column(Integer, primary_key=True)
    first_name = Column(String(40), nullable=False)
    last_name = Column(String(40), nullable=False)

    invited_id = Column(Integer, ForeignKey('staff_worker_info.id'))
    invited = relationship("Worker", uselist=False, remote_side=[id], join_depth=1)

    # I don't know how to describe this column
    invited_name = column_property(
         select([Worker.first_name]). \
         where(Worker.id == invited_id).\
         label('invited_n'))

I understand why this doesn't work, but I don't know how to write it differently.

I should get such a SQL query.

SELECT staff_worker_info.id, staff_worker_info.first_name staff_worker_info.last_name, staff_worker_info.invited_id,
        (SELECT worker_invited.first_name  
         FROM staff_worker_info AS worker_invited
         WHERE staff_worker_info.invited_id = worker_invited.id) AS invited_n,
FROM staff_worker_info

Solution

Might be a bit late, but I recently faced a similar question. I think your problem is quite easy to solve with only the relationship. If you want you can also solve it by using a column_property.

First, using the relationship. If you make the invited relationship joined, then the actual query that is send to the database is a self-join. You can access the first name via that relationship (reference https://docs.sqlalchemy.org/en/14/orm/self_referential.html).

class Worker(declarative_base()):
    __tablename__ = "staff_worker_info"

    id = Column(Integer, primary_key=True)
    first_name = Column(String(40), nullable=False)
    last_name = Column(String(40), nullable=False)

    invited_id = Column(Integer, ForeignKey('staff_worker_info.id'))
    invited = relationship("Worker", uselist=False, remote_side=[id], join_depth=1, lazy='joined')
    
    @property
    def invited_name(self):
        return self.invited.first_name

Then, if the query you want to do is more complex, and it requires you to create a column_property, you can also do it as follows (reference https://docs.sqlalchemy.org/en/14/orm/mapped_sql_expr.html):

from sqlalchemy import inspect
from sqlalchemy.orm import aliased

class Worker(declarative_base()):
    __tablename__ = "staff_worker_info"

    id = Column(Integer, primary_key=True)
    first_name = Column(String(40), nullable=False)
    last_name = Column(String(40), nullable=False)

    invited_id = Column(Integer, ForeignKey('staff_worker_info.id'))
    invited = relationship("Worker", uselist=False, remote_side=[id], join_depth=1)

# Requires alias
worker = aliased(Worker)
inspect(Worker).add_property(
   "invited_name",
   column_property(
         select([worker.first_name]). \
         where(worker.id == Worker.invited_id)
   )
)