On Apr 17, 2008, at 8:07 AM, jean-philippe dutreve wrote:
>
> Hi all,
>
> I'm trying to load a whole Tree of Account objects (Mapped instances)
> in a single SELECT with unlimited depth.
> I'm using PostgreSQL connectby function from the tablefunc module.
> It returns rows of each nodes in a depth first visit.
>
> sql = """
> SELECT acc_accounts.* FROM connectby('acc_accounts', 'account_id',
> 'parent_id', 'name', '%s', 0)
> AS t(keyid int, parent_keyid int, level int, name int),
> acc_accounts where keyid = account_id
> """
>
> accounts = Session.query(Account).from_statement(sql %
> root_account_id).all()
>
> After that, I try so iterate over the result list in order to set the
> account.children relation myself.
> But the problem is that as soon as I initialize the relation, a SQL
> select is issued:
>
> account.children = []
>
> How can I avoid these unnecessary selects on all nodes?
> Is there a better way to let SA populate the relation with the rows
> returned?
>
what youre doing is "eager loading" the "children" collections using a
custom SQL statement that SA has no awareness of. Therefore you'd
have to formulate some way of populating the "children" collections
yourself as rows are loaded. In this case, the parent and child rows
are all loaded vertically in the same set of columns. So this
implies that the point at which the receiving mapper handles a row and
places it somewhere, some intervention is required (if, OTOH, you were
receiving parent/child rows within distinct sets of result columns,
theres another approach used for that scenario).
An example of this exact style of intervention is available in the
distribution examples, in the file "examples/adjacencytree/
byroot_tree.py". This example uses a MapperExtension to change the
way SQLA appends new result rows to the object graph. The query
you're issuing should work well with the approach used in that example
script, so take a look at that. A key feature of this example
include that the "children" relation is configured as "lazy=None" so
that no lazy loads are emitted - you can probably get away with
leaving that with the default "lazy=True", which adds the advantage of
being able to load remaining "chlidren" collections which may have not
been loaded.
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to [email protected]
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
