APIFairy
APIFairy copied to clipboard
Working with SQLAlchemy's CASE construct
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
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.
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.