> > I would like to build an SQL query with sqlbuilder that contains > > several IN operators but I don't seem to succeed. The model is quite > > simple, there are several cities, each city contains a number of > > buildings, each building contains a number of floors and each floor > > contains a number of rooms. So it's pretty straightforward: > > > > class city(SQLObject): > > name = StringCol( ) > > buildings = MultipleJoin( 'building' ) > > > > class building(SQLObject): > > name = StringCol( ) > > complex = ForeignKey( 'city' ) > > floors = MultipleJoin( 'floor' ) > > > > class floor(SQLObject): > > name = StringCol( ) > > building = ForeignKey( 'building' ) > > rooms = MultipleJoin( 'room' ) > > > > class room(SQLObject): > > name = StringCol( ) > > floor = ForeignKey( 'floor' ) > > > > Now I would like to have a single SQL query for selecting all distinct > > room names in a given city. The problem is that with the above model > > both building.q.complex and building.q.floors throw an AttributeError > > so I can't use these in a room.select( ) statement together with IN > > and my impression so far has been that in such a select statement one > > should use these magic 'q' variables.
[Claudio Martinez] > I think this should work. Didn't test it. > > from sqlbuilder import LEFTJOINOn > > joins = [] > joins.append(LEFTJOINOn(None, floor, room.q.floorID==floor.q.id)) > joins.append(LEFTJOINOn(None, building, floor.q.buildingID==building.q.id)) > joins.append(LEFTJOINOn(None, city, building.q.cityID==city.q.id)) > > results = room.select(city.q.name == 'New York', join=joins) Thanks very much, it does work. [Oleg] > But there is no "city" column in "building". I would recommend you to > start with foreign keys that exactly match referenced tables: Sorry, that was also part of the typo. ------------------------------------------------------------------------- Take Surveys. Earn Cash. Influence the Future of IT Join SourceForge.net's Techsay panel and you'll get the chance to share your opinions on IT & business topics through brief surveys-and earn cash http://www.techsay.com/default.php?page=join.php&p=sourceforge&CID=DEVDEV _______________________________________________ sqlobject-discuss mailing list [email protected] https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
