Document bindparam usage in join condition docs

[Christoffer Olsen]

I've been using sqlalchemy for a fairly long time, but never touched upon using the bindparam concept in a join condition. It's an incredibly useful concept that saves me a lot of headaches, and I'm sad I didn't discover it before.

Maybe it could be documented at more accessible stage, somewhere around here? A use case where you can specify a bindparam for Address.city instead of using the static Boston value would be great.

I had a stab at writing an update, but I'm a bit out of water unfortunately.

[Mike Bayer]

So there's a recipe for that over at https://bitbucket.org/zzzeek/sqlalchemy/wiki/UsageRecipes/GlobalFilter, and the reason it's over there is that I think this technique is not at all simple or typical - for it to work fully (e.g., with relationship loading, not just querying) requires a lot of new constructs and understanding of how the APIs work, and that is too much to put in the main docs. One rule of the main docs is that we only put things that are really going to be widely used and are completely supported. So here I'd need to understand more specifically what use case you have in mind - e.g. the docs would be, "In the case where one has the need to <BLANK>"...

[Christoffer Olsen]

Yep, that recipe is where I ended up finding it in the first place. It's fair enough not to include things in the main documentation, but I find it to be an incredibly useful concept.

In our case, we used some gymnastics to load certain things that we don't know about before we have an incoming request, and certainly not at declaration time.

Something like this, pseudocoded a bit:

class Child(Base):
    age = Column(Integer())
    parent_id = Column(ForeignKey())
    parent = relationship(Parent, backref='children')

class Parent(Base):
    ...

Say I now want to load a parent and also eager load every child with the age of 15. Using bindparam I can create a primaryjoin with this parameter and run session.query(Parent).params(age=15).joinedload(Parent.children). That'll give me an eager load of just those, which is pretty darn useful if you have thousands of children. (Family planning apparently not implemented.)

Otherwise my understanding is that I would have no other way other than to either a) eager load the entire collection of children and using something like [child for child in self.children if age == x], or b) using a separate property with a static join condition for every scenario. Or managing a separate query in a not-very-declarative way.

I'm pretty sure I don't fully understand all the concepts around it yet, but it's made my life better, so here's a vote for promoting the concept in a reasonable way :)

[Christoffer Olsen]

Happy with the rationale given by Mike, so closing.