Re: [sqlalchemy] restrict child count?
On 5/19/15 8:57 AM, Richard Gerd Kuesters wrote: hi! this may be a weird question, but is there a way i can restrict the number of children in a relationship? not by limit ... how is that different? Anytime in SQL you want to get only the first N of M, LIMIT or its equivalents must be involved. two options are: 1. write the exact SQL for the primary + relationship you want, then use contains_eager() to specify it as a collection load. the SQL has to be along the lines of SELECT * FROM primary LEFT OUTER JOIN secondary WHERE secondary.id IS NULL or secondary.id IN (select id FROM secondary AS sec_2 LIMIT N WHERE sec_2.primary_id=secondary.primary_id) 2. load the collections individually: from sqlalchemy.orm.attributes import set_committed_value for item in things: child_items = sess.query(Child).with_parent(item).limit(N).all() set_committed_value(item, child_items, child_items) scenario: i have a one to many rel, where the parent have 3 values (row, column, depth) that creates a max child count of row * column * depth (yes, like the 3d stuff) ... so, count(child) = max_child, this sort of stuff. if i could pull of postgres specific constraint for this, even better ... best regards, richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] restrict child count?
thanks Mike! when i stated about the limit, it was because it must not be taken as a parameter for any query, which select * from blah and select * from bla limit N should be return the same exactly number of rows, including where filters and so on. it is something like a physical rule, where my parent is (really) a box and the children its items (so, i cannot put more items then the box's limit). either way, creating a relationship with limit *can* provide me that sort of behaviour? i mean, i'm just asking this because it may be already done by someone. if not, that's not a problem -- i'll have to managed something by myself :) ps: sorry for my bad english, sometimes i can't make understandable questions :) best regards, richard. On 05/19/2015 11:16 AM, Mike Bayer wrote: On 5/19/15 8:57 AM, Richard Gerd Kuesters wrote: hi! this may be a weird question, but is there a way i can restrict the number of children in a relationship? not by limit ... how is that different? Anytime in SQL you want to get only the first N of M, LIMIT or its equivalents must be involved. two options are: 1. write the exact SQL for the primary + relationship you want, then use contains_eager() to specify it as a collection load. the SQL has to be along the lines of SELECT * FROM primary LEFT OUTER JOIN secondary WHERE secondary.id IS NULL or secondary.id IN (select id FROM secondary AS sec_2 LIMIT N WHERE sec_2.primary_id=secondary.primary_id) 2. load the collections individually: from sqlalchemy.orm.attributes import set_committed_value for item in things: child_items = sess.query(Child).with_parent(item).limit(N).all() set_committed_value(item, child_items, child_items) scenario: i have a one to many rel, where the parent have 3 values (row, column, depth) that creates a max child count of row * column * depth (yes, like the 3d stuff) ... so, count(child) = max_child, this sort of stuff. if i could pull of postgres specific constraint for this, even better ... best regards, richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. attachment: richard.vcf
Re: [sqlalchemy] restrict child count?
On 5/19/15 10:54 AM, Richard Gerd Kuesters wrote: thanks Mike! when i stated about the limit, it was because it must not be taken as a parameter for any query, which select * from blah and select * from bla limit N should be return the same exactly number of rows, including where filters and so on. it is something like a physical rule, where my parent is (really) a box and the children its items (so, i cannot put more items then the box's limit). either way, creating a relationship with limit *can* provide me that sort of behaviour? I don't understand what the behavior is here. No LIMIT, yet there is a limit. An assertion?I have no idea what you mean. The relationship 1. emits SQL 2. loads the results into objects.Are we talking about 1. or 2. ? i mean, i'm just asking this because it may be already done by someone. if not, that's not a problem -- i'll have to managed something by myself :) ps: sorry for my bad english, sometimes i can't make understandable questions :) best regards, richard. On 05/19/2015 11:16 AM, Mike Bayer wrote: On 5/19/15 8:57 AM, Richard Gerd Kuesters wrote: hi! this may be a weird question, but is there a way i can restrict the number of children in a relationship? not by limit ... how is that different? Anytime in SQL you want to get only the first N of M, LIMIT or its equivalents must be involved. two options are: 1. write the exact SQL for the primary + relationship you want, then use contains_eager() to specify it as a collection load. the SQL has to be along the lines of SELECT * FROM primary LEFT OUTER JOIN secondary WHERE secondary.id IS NULL or secondary.id IN (select id FROM secondary AS sec_2 LIMIT N WHERE sec_2.primary_id=secondary.primary_id) 2. load the collections individually: from sqlalchemy.orm.attributes import set_committed_value for item in things: child_items = sess.query(Child).with_parent(item).limit(N).all() set_committed_value(item, child_items, child_items) scenario: i have a one to many rel, where the parent have 3 values (row, column, depth) that creates a max child count of row * column * depth (yes, like the 3d stuff) ... so, count(child) = max_child, this sort of stuff. if i could pull of postgres specific constraint for this, even better ... best regards, richard. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com mailto:sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com mailto:sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout. -- You received this message because you are subscribed to the Google Groups sqlalchemy group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
Re: [sqlalchemy] restrict child count?
bingo! thanks Simon. that's exactly the question :)
well, the checks on the cube are already there (they must have a value
higher then 0 to have a volume), but i must not increment the cube
children more then it's maximum capacity. i'm considering an event
listener as well, but i'm wondering if it can't be done at database
level, using postgres?
or, what would be the better approach scenario for this? a trigger, an
event, both?
On 05/19/2015 01:13 PM, Simon King wrote:
On Tue, May 19, 2015 at 4:06 PM, Mike Bayer mike...@zzzcomputing.com wrote:
On 5/19/15 10:54 AM, Richard Gerd Kuesters wrote:
thanks Mike!
when i stated about the limit, it was because it must not be taken as a
parameter for any query, which select * from blah and select * from bla
limit N should be return the same exactly number of rows, including where
filters and so on. it is something like a physical rule, where my parent
is (really) a box and the children its items (so, i cannot put more items
then the box's limit).
either way, creating a relationship with limit *can* provide me that sort of
behaviour?
I don't understand what the behavior is here. No LIMIT, yet there is a
limit. An assertion?I have no idea what you mean. The
relationship 1. emits SQL 2. loads the results into objects.Are we
talking about 1. or 2. ?
I'm guessing he's looking for something like this:
class Cube(Base):
__table_name__ = 'cube'
id = sa.Column(sa.Integer, primary_key=True)
rows = sa.Column(sa.Integer)
cols = sa.Column(sa.Integer)
depth = sa.Column(sa.Integer)
@property
def volume(self):
return self.rows * self.cols * self.depth
class Cell(Base):
__table_name__ = 'cell'
id = sa.Column(sa.Integer, primary_key=True)
cube_id = sa.Column(sa.ForeignKey(Cube.id))
row = sa.Column(sa.Integer)
col = sa.Column(sa.Integer)
depth = sa.Column(sa.Integer)
__table_args__ = (
sa.UniqueConstraint('cube_id', 'row', 'col', 'depth')
)
cube = saorm.relationship(Cube, backref='cells')
Now, given an instance of Cube, how can you ensure that it is
impossible to add more than Cube.volume cells to the Cube.cells
relationship? I imagine it is possible by attaching an event listener
to Cube.cells and doing the validation in there.
If Richard's data really is structured like this, I'd probably instead
want to enforce that:
0 = cell.row cell.cube.rows
0 = cell.col cell.cube.cols
0 = cell.depth cell.cube.depth
I think you could probably do this with SQLAlchemy validators. I don't
know enough Postgres, but I suspect you could also enforce it at the
database level.
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
attachment: richard.vcf
Re: [sqlalchemy] restrict child count?
On Tue, May 19, 2015 at 4:06 PM, Mike Bayer mike...@zzzcomputing.com wrote:
On 5/19/15 10:54 AM, Richard Gerd Kuesters wrote:
thanks Mike!
when i stated about the limit, it was because it must not be taken as a
parameter for any query, which select * from blah and select * from bla
limit N should be return the same exactly number of rows, including where
filters and so on. it is something like a physical rule, where my parent
is (really) a box and the children its items (so, i cannot put more items
then the box's limit).
either way, creating a relationship with limit *can* provide me that sort of
behaviour?
I don't understand what the behavior is here. No LIMIT, yet there is a
limit. An assertion?I have no idea what you mean. The
relationship 1. emits SQL 2. loads the results into objects.Are we
talking about 1. or 2. ?
I'm guessing he's looking for something like this:
class Cube(Base):
__table_name__ = 'cube'
id = sa.Column(sa.Integer, primary_key=True)
rows = sa.Column(sa.Integer)
cols = sa.Column(sa.Integer)
depth = sa.Column(sa.Integer)
@property
def volume(self):
return self.rows * self.cols * self.depth
class Cell(Base):
__table_name__ = 'cell'
id = sa.Column(sa.Integer, primary_key=True)
cube_id = sa.Column(sa.ForeignKey(Cube.id))
row = sa.Column(sa.Integer)
col = sa.Column(sa.Integer)
depth = sa.Column(sa.Integer)
__table_args__ = (
sa.UniqueConstraint('cube_id', 'row', 'col', 'depth')
)
cube = saorm.relationship(Cube, backref='cells')
Now, given an instance of Cube, how can you ensure that it is
impossible to add more than Cube.volume cells to the Cube.cells
relationship? I imagine it is possible by attaching an event listener
to Cube.cells and doing the validation in there.
If Richard's data really is structured like this, I'd probably instead
want to enforce that:
0 = cell.row cell.cube.rows
0 = cell.col cell.cube.cols
0 = cell.depth cell.cube.depth
I think you could probably do this with SQLAlchemy validators. I don't
know enough Postgres, but I suspect you could also enforce it at the
database level.
Simon
--
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To unsubscribe from this group and stop receiving emails from it, send an email
to sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
