Insert column based on related column value

Question

This seems like a real beginner question, but I'm having trouble finding a simple answer. I have simplified this down to just the bare bones with a simple data model representing a one-to-many relationship:

class Room(db.Model):
    __tablename__ = 'rooms'
    id        = db.Column(db.Integer, primary_key=True)
    name      = db.Column(db.String(128), unique=True)
    capacity  = db.Column(db.Integer)
    events    = db.relationship('Event', backref='room')


class Event(db.Model):
    __tablename__ = 'counts'
    id               = db.Column(db.Integer, primary_key=True)
    unusedCapacity   = db.Column(db.Integer)
    attendance       = db.Column(db.Integer)
    room_id          = db.Column(db.Integer, db.ForeignKey('rooms.id'))

Event.unusedCapacity is calculated as Room.capacity - Event.attendance, but I need to store the value in the column — Room.capacity may change over time, but the Event.unusedCapacity needs to reflect the actual unused capacity at the time of the Event.

I am currently querying the Room and then creating the event:

room = Room.query.get(room_id) # using Flask sqlAlchemy
event = event(unusedCapacity = room.capacity - attendance, ...etc)

My question is: is there a more efficient way to do this in one step?

If unused capacity is calculated on python side, you'll always need the room query to get the current room capacity. As @tooTired has pointed out, there are ways to abstract this away (another would be a [`before_insert`](https://docs.sqlalchemy.org/en/latest/orm/events.html#sqlalchemy.orm.events.MapperEvents.before_insert) event). — SuperShoot, Nov 22 '18 at 00:34
A subquery can achieve it on insert: `INSERT INTO counts (attendance, unusedCapacity, room_id) SELECT %(attendance)s, capacity - %(attendance)s, id FROM rooms WHERE id = %(room_id)s;`, but I don't know how to achieve that using ORM and am curious to see the answer! — SuperShoot, Nov 22 '18 at 00:36
Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there. — Mark, Nov 22 '18 at 05:38
@SuperShoot `__init__` could just set `unusedCapacity` to a scalar subquery that uses the passed `room_id` and `attendance`, a bit like @tooTired did it, but without the fetch. For some reason a [context sensitive default](https://docs.sqlalchemy.org/en/latest/core/defaults.html#context-sensitive-default-functions) did not work in this case, unfortunately (it didn't inline the subquery, but tried to pass it through a placeholder :P). — Ilja Everilä, Nov 22 '18 at 05:58
Thanks @IljaEverilä I will try to wrap my head around all of that tonight:) — SuperShoot, Nov 22 '18 at 06:11
@SuperShoot I took the liberty of adding an answer that does just that, so hopefully that'll unwrap it a bit faster. — Ilja Everilä, Nov 22 '18 at 06:19

Ilja Everilä · Accepted Answer · 2018-11-22T12:16:44.090

As noted in the comments by @SuperShoot, a query on insert can calculate the unused capacity in the database without having to fetch first. An explicit constructor, such as shown by @tooTired, could pass a scalar subquery as unusedCapacity:

class Event(db.Model):
    ...
    def __init__(self, **kwgs):
        if 'unusedCapacity' not in kwgs:
            kwgs['unusedCapacity'] = \
                db.select([Room.capacity - kwgs['attendance']]).\
                where(Room.id == kwgs['room_id']).\
                as_scalar()
        super().__init__(**kwgs)

Though it is possible to use client-invoked SQL expressions as defaults, I'm not sure how one could refer to the values to be inserted in the expression without using a context-sensitive default function, but that did not quite work out: the scalar subquery was not inlined and SQLAlchemy tried to pass it using placeholders instead.

A downside of the __init__ approach is that you cannot perform bulk inserts that would handle unused capacity using the table created for the model as is, but will have to perform a manual query that does the same.

Another thing to look out for is that until a flush takes place the unusedCapacity attribute of a new Event object holds the SQL expression object, not the actual value. The solution by @tooTired is more transparent in this regard, since a new Event object will hold the numeric value of unused capacity from the get go.

WRT the `as_scalar()` method, the subquery seemed to work on MySQL irrespective of whether that was executed on the query or not which has me a little confused as to when it needs to be applied. Good point about not holding value until flush, I guess it would boil down to application specifics as to whether the second round trip to the db was worth it to have the value immediately on hand. — SuperShoot, Nov 22 '18 at 12:24
I have a faint memory of seeing somewhere – either in the docs or here on SO – a mention that `as_scalar()` or `label()` is unnecessary in some contexts where it is more or less implied that you want a scalar subquery. Should try and look it up in order to be sure. I think that that particular case was about equality comparison between a column and a subquery. — Ilja Everilä, Nov 22 '18 at 12:36
And here it is, or at least one of them: https://stackoverflow.com/a/6325582/2681632. From '11, no less :D — Ilja Everilä, Nov 22 '18 at 12:43
Will have a read - I appreciate you going to the trouble! Cheers. — SuperShoot, Nov 22 '18 at 13:01

score 1 · Answer 2 · answered Nov 21 '18 at 23:45

1

SQLAlchemy adds an implicit constructor to all model classes which accepts keyword arguments for all its columns and relationships. You can override this and pass the kwargs without unusedCapacity and get the room capacity in the constructor:

class Event(db.Model):
    # ...
    #kwargs without unusedCapacity
    def __init__(**kwargs):
        room = Room.query.get(kwargs.get(room_id))
        super(Event, self).__init__(unusedCapacity = room.capacity - kwargs.get(attendance), **kwargs)


#Create new event normally
event = Event(id = 1, attendance = 1, room_id = 1)

answered Nov 21 '18 at 23:45

tooTired

179
7

This is really just the same solution though, it just does the `Room` query in a different place. – SuperShoot Nov 21 '18 at 23:54
@SuperShoot I understand, but this approach lets the Event creation more standard and cleaner way with code duplication every time a new Event is created. – tooTired Nov 22 '18 at 00:04

Insert column based on related column value

2 Answers2