Insert column based on related column value












1















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?










share|improve this question


















  • 1





    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 event).

    – SuperShoot
    Nov 22 '18 at 0: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 0:36











  • Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.

    – Mark Meyer
    Nov 22 '18 at 5:38






  • 1





    @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 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 5:58













  • Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)

    – SuperShoot
    Nov 22 '18 at 6:11
















1















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?










share|improve this question


















  • 1





    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 event).

    – SuperShoot
    Nov 22 '18 at 0: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 0:36











  • Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.

    – Mark Meyer
    Nov 22 '18 at 5:38






  • 1





    @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 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 5:58













  • Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)

    – SuperShoot
    Nov 22 '18 at 6:11














1












1








1








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?










share|improve this 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?







python sqlalchemy flask-sqlalchemy






share|improve this question













share|improve this question











share|improve this question




share|improve this question










asked Nov 21 '18 at 22:11









Mark MeyerMark Meyer

38.6k33159




38.6k33159








  • 1





    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 event).

    – SuperShoot
    Nov 22 '18 at 0: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 0:36











  • Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.

    – Mark Meyer
    Nov 22 '18 at 5:38






  • 1





    @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 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 5:58













  • Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)

    – SuperShoot
    Nov 22 '18 at 6:11














  • 1





    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 event).

    – SuperShoot
    Nov 22 '18 at 0: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 0:36











  • Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.

    – Mark Meyer
    Nov 22 '18 at 5:38






  • 1





    @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 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 5:58













  • Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)

    – SuperShoot
    Nov 22 '18 at 6:11








1




1





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 event).

– SuperShoot
Nov 22 '18 at 0:34





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 event).

– SuperShoot
Nov 22 '18 at 0: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 0:36





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 0:36













Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.

– Mark Meyer
Nov 22 '18 at 5:38





Thanks @SuperShoot. The insert is being initiated from the python, but calculation doesn't necessarily need to happen there.

– Mark Meyer
Nov 22 '18 at 5:38




1




1





@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 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 5:58







@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 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 5:58















Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)

– SuperShoot
Nov 22 '18 at 6:11





Thanks @IljaEverilä I will try to wrap my head around all of that tonight:)

– SuperShoot
Nov 22 '18 at 6:11












2 Answers
2






active

oldest

votes


















2














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.






share|improve this answer


























  • 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






  • 1





    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: 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



















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)





share|improve this answer
























  • 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 0:04













Your Answer






StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");

StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);

StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});

function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});


}
});














draft saved

draft discarded


















StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421156%2finsert-column-based-on-related-column-value%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown

























2 Answers
2






active

oldest

votes








2 Answers
2






active

oldest

votes









active

oldest

votes






active

oldest

votes









2














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.






share|improve this answer


























  • 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






  • 1





    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: 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
















2














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.






share|improve this answer


























  • 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






  • 1





    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: 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














2












2








2







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.






share|improve this answer















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.







share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 22 '18 at 12:16

























answered Nov 22 '18 at 6:13









Ilja EveriläIlja Everilä

24.5k33763




24.5k33763













  • 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






  • 1





    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: 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



















  • 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






  • 1





    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: 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

















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





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




1




1





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







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: stackoverflow.com/a/6325582/2681632. From '11, no less :D

– Ilja Everilä
Nov 22 '18 at 12:43







And here it is, or at least one of them: 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





Will have a read - I appreciate you going to the trouble! Cheers.

– SuperShoot
Nov 22 '18 at 13:01













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)





share|improve this answer
























  • 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 0:04


















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)





share|improve this answer
























  • 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 0:04
















1












1








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)





share|improve this answer













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)






share|improve this answer












share|improve this answer



share|improve this answer










answered Nov 21 '18 at 23:45









tooTiredtooTired

1697




1697













  • 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 0:04





















  • 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 0:04



















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





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 0:04







@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 0:04




















draft saved

draft discarded




















































Thanks for contributing an answer to Stack Overflow!


  • Please be sure to answer the question. Provide details and share your research!

But avoid



  • Asking for help, clarification, or responding to other answers.

  • Making statements based on opinion; back them up with references or personal experience.


To learn more, see our tips on writing great answers.




draft saved


draft discarded














StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53421156%2finsert-column-based-on-related-column-value%23new-answer', 'question_page');
}
);

Post as a guest















Required, but never shown





















































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown

































Required, but never shown














Required, but never shown












Required, but never shown







Required, but never shown







Popular posts from this blog

android studio warns about leanback feature tag usage required on manifest while using Unity exported app?

SQL update select statement

WPF add header to Image with URL pettitions [duplicate]