How to load from aliased table

[mjallday]

I'm trying to optimise a slow performing CTE, the raw SQL is fast but I'm having trouble getting the generated SQL from SA to be the same. My issue is that the generated SQL contains 3 outer, self-referencing joins which slow it down dramatically. 

Here's the SQL I'm aiming for:

WITH RECURSIVE related_ledger(guid, created_at, reference_ledger_guid) AS (

    SELECT ledger.guid AS guid, ledger.created_at AS created_at, ledger.reference_ledger_guid AS reference_ledger_guid                               

    FROM ledger

    WHERE ledger.guid = %(guid_1)s 

    UNION ALL 

    SELECT ledger.guid AS ledger_guid, ledger.created_at AS ledger_created_at, ledger.reference_ledger_guid AS ledger_reference_ledger_guid

    FROM ledger, related_ledger AS rla

    WHERE ledger.reference_ledger_guid = rla.guid

)                                                                                                                                                              

SELECT related_ledger.guid AS related_ledger_guid, related_ledger.created_at AS related_ledger_created_at, related_ledger.reference_ledger_guid AS related_ledger_reference_ledger_guid

FROM related_ledger;

And here's what my CTE within python looks like:

    related_ledger = Ledger.query.filter(

        Ledger.guid == self.guid

    ).cte('related_ledger', recursive=True)

    related_ledger_alias = aliased(related_ledger,

                                   name='rl')

    rec = Ledger.query.filter(

        Ledger.reference_ledger_guid == related_ledger_alias.c.guid

    )

    union = related_ledger.union_all(rec)

Now I've got two ways to return the data as SA objects (as opposed to straight tuples, with which this query is zippy):

    Ledger.query.select_from(union)

This is slow as it generates something like this:

    with related_ledger as (

        ...

    )

    select ...

    FROM related_ledger 

    LEFT OUTER JOIN ledger AS ledger_1 ON related_ledger.guid = ledger_1.reference_ledger_guid 

    LEFT OUTER JOIN ledger AS ledger_2 ON ledger_1.guid = ledger_2.reference_ledger_guid 

    LEFT OUTER JOIN ledger AS ledger_3 ON ledger_2.guid = ledger_3.reference_ledger_guid

I also tried

    Ledger.query.from_statement(union.select()) 

I would expect this to be fast since it's mapping the original columns in the query to what the object expects. However, I'm having trouble with this syntax and getting this error:

     ** NoSuchColumnError: "Could not locate column in row for column 'ledger.guid'"

This appears to be because the table is aliased as reference_ledger.

So my questions are:

1. Am I writing the CTE in the wrong way to be the select_from method?

2. How can I use the from_statement method to work with an aliased table?

Thanks!

[Michael Bayer]

On Sep 17, 2012, at 1:38 PM, mjallday wrote:

I also tried

    Ledger.query.from_statement(union.select()) 

I would expect this to be fast since it's mapping the original columns in the query to what the object expects. However, I'm having trouble with this syntax and getting this error:

     ** NoSuchColumnError: "Could not locate column in row for column 'ledger.guid'"

OK, try out tip, since there was a bug exactly in this area, that is from_statement(union), that is fixed for 0.7.9.  there's also a bunch of CTE-related fixes.

This appears to be because the table is aliased as reference_ledger.

So my questions are:

1. Am I writing the CTE in the wrong way to be the select_from method?

Those LEFT OUTER JOINs look like you have lazy=False set up, or something, since I don't see you otherwise specifying any joins.  I usually leave joined-loads off at the relationship() level.  Especially if you're self-referential, and dealing with CTEs and stuff, you don't want any complexity enabled by default on your mappings.    That will probably fix this issue.

2. How can I use the from_statement method to work with an aliased table?

see above tip recommendation.

[mjallday]

You're absolutely correct, it was the relationship declaration that was my issue. Removing the lazy='joined' from the code removed the outer joins. 

I really appreciate the speedy and concise answer. Thank you very much!