Browsing through a list I found many posts concerning many-to-many relationships. There were also some solutions that unfortunately seemed to me rather to be walkarounds. As simplicity is the thing than made me use sqlobject, I would love keep it that way :) Therefore, I'm wondering if it would be possible to implement something like this:
 
Let's imagine reference example Users/Roles example but add some more complexity:
 
class User(SQLObject):
    Name = StringCol(length=20)# there will go: Mary, John
    Roles = RelatedJoin('Role')
    Workplaces = RelatedJoin('Workplace')
 
class Role(SQLObject):
    Name = StringCol(length=20) #there will go: president, engineer, sales rep.
    Users = RelatedJoin('User')
 
class WorkPlace(SQLObject):
    Name = StringCol(lenght=20) #there will go: London, Paris, New York
    Users = RelatedJoin('Workplace')
 
Now some simple case from the functionality point of view: How can we find all users that are engineers and work in London? Of course currently you probably have to use sqlbuilder, but THIS IS EVIL for me. Why? Sqlbuilder forces you to operate on the table fields instead of objects, breaking all the ORM idea. Now, imagine how it could work:
 
User.selectBy('engineer' in Roles, 'London' in Workplaces)
 
this shall result in generating SINGLE db query. Better yet sqlbuilder could be replaced with 'querybulder' translating object queries to pure sql like:
User.select("'%engineer%' in User.Roles and '%London%' in User.Workplaces")
 
This idea came basically from my friend's project (www.sooda.org) that is, apart from the fact of .net-only, extremely extensible by implementing sql-like pseudo language operating purely on objects.
 
Is it possible to do it in sqlobject? Or maybe I am missing some point?
 
Regards,
Michal