On Aug 11, 2010, at 1:19 PM, botz wrote:
> Thanks for that... I didn't manage to get it to work straight off, but
> it seems like I'm close.
>
> Tried a couple things just to force it to behave as I wanted.
>
>
> # defined the selectable
> selectable = select( [ sql.column( 'x' ), sql.column('y')],
> from_obj =
> [ func.foo_props(bindparam( 'x' )) ] ,
> bind=...
> ).alias( 't')
>
>
> # mapping
> mapper( FooProps , selectable , primary_key = [ selectable.c.x ] )
>
> this fails with:
> InvalidRequestError: Could not find any Table objects in mapped table
> 'SELECT x,y FROM foo_props(%(param_1)s)'
>
> #in sqlalchemy.orm.mapper the find_tables() call defaults to
> include_aliases = False, forced it to be true:
>
> def _configure_pks(self):
> self.tables =
> sqlutil.find_tables(self.mapped_table,include_aliases=True)
>
> (include_selectable = True worked also)
I'd have to spend some time thinking about what options exist there. It
might be better that the recipe for "mapping to a function" be done more
similarly to http://www.sqlalchemy.org/trac/wiki/UsageRecipes/Views, where we
are using a table() object on the inside + custom compilation. it would at
least eliminate one level of subquerying. But also I think the whole
"self.tables" requirement is probably not even needed (#1876).
>
> then define the relationship:
>
> mapper( Foo , foo_table, properties = dict( props =
> relation( FooProps,
>
> primaryjoin = foo_table.c.x = selectable.c.x, foreignkey =
> [ selectable.c.x ] )))
>
>
> So now when I invoke the relationship
> a.foo
>
> there a call:
>
> SELECT t.x, t.y
> FROM (SELECT x,y
> FROM foo_props(%(x)s)) AS t
> WHERE %(param_1)s = t.x
>
> but only the param_1 is defined in the parameters to the call. I can
> hack it by just setting the initial bindparam('x') to be
> bindparam('param1'),
> but that's not really ideal obviously
What is implied by relationship() is that you're joining and limiting a full
set of records using a join condition. This implies that the mapped selectable
would be returning a set of rows corresponding to all possible child
collections, not just the collection of a single parent based on a bind. So
here you'd perhps have to wrap your function into a correlated subquery to
produce that effect, or rewrite the function so that it returns all possible
child collections with an extra set of columns indicating those of the parents.
There is a recipe to get binds into a relationship, but not for this same use
case, at http://www.sqlalchemy.org/trac/wiki/UsageRecipes/GlobalFilter - it
probably is also not what you're looking for here.
So if all those options and not feasable here, then you are gaining nothing by
using relationship() in the first place - its already a "viewonly"
relationship, and for the case of "eager loading" as well as any kind of
expression construction, which is the other reason you'd use relationship and
not just a method on your class, those aren't feasable either since again, your
mapped function doesn't represent the full set of possible results.
For an attribute that is used for nothing other than Foo.foobars hitting the
database and returning an iterable, you don't need relationship(). you just
put a method / @property on your Foo class that aquires the current session
using object_session(self). If you also wanted class level expressions, such
as query(Foo).filter(Foo.foobars.any(...)), to have some kind of meaning that
you define, that can also be constructed without using relationship() using
some techniques that I plan on adding to a new extension soon, which builds
upon examples/derived_attributes.
--
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.
