Joins in SQLObject? How?

[Steve Bergman]

I am having trouble understanding how to use SQLObject in a relational
way in TurboGears.

I have classes in model.py that look something like the definitions below.

I want to generate an html table that includes a line for each existing
"Project" item and which includes the fields:

clientName, clientAddress, clientCity, clientState, ClientZip,
projectDesc ProjectHours, billable

What is the SQLObject way of getting a suitable result set for
generating such a table? (I'm using PostgreSQL)

I think I need to be using LEFTJOINOn or similar, but it seems to simply
return the text of an SQL query and I have not been able to figure out
how to use the result it returns.


Thanks for any enlightenment.

-Steve

-----------------

class Client(SQLObject):
_connection = hub

clientName = StringCol(length=50)
clientAddress = StringCol(length=50)
clientCity=StringCol(length=25)
clientState=StringCol(length=2)
clientZip=StringCol(length=10)
projects = MultipleJoin('Project')


class Project(SQLObject):
_connection = hub

projectDesc = StringCol(length=50)
projectHours = DecimalCol(size=6, precision=2)
billable = EnumCol(enumValues=['y','n','c'])
clientId = ForeignKey('Client')
client = SingleJoin('Client')

[SuperJared]

Steve,

Check out the FAQ on the SQLObject page:
http://sqlobject.org/FAQ.html#how-can-i-do-a-left-join

I haven't done it yet, but their docs are fabulous. Please let us know
how it goes!

SuperJared

[bon...@gmail.com]

For this particular case, I don't think you need other joins.

A simple:

client = Client.get(1)
for x in client.projects: print x.projectDesc

should work.

However, if you have lots of rows in projects(for each client), beware
of the memory implication as everything will be retrieved once you
start to access it.

Basically, SQLObject can handle TWO table N to N situation pretty well.
It is when you need complex cross table retrieve/update then you need
to go back to raw SQL, as that kind of situation just cannot be
modeled.

[Steve Bergman]

bon...@gmail.com wrote:

>For this particular case, I don't think you need other joins.
>
>A simple:
>
>client = Client.get(1)
>for x in client.projects: print x.projectDesc
>
>should work.
>
>
>

Thanks for the response.

Actually, I did end up doing sonething like that. It works, but it
feels like DBase III.

Obviously I am not thinking about the sqlbuilder functions in the right
way, because I have not managed to get anything but a plethora of
different error messages.

I've started a completely new test project that looks like this:
--------------------------

#model.py:
from sqlobject import *
from turbogears.database import PackageHub
hub = PackageHub("sqltest")
__connection__ = hub

class Person(SQLObject):
_connection = hub
lastName = StringCol(length=25)
firstName = StringCol(length=15)
addresses = MultipleJoin('Address')

class Address(SQLObject):
_connection = hub
streetAddr = StringCol(length=25)
city = StringCol(length=15)
state = StringCol(length=2)
zip = StringCol(length=10)
person = ForeignKey('Person')

---------------------------

#controllers.py
import turbogears
import cherrypy
from turbogears import controllers
from sqlobject.sqlbuilder import LEFTJOINOn
from model import hub, Address, Person

class Root(controllers.Root):
@turbogears.expose(html="sqltest.templates.welcome")
def index(self):
result = Address.select(join=LEFTJOINOn(None, Person,
Address.q.person == Person.q.id))
return dict(dummy="dummy")

-----------------------------
I have added a Person (id=1) and an Adrress with a person field that
points to 1

And when I load the page, I get:

Traceback (most recent call last):
File "/usr/lib/python2.4/site-packages/CherryPy-2.1.0_rc2-py2.4.egg/cherrypy/_cphttptools.py", line 271, in run
main()
File "/usr/lib/python2.4/site-packages/CherryPy-2.1.0_rc2-py2.4.egg/cherrypy/_cphttptools.py", line 502, in main
body = page_handler(*args, **cherrypy.request.paramMap)
File "/usr/lib/python2.4/site-packages/TurboGears-0.8a2-py2.4.egg/turbogears/controllers.py", line 121, in newfunc
output = func(self, *args, **kw)
File "/home/steve/sqltest/sqltest/controllers.py", line 10, in index
result = Address.select(join=LEFTJOINOn(None, Person, Address.q.person == Person.q.id))
File "/usr/lib/python2.4/site-packages/SQLObject-0.7.0-py2.4.egg/sqlobject/sqlbuilder.py", line 362, in __getattr__
self.soClass.sqlmeta.columns[attr].dbName,
KeyError: 'person'



What am I doing wrong????

[bon...@gmail.com]

And OO do make you getting away from SQL(mostly declarative) back to
imperative like dBase.

As for your error, you may need to use "personID" rather than person as
that seems to be a requirement for foreign key field.

[Steve Bergman]

bon...@gmail.com wrote:

>And OO do make you getting away from SQL(mostly declarative) back to
>imperative like dBase.
>
>As for your error, you may need to use "personID" rather than person as
>
>

Yes. I got past that. (I'm trying to get used to these "magic names".)
And I'm able to get a result, which seems to be a regular, sliceable,
select result and I can display, say, result[0].lastName. What I'm
trying to figure out now is how you actually access the fields in both
tables of the join. I'm only getting fields from one. I want to be
able to list all the addresses, one per line, with the person
information also on each line. Anyway, I've given up for now and have
gome back to my original project, as I have wasted far too much time
trying to make this work. The OO way is very inefficient, and wouldn't
be manageable if my schema were more complex than it is, but OO and for
loops will have to do for now.

Thanks again,
Steve

[bon...@gmail.com]

something along the line of :

persons = Person.select(some condition)

for x in persons:
for y in persons.addresses:
print x.name, y.address

Or turn it into a list of tuples

[(x.name, y.address) for x in persons for y in x.addresses]

A poor man's SELECT statement.

Of course it is not as clean as SQL(especially when you have complex
situation). OODB may be a fashionable thing but plain old SQL beats it
for real applications. In fact, even in my good old ISAM days(on IBM
systems), I used multiple pass programs to flatten trees like the above
for reporting.

Use both in SQLObject, it is possible.

[Steve Bergman]

bon...@gmail.com wrote:

>
>Use both in SQLObject, it is possible.
>
>
>

From what I gather from early May 2005 postings to the SQLObject
discussion list, it is, in fact, not possible.

The sqlbuilder join methods can only return values from one table.
Their actual functionality, if any, would seem to be a mystery.

-Steve

[bon...@gmail.com]

you can, may not be sqlbuilder.

http://thraxil.org/users/anders/posts/2005/04/18/raw-queries-with-SQLObject/

Also, you can use database view to mimick that, that is not very
dynamic though.

[Kevin Dangoor]

On 10/21/05, Steve Bergman <st...@rueb.com> wrote:
> From what I gather from early May 2005 postings to the SQLObject
> discussion list, it is, in fact, not possible.
>
> The sqlbuilder join methods can only return values from one table.
> Their actual functionality, if any, would seem to be a mystery.

This comes up often enough that there should probably be a proposed
way to run queries that return something like a result set, something
that bridges SQLObject and the database a little more.

Generally speaking, when working with an OR mapper you *want* to work
with your objects. You get all of the appropriate behavior, and you
also get your objects cached. One should very infrequently be wanting
to pull "raw" data from the database.

Kevin

--
Kevin Dangoor
Author of the Zesty News RSS newsreader

email: k...@blazingthings.com
company: http://www.BlazingThings.com
blog: http://www.BlueSkyOnMars.com

[bon...@gmail.com]

Kevin Dangoor wrote:
> On 10/21/05, Steve Bergman <st...@rueb.com> wrote:
> > From what I gather from early May 2005 postings to the SQLObject
> > discussion list, it is, in fact, not possible.
> >
> > The sqlbuilder join methods can only return values from one table.
> > Their actual functionality, if any, would seem to be a mystery.
>
> This comes up often enough that there should probably be a proposed
> way to run queries that return something like a result set, something
> that bridges SQLObject and the database a little more.

Indeed.

>
> Generally speaking, when working with an OR mapper you *want* to work
> with your objects. You get all of the appropriate behavior, and you
> also get your objects cached. One should very infrequently be wanting
> to pull "raw" data from the database.

But that as Steve said, is too dBase/ISAM like which is going backward
for many situation. There is no need to pull raw data but a need to
turn a result set from a SQL query as an SQLObject and use the
excellent mapping of SQLObject that does all the internal data
conversion etc.

[Kevin Dangoor]

On 10/21/05, bon...@gmail.com <bon...@gmail.com> wrote:
> > Generally speaking, when working with an OR mapper you *want* to work
> > with your objects. You get all of the appropriate behavior, and you
> > also get your objects cached. One should very infrequently be wanting
> > to pull "raw" data from the database.
> But that as Steve said, is too dBase/ISAM like which is going backward
> for many situation.

Not exactly. In my mind, there's a big difference between what an OR
mapper does and the old ISAM style. The difference is that you're
getting whole, completely baked, complete-with-methods objects. If
your objects are nothing more than data containers, then an OR mapper
is not a huge benefit. But, if your objects have important methods on
them (plus getters and setters and the like), then it is a benefit.

For some more thoughts on this, you can look at Martin Fowler's Anemic
Data Model anti-pattern page:
http://www.martinfowler.com/bliki/AnemicDomainModel.html

> There is no need to pull raw data but a need to
> turn a result set from a SQL query as an SQLObject and use the
> excellent mapping of SQLObject that does all the internal data
> conversion etc.

There will likely be limitations on what SQLObject can accomplish, and
the implementation will become complex if you try to bring too much of
SQLObject's good features over. Getting a half-there, half-not
SQLObject back would get nasty very quickly. Consider this:

class Foo(SQLObject):
somefield = StringCol()
someotherfield = StringCol()

def _get_someotherfield(self):
return self.somefield + "-" + self._SO_getValue("someotherfield")

(usual caveats that this was written in mail, blah, blah)

So, if you selected out only "someotherfield", you'll either get only
the value of "someotherfield", without the expected prependage. Or,
you'll get an error, because you dont have the value of "somefield".

For those times when raw performance is key and you're looking up data
from other tables, I was thinking more along the lines of a thin
bridge between SQLBuilder and a straight DBAPI result. The only
difference may be that if you're getting IDs out in your results,
there may be convenience methods for grabbing the whole object and the
like. Otherwise, you get pretty much what DBAPI gives you directly.

Kevin

[bon...@gmail.com]

Kevin Dangoor wrote:
> Not exactly. In my mind, there's a big difference between what an OR
> mapper does and the old ISAM style. The difference is that you're
> getting whole, completely baked, complete-with-methods objects. If
> your objects are nothing more than data containers, then an OR mapper
> is not a huge benefit. But, if your objects have important methods on
> them (plus getters and setters and the like), then it is a benefit.
>
> For some more thoughts on this, you can look at Martin Fowler's Anemic
> Data Model anti-pattern page:
> http://www.martinfowler.com/bliki/AnemicDomainModel.html

May be, but I don't see it too differently, from the data point of
view. The methods can help but overall still less elegant than a SQL
statement, when I need complex queries. Of course, it really depends on
what the underlying object is. For business oriented tabulated data, I
don't see much benefit of an OO approach.