On Wed, May 22, 2019, at 3:56 PM, bvbav wrote:
> Hi
>
> I was wondering if I can get some advice on this...
>
> Let's say I have these models:
>
> class Pet(db.Model):
> id = db.Column(..., primary_key=True)
> name = db.Column(...)
>
>
> resident_pet = db.Table('resident_pet', db.Column('resident_id', db.Integer,
> db.ForeignKey('resident.id'), primary_key=True), db.Column
> `('pet_id', db.Integer, db.ForeignKey('pet.id'), primary_key=True)
>
> class Resident(db.Model):`
> `` id = db.Column(..., primary_key=True)
> name = db.Column(...)
``
> `````` pets``` = db.relationship('Resident', secondary=resident_pet,
> lazy='dynamic')`
>
``
> ``````resident_address``` = db.Table('`````````resident_address```',
> db.Column('resident_id', db.Integer, db.ForeignKey('resident.id'),
> primary_key=True), db.Column
> `('address_id', db.Integer, db.ForeignKey('address.id'), primary_key=True)
>
>
> class Address(db.Model):
> ...
> residents = ````````````````db.relationship('Resident',
> secondary=resident_address, lazy='dynamic')
``````````
>
> Now I want to query tables Address, Resident, Pet like this:
>
> db.session.query(Address, Resident, Pet).filter(Address.id ==
> 1).join(Address.residents).join(Resident.pets).all()
>
> Now, let's say hypothetically an address (represents a home) can have a large
> number of residents (e.g 10,000+), and each resident can have the same number
> of pets (e.g 10,000+). This is the reason why I used lazy='dynamic', so I can
> do quick filters etc. But this causes a problem when I do joins like the one
> above. Because with the dynamic loading, individual SELECT queries will be
> emitted for every pet for every resident which can result in a low
> performance? Is my assumption correct?
>
> I thought about switching to 'selectin' while keeping 'dynamic', so there
> would be two relationships
>
> `````class Address(db.Model):
> ...`````
> ``````````` residents = ````````````````db.relationship('Resident',
> secondary=resident_address, lazy='selectin')
```````````` residents_dynamic = ````````````````db.relationship('Resident',
secondary=resident_address, lazy='dynamic')```````````
>
> However, doing this (while keeping the same JOIN query above, results in a
> lot more queries and take a longer amount of time to finish. I also figured
> since I'm querying everything, there's no point in eager loading
> Resident.pets, because it's not like I'm getting one instance of Resident and
> expect to have the pets collection already there.
>
> How can I get better performance for this kind of join?
the mechanism of "dynamic" loading is that your ORM instance contains a link to
a special Query object, and it does not store any Python list of items. This is
entirely different from what relationship does in all other cases, which is
that your ORM instance has a Python list, set, or other kind of collection that
simply stores the related objects in memory once loaded. So it is not possible
for a single relationship to perfom in both a "dynamic" as well as a fixed
"eager load" style of use.
Therefore, for normal use of your objects when you don't need to add additional
filter criteria, you should use the "residents" relatoinship that does not use
"dynamic" loading. Only when you need to make a special SQL query that is
essentially Address.residents + extra SQL filtering, would the
"residents_dynamic" relationship be used, as it by definition has to query the
database on each use (else it wouldn't be "dynamic"). hope this helps.
>
> --
> SQLAlchemy -
> The Python SQL Toolkit and Object Relational Mapper
>
> http://www.sqlalchemy.org/
>
> To post example code, please provide an MCVE: Minimal, Complete, and
> Verifiable Example. See http://stackoverflow.com/help/mcve for a full
> description.
> ---
> 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.
> To view this discussion on the web visit
> https://groups.google.com/d/msgid/sqlalchemy/ab7a84b6-225a-40ec-8615-1ecfe7cf4ce9%40googlegroups.com
>
> <https://groups.google.com/d/msgid/sqlalchemy/ab7a84b6-225a-40ec-8615-1ecfe7cf4ce9%40googlegroups.com?utm_medium=email&utm_source=footer>.
> For more options, visit https://groups.google.com/d/optout.
--
SQLAlchemy -
The Python SQL Toolkit and Object Relational Mapper
http://www.sqlalchemy.org/
To post example code, please provide an MCVE: Minimal, Complete, and Verifiable
Example. See http://stackoverflow.com/help/mcve for a full description.
---
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.
To view this discussion on the web visit
https://groups.google.com/d/msgid/sqlalchemy/f670f3e6-388d-4a6a-b02f-5cb8ca9cccf7%40www.fastmail.com.
For more options, visit https://groups.google.com/d/optout.
