Join Examples

Self Joins to achieve a parent/child relationship

I just found the following message on google, and it was exactly what I needed. Thanks David! Here's a copy of the message.. -SamNilsson

Email Archive: sqlobject-discuss (read-only) Search

From: David McNab <david@re...> Table Joining to Itself 2004-03-24 06:20

Hi,

I"m posting here in the hope that my problem, and its solution, might save others from the battle I"ve had.

Basically, I"m implementing a web shopping cart. While I was tempted to have just a plain 2 or 3 level product hierarchy, this felt too restrictive and I wanted a more general solution - the ability to have an unrestricted n-level hierarchy of product categories.

After some experimenting and reading SQLObject source, the following solution came out:

class prodcats(SQLObject):
    _connection = _conn
    name=StringCol()
    longname=StringCol()
    parent=ForeignKey("prodcats", default=None)
    children=MultipleJoin("prodcats", joinColumn="parent_id")

prodcats.createTable()

rPets = prodcats.new(
  name="pets", longname="Pets")

rBirds = prodcats.new(
  name="birds", longname="Birds", parent=rPets)

rLargeBirds = prodcats.new(
  name="large", longname="Large Birds", parent=rBirds)

rSmallBirds = prodcats.new(
  name="small", longname="Small Birds", parent=rBirds)

rDogs = prodcats.new(
  name="dogs", longname="Dogs", parent=rPets)

rLargeDogs = prodcats.new(
  name="large", longname="Large Dogs", parent=rDogs)

rSmallDogs = prodcats.new(
  name="small", longname="Small Dogs", parent=rDogs)

--

Kind regards David

--