Hello everybody!
I would like to know if it's possible to create an optimized query
that gives all the elements of a class A that have a relationship to
an specific class B
Here's the thing:
I have a pretty simple class "Region" that defines an area in the screen:
class Region(declarativeBase):
__tablename__ = "regions"
id = Column("id", Integer, primary_key=True, key="id")
x = Column("x", Integer)
y = Column("y", Integer)
width = Column("width", Integer(unsigned=True))
height = Column("height", Integer(unsigned=True))
And then a class Layout that can contain (or point to?) a number of
these regions depending on what they're used for:
class Layout(declarativeBase):
__tablename__ = "layouts"
id = Column("id", Integer, primary_key=True, key="id")
width = Column("width", Integer)
height = Column("height", Integer)
logoRegionId = Column("logo_region_id", Integer,
ForeignKey("regions.id"), key="logoRegionId")
logoRegion = relationship(Region.Region,
uselist=False,
primaryjoin=lambda:
Region.Region.id == Layout.logoRegionId
)
backgroundRegionId = Column("background_region_id", Integer,
ForeignKey("regions.id"),
key="backgroundRegionId")
backgroundRegion = relationship(Region.Region,
uselist=False,
primaryjoin=lambda:
Region.Region.id == Layout.backgroundRegionId
)
mainImageRegionId = Column("main_image_region_id", Integer,
ForeignKey("regions.id"),
key="mainImageRegionId")
mainImageRegion = relationship(Region.Region,
uselist=False,
primaryjoin=lambda:
Region.Region.id == Layout.mainImageRegionId
)
As you can see, the Regions don't have a backref to Layout (mainly
because they may be used in other classes) but one specific region
will only be in one layout at the same time (regions are never shared
by layouts) and one Region will never be used in the same layout more
than once (there won't be a Layout.logoRegion and a
Layout.mainImageRegion pointing to the same Region instance)
What I would like to do is knowing what layouts (or, better said,
"layout") are using an specific given region in any of its fields.
Let's say, given this:
Layouts:
+----+-------+--------+----------------+----------------------+----------------------+
| id | width | height | logo_region_id | background_region_id |
main_image_region_id |
+----+-------+--------+----------------+----------------------+----------------------+
| 38 | 1280 | 720 | 8 | 7 |
9 |
| 45 | 1280 | 720 | 15 | 16 |
17 |
| 52 | 1280 | 720 | 23 | 24 |
25 |
+----+-------+--------+----------------+----------------------+----------------------+
I would like to have a method to say "What layouts are using region
'16'?" and the answer would be a [45], for instance (if it were [45,
52], I'd have to send a nasty exception, but that's besides the point).
Of course, I could just go checking field by field in the Layouts, but
if I could do it with an SQL expression, that would probably be
faster.
I even have a method that can give me the names of the relationships
towards another class (in this case,
Layout.relationshipsTo(Region.Region), which would return
["logoRegion", "backgroundRegion", "mainImageRegion"]. I've tried
playing with getattr(Layout, "name_of_relationship"), put that in
joins, in onclauses for the join... Nothing.
Any hint will be appreciated!
Thank you in advance
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com.
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com.
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en.
