How to join a table with itself in the model

[Ed Singleton]

I want to join a table with itself in the model definition.

Something like:

class Page(SQLObject):
parent_page = ForeignKey('Page')
child_pages = MultipleJoin('Page')

but when I do, and I try to pull out the child pages I get the error:

OperationalError: (1054, "Unknown column 'page_id' in 'where clause'")

Any clues?

Thanks

Ed

[Ed Singleton]

Full traceback in case anyone's intrested:

Page handler: <bound method Root.index of <sti.controllers.root.Root
object at 0x01564590>>
Traceback (most recent call last):
File "c:\python24\lib\site-packages\CherryPy-2.2.1-py2.4.egg\cherrypy\_cphttptools.py",
line 105, in _run
self.main()
File "c:\python24\lib\site-packages\CherryPy-2.2.1-py2.4.egg\cherrypy\_cphttptools.py",
line 254, in main
body = page_handler(*virtual_path, **self.params)
File "<string>", line 3, in index
File "c:\python24\lib\site-packages\TurboGears-0.9a6-py2.4.egg\turbogears\controllers.py",
line 273, in expose
output = database.run_with_transaction(
File "c:\python24\lib\site-packages\TurboGears-0.9a6-py2.4.egg\turbogears\database.py",
line 221, in run_with_transaction
retval = func(*args, **kw)
File "<string>", line 5, in _expose
File "c:\python24\lib\site-packages\TurboGears-0.9a6-py2.4.egg\turbogears\controllers.py",
line 290, in <lambda>
mapping, fragment, *args, **kw)))
File "c:\python24\lib\site-packages\TurboGears-0.9a6-py2.4.egg\turbogears\controllers.py",
line 314, in _execute_func
output = errorhandling.try_call(func, *args, **kw)
File "c:\python24\lib\site-packages\TurboGears-0.9a6-py2.4.egg\turbogears\errorhandling.py",
line 71, in try_call
return func(self, *args, **kw)
File "D:\TG-Sites\STI\sti\controllers\root.py", line 19, in index
childpages = page.child_pages
File "<string>", line 1, in <lambda>
File "c:\python24\lib\site-packages\SQLObject-0.7.1dev_r1675-py2.4.egg\sqlobject\joins.py",
line 142, in performJoin
inst.id)
File "c:\python24\lib\site-packages\SQLObject-0.7.1dev_r1675-py2.4.egg\sqlobject\dbconnection.py",
line 600, in _SO_selectJoin
return self.queryAll("SELECT %s FROM %s WHERE %s = %s" %
File "c:\python24\lib\site-packages\SQLObject-0.7.1dev_r1675-py2.4.egg\sqlobject\dbconnection.py",
line 751, in queryAll
return self._dbConnection._queryAll(self._connection, s)
File "c:\python24\lib\site-packages\SQLObject-0.7.1dev_r1675-py2.4.egg\sqlobject\dbconnection.py",
line 311, in _queryAll
self._executeRetry(conn, c, s)
File "c:\python24\lib\site-packages\SQLObject-0.7.1dev_r1675-py2.4.egg\sqlobject\mysql\mysqlconnection.py",
line 60, in _executeRetry
return cursor.execute(query)
File "C:\Python24\lib\site-packages\MySQLdb\cursors.py", line 137, in execute
self.errorhandler(self, exc, value)
File "C:\Python24\lib\site-packages\MySQLdb\connections.py", line
33, in defaulterrorhandler
raise errorclass, errorvalue

OperationalError: (1054, "Unknown column 'page_id' in 'where clause'")

[Matthew Bevan]

Try:

> class Page(SQLObject):
> parent_page = ForeignKey('Page')

> child_pages = MultipleJoin('Page', joinColumn="parent_page_id")

Also useful, if page order is important, is adding a orderBy to child_pages.

[Jorge Godoy]

Specify the "joinColumn" as "id".

--
Jorge Godoy <jgo...@gmail.com>

[Robin Haswell]

I think this helps, it's sample working code for my Article model.
Articles have related articles:

class Article(SQLObject):
title = UnicodeCol()
body = UnicodeCol()
created = DateTimeCol(default=datetime.now)
sort = IntCol(default=0)

slug = StringCol(alternateID=True, default=None, length=255)
subject = ForeignKey("Subject")
user = ForeignKey("User")
related = RelatedJoin("Article", joinColumn="related_id_1",
otherColumn="related_id_2", orderBy="title")

def _set_title(self, value):
if not self.slug:
self.slug = Slug(value, self)
self._SO_set_title(value)
def _get_siteurl(self):
return "/information/%s/%s.html" % (self.subject.slug,
self.slug)
def __str__(self):
return self.title

I had to manually create the join table after `sql create` - no other
caveats.

-Rob

[Ed Singleton]

This worked for me.

It's a bit too much magic for my taste, because I don't have a
parent_page_id column and I couldn't find any documentation anywhere
that suggests that name would be created (I would have expected
parent_pageID or something).

Thanks for all the help from people.

Ed

[Jorge Godoy]

Take a look at what is generated with "ForeignKey". It creates a column named
"<the_name_you_gave>_id". Then, in the MultipleJoin, you were joining with
this column.

It's in the docs, even though it might not be obvious (but hey, you're the one
that made the join with the foreign key instead of the primary key...).

--
Jorge Godoy <jgo...@gmail.com>

[mulicheng]

I'm just curious.. I've wondered about this problem for a while. But
what is the best practice for this type of relationship when selecting
the child pages for display? I'm thinking of the case when a page has
children who also have children and so on. Is it common practice to
recursively query the database until all the children are selected? I
can't think of any sql oriented way to select all the pages otherwise.

Thanks
Dennis

[Matthew Bevan]

I let SQLObject do whatever it needs to give me child nodes in the easiest way
to loop possible. This means that SQLObject is doing a SELECT to get the
root node, then a SELECT to get all direct children. Then one SELECT per
child to get its children, and so on. This is fine for me, because I don't
descend the entire tree at any other time than site startup to build the
controller tree dynamically.

(This was the only way I could think of to reduce heavy SQL queries during
page views. Not having to look up every parent upon each request is nice.)

During startup of the site (start_extension) the entire content tree is
descended and controllers are instantiated and added dynamically. Once built
(which can take awhile for a large site) as all relevant data is already
in-memory, and individual requests are lightning fast. This is relying
heavily on SQLObject's singleton nature - a = Bob.get(1); b = Bob.get(1), a
is the same instance as b.

Of course, you can turn off this caching behavior and have .get() requests
done dynamically. Saves a LOT of memory, but is appropriately slower.

E.g. a site with two folders and two pages in each folder results in the
equivalent of the following:

class Root(RootController, CMSRoot):
def __init__(self):
self.firstfolder = CMSFolder(id=2, parent=self)
self.secondfolder = CMSFolder(id=3, parent=self)
self.firstfolder.pageA = CMSPage(id=4, parent=self.firstfolder)
self.firstfolder.pageB = CMSPage(id=5, parent=self.firstfolder)
self.secondfolder.pageA = CMSPage(id=6, parent=self.secondfolder)
self.secondfolder.pageB = CMSPage(id=7, parent=self.secondfolder)

My 2c.

[Julio Oña]

Hello,

You could have hierarchical data on a relational database and have a easy way to access it:

http://www.evolt.org/article/Four_ways_to_work_with_hierarchical_data/17/4047/
(I've lost the second link, it has even a book on this kind of structures in RDBMs)

Basically you have to put a column on your SO so it will represent the path of the tree until it.
To get all sons of a node you only have to know its path and select by that file with the path as beginnig
in sqlobject should be something like:

class MyTree(SQLObject):
    name=StringCol()
    path=StringCol() #this will be the place where the tree path should be.
    level=IntCol() #Just to make easier to get next level
    def _get_full_name(self):
        return "%s.%s" % (self.path, self.name)

Suppose we have node and need to look all their childrens:

MyTree.select(AND(MyTree.q.path.startswith (node.full_name+"."),MyTree.level=node.level+1))

Ready!!!
One query gets all, no funny recursive references.

When creating entries you shold do semthing like:
a=MyTree(name="a", path="a", level=0)
b=MyTree(name="b", path="%s.%s" % (a.full_name, "b"), level=a.level+1) #so b is under a
c=MyTree(name="c", path="%s.%s" % (a.full_name, "b"), level=a.level+1 ) #and it's c

This last code is a little ugly but functional.

Regards.

On 5/17/06, Matthew Bevan < matt....@topfloor.ca> wrote:

On Wednesday 17 May 2006 10:05, mulicheng wrote:
> I'm just curious.. I've wondered about this problem for a while.  But
> what is the best practice for this type of relationship when selecting
> the child pages for display?  I'm thinking of the case when a page has
> children who also have children and so on.  Is it common practice to
> recursively query the database until all the children are selected?  I
> can't think of any sql oriented way to select all the pages otherwise.

I let SQLObject do whatever it needs to give me child nodes in the easiest way

--
Julio

[Julio Oña]

sorry,

Last line should be:

c=MyTree(name="c", path="%s.%s" % (a.full_name, "c"), level=a.level+1 ) #and it's c

my fault to use c&p.

Regards.

--
Julio

[Jorge Vargas]

Jorge I'm doing that and it's not working for me I strip down my model to

class Region(SQLObject):
    name =  StringCol(alternateID=True)
    borders = MultipleJoin("Region",joinColumn='id')

also try with borders_id

tg-admin sql sql says:
CREATE TABLE region (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

and tg-admin sql create gives no errors at all.

any ideas?

--
Jorge Godoy      < jgo...@gmail.com>

[Ed Singleton]

Don't you need to use a RelatedJoin as it's a many to many relationship?

Ed

[Jorge Vargas]

On 6/24/06, Ed Singleton <singl...@gmail.com> wrote:

Don't you need to use a RelatedJoin as it's a many to many relationship?

ahhh of course... it was late sorry

anoything that should be said is that you DO need the otherColumn attribute since SO will ignore it thinking it's a dup if you do

I add this to the wiki please enhance it.
http://trac.turbogears.org/turbogears/wiki/SQLObjectTreeLike