On Apr 26, 2014, at 10:59 AM, Peder Husom <pederhu...@gmail.com> wrote:
> Hi, I've been to the IRC channel and gotten alot of help from "inklesspen", > but I can't seem to figure this out. > > I have these tree tables; > > Users > - iduser > - name > > Companies > - idcompany > - name > > CompaniesUsers > - companyid > - userid > - owner (TINYINT|Boolean) > > > Now in my "Company" class I want users to select all users that are not > owners. Something like this; > select * from users u where u.iduser in (select userid from companiesusers cu > where cu.companyid = 1 and cu.owner = 0); > > I've tried playing around with association_proxy and relationships but I want > seems to properly add the two together. > Here is a sample of one of my tries; > https://gist.github.com/anonymous/11293742 > > Would be grateful if anyone could give me some hints, and I would love an > example. > The examples in the docs are great, but they don't explain how do "use them > together". the query can be emitted as follows: subq = session.query(CompaniesUser.id).filter(CompaniesUser.companyid == 1).filter(CompaniesUser.owner == 0) session.query(User).filter(User.id.in_(subq)) Were you hoping to have this effect? some_company = Session.query(Companies).first() some_company.non_user_owners ? So the easiest way to get this query off of Companies immediately is just to use a @property (see http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#building-query-enabled-properties): class Companies(...): @property def non_user_owners(self): return object_session(self).query(User).... <same query> If OTOH you do in fact want this query to take the current Company.id into account, this would be simple using primaryjoin/secondaryjoin/secondary, it just requires that the IN is unwrapped into a regular join criterion. Assume the name of the CompaniesUsers table is "companiesusers": class Companies(...): non_user_owners = relationship("Users", primaryjoin="Companies.id == companiesusers.c.companyid", secondary="companiesusers", secondaryjoin="and_(companiesusers.c.userid=Users.id, companiesusers.owner == 0)" ) join conditions are documented at http://docs.sqlalchemy.org/en/rel_0_9/orm/relationships.html#configuring-how-relationship-joins. Just curious, when you say "don't explain how to "use them together"", is that including this documentation section? What I've done here is adapt your cases to examples which are already present in that section - what exactly is missing? -- You received this message because you are subscribed to the Google Groups "sqlalchemy" group. To unsubscribe from this group and stop receiving emails from it, send an email to sqlalchemy+unsubscr...@googlegroups.com. To post to this group, send email to sqlalchemy@googlegroups.com. Visit this group at http://groups.google.com/group/sqlalchemy. For more options, visit https://groups.google.com/d/optout.
