Thanks for the response. I do understand all of that, but in the doc
gives the following example:
MyTable.q.name == Table1.q.value))
will return the following sql statement
SELECT * FROM my_table
LEFT JOIN table2 ON my_table.name = 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 = RailCar.select(...)
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(). SQLObject.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:
brand = StringCol()
Now SQLObject knows there are two columns in the table - "id" and "brand".
When you call RailCar.select() 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 row.id should return? Imagine there are
two "name" columns, one in RailCar and the other in RailStatus, what
row.name 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:
railCar = ForeignKey("RailCar")
While looping over RailCarStatus.select() use "railCar" that points to
RailCar table. SQLObject fetches an instance:
for status in RailCarStatus.select():
car = status.railCar
Or you can do vice versa:
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.