Thanks for the response.  I do understand all of that, but in the doc (, it gives the following example:
    join=LEFTJOINOn(None, Table1,
           == Table1.q.value))
will return the following sql statement
SELECT * FROM my_table
LEFT JOIN table2 ON = table1.value;
I assumed the * was meant as all columns.

My problem is that I'm trying to create a single SelectResult (I think) object to pass to a PaginateDataGrid widget in TurboGears.  I know I can do all of the things below to get the data, but I don't know of a way to do it and get it into a SelectResult to pass along.

Thanks again for the information, at least I know now that what I'm trying to do isn't going to work.  Anyone have any suggestions or run into this situation before?  Am I looking at this all wrong?


Oleg Broytmann wrote:
On Fri, Aug 03, 2007 at 10:15:50AM -0500, Jim Steil wrote:
x =

The problem is that I don't get any of the fields from the RailStatus 
table in the select.

   The problem is not with the join, but with your understanding of
.select(). can do simple or complex joins, but it
operates on the primary class, RailCar in your case, and only return fields
(attributes, columns) of the table/class, not of any table in a join. Let's
look why. You have declared the class like this:

class RailCar(SQLObject):
   brand = StringCol()

Now SQLObject knows there are two columns in the table - "id" and "brand".
When you call SQLObject returns a list of RailCar
instances, and these instances can only contain the declared attributes.
There is no room for attributes from different classes. There are two or
more "id" columns in a join - what should return? Imagine there are
two "name" columns, one in RailCar and the other in RailStatus, what should return for an every row in a join?

What do I need to do to get the fields from both 
the joined tables into the result?  Any pointers would be appreciated.

Declare a relation between these tables/classes:

class RailStatus(SQLObject):
   railCar = ForeignKey("RailCar")

While looping over use "railCar" that points to
RailCar table. SQLObject fetches an instance:

for status in
   car = status.railCar
   print car
   print car.brand

   Or you can do vice versa:

class RailCar(SQLObject):
   railStatus = ForeignKey("RailStatus")

   Or you can do both. You can use ForeignKey or SingleJoin for one-to-one
relation, MultipleJoin for one-to-many or RelatedJoin for many-to-many.