[sqlalchemy] Optimally access a model paramater n number of relationships away

[Brian Leach]

Hi everyone!

I have asked this question on StackOverflow, please see it for full detail 
of how the models are laid 
out: 
http://stackoverflow.com/questions/36463623/optimally-access-an-sqlalchemy-model-paramater-n-number-of-relationships-away

I have some "test" models that all have a relationship back to a "location" 
model, each a varying number of relationships away. When I want to get the 
name of the "location" associated with a "test", there is a ton of SQL 
emitted. Is it possible to build in some sort of shortcut to the location 
relationship?

I have tried setting the 'lazy' parameter on the relationship to 'select', 
'joined', and 'subquery' although these do not seem to make a significant 
difference when it comes to tying a test model to its location.

The way I have things defined also make it difficult for me to filter a 
group of tests based on their location, since the relationship is so 
different with each. I do not even know how to join these tables using raw 
SQL or how to begin to approach trying to emulate what the ORM is 
constructing for me.

I would imagine that this has been nailed before and am hoping that one of 
you out there can fill me in on the missing concept.


 - Brian

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.

[sqlalchemy] Is this a correct way to set up table inheritance? (circular dependency issue on delete attempt)

[Brian Leach]

This question is sparked by some trouble that I am having with deleting 
model instances. Please see this 
question: 
http://stackoverflow.com/questions/35163325/sqlalchemy-circular-dependency-on-delete

I have set up my models like below. I have several kinds of "tests" defined 
in my database. Each "test" shares certain columns like 'status', 'date', 
'who preformed it', etc. Each different test type then defines columns 
specific to it. 

class HasID(object):
@declared_attr
def id(cls):
return Column('id', Integer, Sequence('test_id_seq'), primary_key=True)
...

class TestParent(HasID, Model)
__tablename__ = 'tests'
discriminator = Column(String(50))
__mapper_args__ = {'polymorphic_on': discriminator}
...

class FooTest(TestParent, Model):
__tablename__ = 'footests'
__mapper_args__ = {'polymorphic_identity': 'footests'}
id = Column(Integer, ForeignKey('tests.id'), primary_key=True)
parent_id = Column(Integer, ForeignKey('footests.id'))
children = relationship('FooTest',
foreign_keys='FooTest.id',
lazy='joined',
join_depth=2,
cascade='save-update, merge, delete, delete-orphan')
...

class BarTest(TestParent, Model):
__tablename__ = 'bartests'
__mapper_args__ = {'polymorphic_identity': 'bartests'}
id = Column(Integer, ForeignKey('tests.id'), primary_key=True)
...



Now, I am wondering if this is a correct way to set up the FooTest, as one 
instance of FooTest may have several child instances of FooTest as 
children. 

I am unable to delete any test instance (BarTest or otherwise) and am 
getting a circular dependency error referring to the FooTest table. 

Am I missing any fundamental concepts related to table inheritance?


Thanks everyone,

Brian Leach

-- 
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 https://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.