[storm] Mixing explicit and implicit joins

James Henstridge james.henstridge at canonical.com
Mon Mar 16 05:24:37 GMT 2009


On Fri, Mar 13, 2009 at 6:09 AM, Shaun McCance <shaunm at gnome.org> wrote:
> I'm trying to figure out how to force a single left join while
> still getting implicit joins with other tables.  Here's a very
> stripped down version of what I've got:
>
> Branch
>  ident
>  mod_person_ident (-> Entity)
> Entity
>  ident
> SetModule
>  subj_ident (-> ReleaseSet)
>  pred_ident (-> Branch)
>
> Here's the wrong version:
>
> store.find((Branch, Entity),
>  Branch.mod_person_ident == Entity.ident,
>  Branch.ident == SetModule.pred_ident,
>  SetModule.subj_ident == "something")
>
> The problem is that with doesn't return any Branch objects
> where mod_person_ident==None.  This does what I want:
>
> join = (LeftJoin(Branch, Entity,
>          Branch.mod_person_ident == Entity.ident),
>        SetModule))
> store.using(join).find((Branch, Entity),
>  Branch.ident == SetModule.pred_ident,
>  SetModule.subj_ident == "something")
>
> This does what I want.  The problem is that this all happens
> in a utility function that returns a ResultSet.  That utility
> function takes *args just like store.find, and it could be
> called without a reference to SetModule, or with a reference
> to some other table.
>
> So I'd like to get the same implicit list of tables I get
> with store.find, but force that single left join.  If there
> were some function to scan *args and give me a list of the
> tables I need, that would do the trick.
>
> Any ideas?

I was discussing something like this with Tim Penhey on IRC recently,

The implicit tables behaviour is implemented basically as follows:

1. ResultSet object generates a Select() object that represents the
query to perform.  It passes this to the connection to execute.
2. Connection runs compile() on the Select object to generate SQL.
3. The compile() function registered for Select objects compiles all
the clauses (WHERE, GROUP BY, HAVING, ORDER BY).
4. Compiling some parts of the expression tree (Column and Table
nodes) will add tables to the state.auto_tables list.
5. The referenced tables in state.auto_tables are used as the FROM
clause, assuming that no tables have been set explicitly.

When you use store.using(), you are specifying the tables explicitly
so in (5) the list of collected tables gets discarded.

When writing the SQLObject compatibility layer, we ended up needing
something in between: specify some joins explicitly but still pick up
any remaining tables via the auto_tables method listed above.  This
sounds a lot like what you are after.

The solution used for the compatibility layer was a special
AutoTables(expr, tables, replace=False) expression object.  The
compilation function for this type adds the given tables (or joins) to
state.auto_tables, which adds the joins to the query while not
requiring you to explicitly list every table for the query.

So that will probably solve your problem.  The only question I'm not
sure about is: do we consider AutoTables part of the public API we
recommend people to use, or just an implementation detail of the
SQLObject compatibility layer?

Given that there is demand for such a feature, I think we should
expose something like this.  I am not sure AutoTables is the best API
for this though.

James.



More information about the storm mailing list