Re: [Sqlalchemy-users] eager loading the same table twice - ITS IN !

[Shuo Yang]

You the man!  Thanks for this feature!

On 12/2/05, Michael Bayer <[EMAIL PROTECTED]> wrote:

OK, this new unit test blows me away:

# Place mapper

Place.mapper = mapper(Place, place)

# Transition mapper, with Place relations

Transition.mapper = mapper(Transition, transition, properties = dict(

    inputs = relation(Place.mapper , place_output, lazy=True),

    outputs = relation(Place.mapper, place_input, lazy=True),

    )

)

# add Transition relations to Place mapper

Place.mapper.add_property ('inputs', relation(Transition.mapper, place_output, lazy=True))

Place.mapper.add_property('outputs', relation(Transition.mapper, place_input, lazy=True))

# Place eager mapper

Place.eagermapper = Place.mapper.options(

    eagerload('inputs', selectalias='ip_alias'),

    eagerload('outputs', selectalias='op_alias')

)

t1 = Transition('transition1')

t2 = Transition('transition2')

t3 = Transition('transition3')

p1 = Place('place1')

p2 = Place('place2')

p3 = Place('place3')

t1.inputs.append(p1)

t1.inputs.append(p2)

t1.outputs.append(p3)

t2.inputs.append(p1)

p2.inputs.append(t2)

p3.inputs.append(t2)

p1.outputs.append(t1)

objectstore.commit ()

l = Place.eagermapper.select()

print repr(l)

Program output:

INSERT INTO place (name) VALUES (:name)

{'name': 'place1'}

INSERT INTO place (name) VALUES (:name)

{'name': 'place2'}

INSERT INTO place (name) VALUES (:name)

{'name': 'place3'}

INSERT INTO transition (name) VALUES (:name)

{'name': 'transition1'}

INSERT INTO transition (name) VALUES (:name)

{'name': 'transition2'}

INSERT INTO transition (name) VALUES (:name)

{'name': 'transition3'}

INSERT INTO place_input (place_id, transition_id) VALUES (:place_id, :transition_id)

[{'place_id': 3, 'transition_id': 1}]

INSERT INTO place_output (place_id, transition_id) VALUES (:place_id, :transition_id)

[{'place_id': 1, 'transition_id': 1}, {'place_id': 2, 'transition_id': 1}, {'place_id': 1, 'transition_id': 2}]

INSERT INTO place_input (place_id, transition_id) VALUES (:place_id, :transition_id)

[{'place_id': 1, 'transition_id': 1}]

INSERT INTO place_output (place_id, transition_id) VALUES (:place_id, :transition_id)

[{'place_id': 2, 'transition_id': 2}, {'place_id': 3, 'transition_id': 2}]

SELECT place.place_id AS place_place_id, place.name AS place_name, 

ip_alias.transition_id AS ip_alias_transition_id, ip_alias.name AS ip_alias_name,

op_alias.transition_id AS op_alias_transition_id, op_alias.name AS op_alias_name

FROM place

LEFT OUTER JOIN place_output ON place.place_id = place_output.place_id

LEFT OUTER JOIN transition AS ip_alias ON ip_alias.transition_id = place_output.transition_id

LEFT OUTER JOIN place_input ON place.place_id = place_input.place_id

LEFT OUTER JOIN transition AS op_alias ON op_alias.transition_id = place_input.transition_id

ORDER BY place.oid, place_output.oid, place_input.oid

{}

(1, u'place1', 1, u'transition1', 1, u'transition1')

(1, u'place1', 2, u'transition2', 1, u'transition1')

(2, u'place2', 1, u'transition1', None, None)

(2, u'place2', 2, u'transition2', None, None)

(3, u'place3', 2, u'transition2', 1, u'transition1')

[<__main__.Place object at 0x778b10>, <__main__.Place object at 0x778950>, <__main__.Place object at 0x778bb0>]

On Dec 2, 2005, at 4:07 PM, Shuo Yang wrote:

On 12/2/05, Michael Bayer <[EMAIL PROTECTED]> wrote:

I think thats going to give you a circular eager relationship.  Also, a
circular thing like this needs to be set up in a certain way so that you
have both original mappers pointing to each other.  The thing about the
right relationships being on attached to original mappers is that the
commit() dependencies are set up and maintained by those first mappers.
The first mapper created by default will track new objects and updates to
existing objects, so it needs to be aware of dependencies since it will be
called upon to set those up when you say "commit".

This would work better if you defined the relationships with lazy
relationships, and used mapper options to create eager-loading versions of
those mappers:

Place.mapper = mapper(Place, place)

Transition.mapper = mapper(Transition, transition, properties = dict(
     inputs = relation(Place.mapper, place_output, lazy=True),
     outputs = relation( Place.mapper, place_input, lazy=True),
     )
)

Place.mapper.add_property('inputs',
    relation(Transition.mapper, place_input, lazy=True))
Place.mapper.add_property('outputs',
    relation(Transition.mapper , place_output, lazy=True))

Those are the circular lazy mappers.  Right off, I would be curious to try
commit()ing some objects which are set up that way to make sure it doesnt
get confused.  I am pretty sure I need to make some fixes just to support
the above pattern, with regards to commit().  While having two mappers
point to each other is not a problem with a one-to-many relationship, with
a many-to-many relationship I dont think the above is going to work
correctly right now.

Added this to the test:

Index: double.py
===================================================================
--- double.py   (revision 671)
+++ double.py   (working copy)
@@ -71,6 +71,9 @@
             )
         )

+        Place.mapper.add_property('inputs', relation(Transition.mapper, place_input, lazy=True))
+        Place.mapper.add_property('outputs', relation(Transition.mapper, place_output, lazy=True))
+
         tran = Transition('transition1')
         tran.inputs.append(Place('place1'))
         tran.outputs.append(Place('place2'))


And indeed, you raised a circular dependency error :):

  File "/Projects/sqlalchemy/lib/sqlalchemy/topological.py", line 64, in sort
    raise "Circular dependency detected " + repr(edges) + repr(queue)
Circular dependency detected {Mapper|Place|place  (idself=1079142924): {Mapper|Transition|transition  (idself=1079142892): True}, Mapper|Transition|transition  (idself=1079142892): {Mapper|Place|place  (idself=1079142924): True}}[]
 

Then assuming the above works, you can make new mappers that handle the
eager thing.  These mappers would just deal with loads, and both of them
reference the corresponding lazy mapper, so there is no circular eager
relationship:

Place.eagermapper = Place.mapper.options(
     eagerload('inputs', selectalias='ip_eager'),
     eagerload('outputs', selectalias='op_eager'))

Transition.eagermapper = Transition.mapper.options(
       eagerload('inputs', selectalias='ip_eager'),
       eagerload('outputs', selectalias='op_eager'))

I like it. :D 

You could also have those eagermappers be assigned to 'mapper' and have
the two lazy mappers assigned to something else, like 'lazymapper', or
whatever, having the mapper attached to the class is not really important
in fact.

The issue with the above options calls are, you cant do them yet.  I need

to add the 'selectalias' option to the eagerload() option, and also its
not propigated even if you have 'selectalias' on the lazy option.  So in
the meantime, you probably have to instead create the eagermapper
completely as you already did below, i.e.:

Place.eagermapper = mapper(Place, place, properties = dict(
     inputs = relation(Transition.mapper , place_input, lazy=False,
selectalias='ip_alias_t'),
     outputs = relation(Transition.mapper, place_output, lazy=False,
selectalias='op_alias_t'),
     )
)

Since you are jumping right into some pretty ambitious patterns, ill have
to test all of this stuff out tonight or later in the weekend and add new
logic to cope with a many-to-many circular relationship, as well as add
those options to eagerloader().

Didn't mean to make you work so hard! :p  Keep up the good work!  Now there are two things I'll be waiting for: double eagerload for sqlalchemy, and zblog paste template.

Shuo Yang wrote:
> Thanks Mike.  I like this solution better.
>
> Now, if I want to give similar properties to Place.mapper, would I do
> something like this:?
>
> -----------------------------
>
> Place.mapper = mapper(Place, place)
>
> Transition.mapper = mapper(Transition, transition, properties = dict(
>     inputs = relation(Place.mapper, place_output, lazy=False,
> selectalias='op_alias_p'),
>     outputs = relation(Place.mapper , place_input, lazy=False,
> selectalias='ip_alias_p'),
>     )
> )
>
> Place.mapper = mapper(Place, place, properties = dict(
>     inputs = relation(Transition.mapper, place_input, lazy=False,
> selectalias='ip_alias_t'),
>     outputs = relation(Transition.mapper, place_output, lazy=False,
> selectalias='op_alias_t'),
>     )
> )
>
> -----------------------------
>
>
>
>
> On 12/2/05, Michael Bayer < [EMAIL PROTECTED]> wrote:
>>
>> Hey John -
>> Well, this one really kicked my ass for quite awhile, as each fix to the
>> issue revealed that the entire approach of using aliases to eager load
>> just
>> doesnt generally work.
>>
>> the biggest issue being, if you map the Place class against the place
>> table, as well as the output_place and input_place aliases, when you
>> create
>> new Place objects, they are "dependency-sorted" based on the original
>> mapper, i.e. the "place" table mapper, which has no idea about the
>> relationships inside the Transition.mapper , so it will screw up often if
>> not always.
>>
>> So while you can now stick an Alias object inside a relation(), you dont
>> have to.  A more concise syntax that addresses the issue at a more
>> specific
>> spot has been added:
>>
>>         Place.mapper = mapper(Place, place)
>>         Transition.mapper = mapper(Transition, transition, properties >
>> dict(
>>             inputs = relation(Place.mapper, place_output, lazy=False,
>> selectalias='op_alias'),
>>             outputs = relation(Place.mapper, place_input, lazy=False,
>> selectalias='ip_alias'),
>>             )
>>         )
>>
>> Which properly saves new Transition and Place objects and their
>> relationships.  Upon select it produces a query like:
>>
>> SELECT transition.transition_id AS transition_transition_id,
>> transition.name AS transition_name,
>> ip_alias.place_id AS ip_alias_place_id, ip_alias.name AS ip_alias_name,
>> op_alias.place_id AS op_alias_place_id, op_alias.name AS op_alias_name
>> FROM transition LEFT OUTER JOIN place_input ON transition.transition_id
>> > place_input.transition_id
>> LEFT OUTER JOIN place AS ip_alias ON ip_alias.place_id >
>> place_input.place_id
>> LEFT OUTER JOIN place_output ON transition.transition_id >
>> place_output.transition_id
>>  LEFT OUTER JOIN place AS op_alias ON op_alias.place_id >
>> place_output.place_id
>> ORDER BY transition.oid, place_input.oid, place_output.oid
>>
>> But despite using the two alias names, the Place object is still mapped
>> against the original 'place' table, and EagerLoader performs some
>> translation upon the rows coming back in so that they appear to be
>> selected
>> against 'place' instead of 'ip_alias' or 'op_alias'.
>>
>> There is also a test case based on the one you gave me inside
>> test/double.py .
>>
>> - mike
>>
>>
>> On Dec 1, 2005, at 1:10 PM, Shuo Yang wrote:
>>
>>
>> On 11/30/05, Michael Bayer < [EMAIL PROTECTED] > wrote:
>> >
>> > Michael Bayer wrote:
>> > > then if that doesnt work, you can also say:
>> > >
>> > >     Transition.mapper = mapper(Transition, transition, properties >
>> > dict(
>> > >          inputs = relation(Place, output_place, place_output,
>> > > primaryjoin=and_(output_place.c.foo==place_output.c.foo,
>> > >           place.c.bar==place_output.c.bar)
>> > > lazy=False),
>> >
>> > err that should be:
>> >
>> >      Transition.mapper = mapper(Transition, transition, properties > >
>> dict(
>> >           inputs = relation(Place, output_place, place_output,
>> > primaryjoin=place.c.bar==place_output.c.bar,
>> > secondaryjoin=output_place.c.foo==place_output.c.foo,
>> >    ...
>> > lazy=False),
>>
>>
>>
>> ...
>> outputs = relation(Place, input_place, place_input, lazy=False),
>>   File "build/bdist.linux-i686/egg/sqlalchemy/mapper.py", line 89, in
>> mapper
>>   File "build/bdist.linux-i686/egg/sqlalchemy/mapper.py", line 264, in
>> _init_properties
>>   File "build/bdist.linux-i686/egg/sqlalchemy/mapper.py", line 1007, in
>> init
>>   File "build/bdist.linux-i686/egg/sqlalchemy/mapper.py", line 682, in
>> init
>>   File "build/bdist.linux-i686/egg/sqlalchemy/mapper.py", line 756, in
>> _match_primaries
>> AttributeError: 'Alias' object has no attribute 'table'
>>
>> Now it's this :p
>>
>>
>
>
> --
> ---------------------------------------------------------------------------------------------------
> John S. Yang
>

--
---------------------------------------------------------------------------------------------------
John S. Yang

--
---------------------------------------------------------------------------------------------------
John S. Yang