Re: [SQLObject] search RelatedJoin

[Ksenia M. <kse...@gm...>]

Thanks a lot to all of you for the help,

That's a lot of information to study for novice SQLObject user :) I am
not sure I follow everything Luke explained about the joins , but it
will come...
I apologize, my question was very unclear. Yes, I only need direct
children (no need for hierarchical SQL lookups at the moment). But I
also want to search children, like the examples Luke has given.

I was reading the documentation about select() method and wanted to
use it for searching. Like in  this example:
peeps = Person.select(
            AND(Address.q.personID == Person.q.id,
                Address.q.zip.startswith('504')))

...but for RelatedJoin.

But I see now that with custom methods and by overriding SQLObject
methods there are more possibilities....

Ksenia.

P.S. Here is the current code, just in case:

class AddressParentChild(SQLObject):
    child = ForeignKey('Address', cascade=True)
    parent = ForeignKey('Address', cascade=True)

class Address(SQLObject):
    name = StringCol(length=200)
    email = StringCol(length=100)
    type = StringCol(length=100, default='person')
    parents = RelatedJoin("Address",
                          addRemoveName="Parent",
                          intermediateTable="address_parent_child",
                          otherColumn = "parent_id",
                          joinColumn="child_id")
    children = RelatedJoin("Address",
                          addRemoveName="Child",
                          intermediateTable="address_parent_child",
                          otherColumn= "child_id",
                          joinColumn="parent_id")

and the hard coded SQL I use now that should be changed (in this
example to find children with name X or children whoes parent has name
X)

            rows = Address.select('''
            (address.id IN (SELECT child_id FROM address_parent_child 
                INNER JOIN address ON address_parent_child.parent_id =
address.id WHERE address.name ILIKE %s))
            OR (address.name ILIKE %s)
            ''' % (Address.sqlrepr('%' + selected_parent + '%'), 
            Address.sqlrepr('%' + selected_parent + '%')), 
            orderBy=Address.q.name)