Hello.
I've tried an experiment to verify that yield_per() with subqueryload() behaves
as badly as you described, but according to my practical observation, it issues
ONE subqueryload() and everything behaves as I would expect. It emits two
SELECTs, one to fetch the objects and the second one to fetch the related data
and that's about it, see below. So back to square one, what is wrong with it?
Base = declarative_base()
class Contact(Base):
# Just a sketch, I hope you get the picture.
id,
name,
phones = relationship(Phone) # many
class Phone(Base):
# Just a sketch, I hope you get the picture.
id,
number,
contact = relationship(Contact) # one
# Setup engine with echo set to True.
phones = ['123456789', '987654321', '555777999']
for i in range(1, 11):
c = Contact(name=u' '.join([u'Contact', unicode(i)]))
session.add(c)
session.add_all(Phone(contact=c, number=e) for e in phones)
session.flush()
session.expunge_all()
q = session.query(Contact).options(subqueryload(Contact.phones))
for each in q.yield_per(2):
print each.last_name
for e in each.phones:
print e
The output is like this:
SA info about all the inserts after session.flush().
SA info about select for contacts.
SA info about select for their phones.
Contact 10
123456789
987654321
555777999
Contact 9
123456789
987654321
555777999
Contact 8
123456789
987654321
555777999
Contact 7
123456789
987654321
555777999
Contact 6
123456789
987654321
555777999
Contact 5
123456789
987654321
555777999
Contact 4
123456789
987654321
555777999
Contact 3
123456789
987654321
555777999
Contact 2
123456789
987654321
555777999
Contact 1
123456789
987654321
555777999
Thank you,
Ladislav Lenart
On 5.6.2013 11:26, Ladislav Lenart wrote:
> Hello.
>
> I have one more question about my approach to WindowedRangeQuery:
>
>> * Suppose a complex query q with join(...), filter(...) and options(...).
>> * I need to create q2 from q such that:
>> * It has no options.
>> * Can I reset the options with q = q.options(None)?
>> * It has select with the over by magic taken from WindowedRangeQuery recipe.
>> * I know I can use with_entities() for this.
>> * I will use q2 to obtain the window ranges.
>> * I will iterate over the window ranges and apply each to the original q and
>> execute it in a loop.
>
> Suppose I have a complex query (without options() / order_by()) to obtain the
> objects I want. I use this query to calculate window intervals. I don't have
> to
> use it again to fetch the objects, because I already have their id intervals.
> Am
> I right?
>
>
> Thank you,
>
> Ladislav Lenart
>
>
> On 4.6.2013 19:15, Ladislav Lenart wrote:
>> On 4.6.2013 18:49, Michael Bayer wrote:
>>>
>>> On Jun 4, 2013, at 12:28 PM, Ladislav Lenart <lenart...@volny.cz> wrote:
>>>
>>>>
>>>>> Absolutely, you should do whatever you have to in order to get the range
>>>>> you want, in fact the recipe even says this...
>>>>
>>>> Ok. What I want to do is basically the following:
>>>> * Suppose a complex query q with join(...), filter(...) and options(...).
>>>> * I need to create q2 from q such that:
>>>> * It has no options.
>>>> * Can I reset the options with q = q.options(None)?
>>>> * It has select with the over by magic taken from WindowedRangeQuery
>>>> recipe.
>>>> * I know I can use with_entities() for this.
>>>> * I will use q2 to obtain the window ranges.
>>>> * I will iterate over the window ranges and apply each to the original q
>>>> and
>>>> execute it in a loop.
>>>>
>>>> Can this strategy work?
>>>
>>> there's not a publicly supported feature to "reset" the options right now
>>> so you'd probably need to apply them after you get your window ranging
>>> query. You can probably remove the effect of eager loaders if you were
>>> to just assign a blank _attributes dictionary to the new query though (just
>>> no long term guarantees).
>>>
>>> other than that, I use the windowing concept extensively and it works very
>>> well.
>>
>> Ok, I will try it.
>>
>> Thank you very much for your invaluable insights,
>>
>> Ladislav Lenart
--
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?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
