Working with SQLAlchemy's CASE construct

[pascal-mueller]

Hello,

I have the following model:

class Message(db.Model):
    __tablename__ = 'messages'

    id: so.Mapped[int] = so.mapped_column(primary_key=True)
    chat_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey("chats.id"))
    content: so.Mapped[str] = so.mapped_column(sa.String(1024))

    author_id: so.Mapped[int] = so.mapped_column(sa.ForeignKey("users.id"))
    author: so.Mapped['User'] = so.relationship('User')
    
    timestamp: so.Mapped[datetime] = so.mapped_column(
        index=True, default=datetime.utcnow)

and the following schema

class MessageSchema(ma.SQLAlchemySchema):
    class Meta:
        model = Message
        ordered = True

    id = ma.auto_field(dump_only=True)
    chat_id = ma.auto_field(dump_only=True)
    content = ma.String()
    author = ma.Nested(UserSchema, dump_only=True)
    timestamp = ma.auto_field(dump_only=True)
    direction = ma.String(dump_only=True)

    @post_dump
    def fix_datetimes(self, data, **kwargs):
        data['timestamp'] += 'Z'
        return data

Furthermore, I have the following route

@messages.route('/messages/<int:id>', methods=['GET'])
@authenticate(token_auth)
@response(messages_schema)
def all(id):
    """Retrieve all messages from current user"""
    me = token_auth.current_user()

    
    # Why does this not include "direction" in the result?
    messages1 = Message.select().where(
        Message.chat_id == id,
        case((Message.author == me, '1'), else_='0').label("direction")
    )
    

    messages2 = select(
        Message.content,
        Message.timestamp, 
        case((Message.author == me, 1), else_=0).label("direction")).where(Message.chat_id == id)
    
    return messages2

My goal is to retrieve all messages where Message.chat_id == id. I then want to basically map the author column to 1 if me is the author of the message and 0 otherwise. I use case to achieve that.

As you can see, I provided two way of doing the above. Now I'm confused as to what types I can return in the route. Based on the microblog you wrote, it seems that I can return object of the SqlAlchemy Select Query type, which messages1 and messages2 should be. I'm further confused by the schema in this case because I'm very unsure if I have to add the labelled column I made.

Anyway, here's some more info:

db.session.execute(messages1).first()[0].__dict__

gives

{'_sa_instance_state': <sqlalchemy.orm.state.InstanceState object at 0x7fba39888a70>, 'author_id': 4, 'id': 3, 'chat_id': 1, 'content': 'Discuss professional perform man strong.', 'timestamp': datetime.datetime(2023, 4, 23, 19, 14, 20)}

while

db.session.execute(messages2).first()

('Agent ever impact team half feel.', datetime.datetime(2023, 2, 25, 5, 46, 8), 0)

As you can see, one returns a dict and the other a tuple. My guess is that the first adobts the structure of the model while the second doesn't but note that the first does not include my SQL transformation. That's the reason I thought that maybe I have to add the labelled column to the schema.

In any case, how do I solve this? What's a nice way of doing this? Do I need to add direction to my schema if I have a labelled column named like that?

Thanks in advance

[miguelgrinberg]

Your first query returns Message objects, which do not have a direction field. You added a case statement in the where(), which has no influence on what is returned. If you wanted to return a made-up direction column, that should be added in the select(), not the where(). But then your query would not be returning Message instances anymore, so that is also not a solution to your problem.

I think you have two options. Either you modify your model to match your schema by adding direction to it, maybe as a property, or you run a custom query that returns Message instances along with their direction, and then you combine the properties of the message with the direction into a list of dictionaries, and return that from the route, instead of Message instances. Both approaches are likely to present some challenges, so I can't really give a preference, I would probably have to try to implement this to know which solution is best.

My opinion is that you are fighting against the idea of having strict schema compliance here, so what I would probably do is to figure out a way to work with well defined models and schemas. The direction field as you defined it is not a property of the message, so it feels as it does not belong in there, since its value is dependent on context and not the entity itself.

[pascal-mueller]

Thanks a lot for the input. Especially the hint about the context. I removed the SQL transformation and simply do some post processing of the data on the client side. I think by trying to move it from the client to the server, I moved it "out of context", which led to the above issue.