sqlobject-discuss

[SQLObject] search RelatedJoin

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

Hi,

I solved this: http://sourceforge.net/mailarchive/message.php?msg_id=9996700
 (don't have it in mijn mailbox for some reason) by explicitly
declaring a class for the third (many-to-many) table and adding
"cascade" attribute to the columns of this table.

Now another question: how to search RelatedJoin? I have a table
Address with many-to-many relation to it self. Address kan have
multiple addresses as parents, and can be a parent on it's own. I'd
like to find all children of given parent. How should I build this
query with SQLObject?


Thanks!

-- 
Ksenia

Re: [SQLObject] search RelatedJoin

[Michel T. <mic...@ya...>]

Hi!

I faced the same problem, a parent has many children, I don't know well
you case, my case isn't many-to-many, it is one to many, one parent to
many children, each child can be a parent but cannot have more then one
parent.

My application is a forum, I ned a MultipleJoin and a ForeignKey o the
same table but that time I couldn't have. I was creating the table
throught SQLObject and I can't create a foreign key to a not existent
table (the same table that doesn't exists yet).

Maybe you can create the table manually, I don't try this way, I think
this possibility just now :), you can create a ForeignKey yourself, I
done this way:

class Forum:
  parentID=IntCol(default=None)
  def _get_parent(self):
    return self.parentID and Forum.get(self.parentID) or None
  def _get_childs(self):
    return list(Forum.select(Forum.q.parentID==self.id))
  def _create(self, id, **kw):
    kw['parentID']=kw.pop('parent').id

This way you can work like that: Forum(parent=x)...
Sorry my poor english, I hope I could help you...



 --- Ksenia Marasanova <kse...@gm...> escreveu: 
> Hi,
> 
> I solved this:
> http://sourceforge.net/mailarchive/message.php?msg_id=9996700
>  (don't have it in mijn mailbox for some reason) by explicitly
> declaring a class for the third (many-to-many) table and adding
> "cascade" attribute to the columns of this table.
> 
> Now another question: how to search RelatedJoin? I have a table
> Address with many-to-many relation to it self. Address kan have
> multiple addresses as parents, and can be a parent on it's own. I'd
> like to find all children of given parent. How should I build this
> query with SQLObject?
> 
> 
> Thanks!
> 
> -- 
> Ksenia
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by:
> Sybase ASE Linux Express Edition - download now for FREE
> LinuxWorld Reader's Choice Award Winner for best database on Linux.
> http://ads.osdn.com/?ad_id=5588&alloc_id=12065&op=click
> _______________________________________________
> sqlobject-discuss mailing list
> sql...@li...
> https://lists.sourceforge.net/lists/listinfo/sqlobject-discuss
>  

=====
--
Michel Thadeu Sabchuk
Curitiba/PR


	
	
		
_______________________________________________________ 
Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/

Re: [SQLObject] search RelatedJoin

[Ian B. <ia...@co...>]

Ksenia Marasanova wrote:
> I solved this: http://sourceforge.net/mailarchive/message.php?msg_id=9996700
>  (don't have it in mijn mailbox for some reason) by explicitly
> declaring a class for the third (many-to-many) table and adding
> "cascade" attribute to the columns of this table.
> 
> Now another question: how to search RelatedJoin? I have a table
> Address with many-to-many relation to it self. Address kan have
> multiple addresses as parents, and can be a parent on it's own. I'd
> like to find all children of given parent. How should I build this
> query with SQLObject?

Blech; that's a real pain in SQL in general.  There's whole books on it 
(literally).  Hmm... I know I've seen several articles on it, but I'm 
missing them at the moment.

Anyway, you have to use different tree representations if you want to do 
it with a single query.  Maybe someone else has an article on this handy.

Sometimes I've kept a table that contains all the ancestor 
relationships, maybe with a degree-of-separation column as well.  You 
have to do a fair amount of bookkeeping to make it work (not at all 
normal), but it's relatively fast and easy to understand.  There's some 
better algorithms that use different kinds of numbering.  Using strings 
is also easy to understand, along the lines of a path.  So '/a/b/c' 
means c is a child of '/a/b', and b is a child of '/a'.  Lots of 
bookkeeping there as well.

-- 
Ian Bicking  /  ia...@co...  / http://blog.ianbicking.org

Re: [SQLObject] search RelatedJoin

[Luke O. <lu...@me...>]

Quoting Ksenia Marasanova <kse...@gm...>:
>
> Now another question: how to search RelatedJoin? I have a table
> Address with many-to-many relation to it self. Address kan have
> multiple addresses as parents, and can be a parent on it's own. I'd
> like to find all children of given parent. How should I build this
> query with SQLObject?
>

As Ian says, if you're looking for _all_ the ancestors of a recursive 
structure
like this, or an arbitrary distance in the tree, it's messy and there is
probably bookkeeping info that will make it more manageable.

If you just need direct children, RelatedJoin should do it for you, just have
one for the parents and one for the children.

Address --> [childID|parentID] <-- Address

class Address(SQLObject):

      # col defs
      #...

      children = RelatedJoin('Address', joinColumn='parentID',
otherColumn='childID')
      parents = RelatedJoin('Address', joinColumn ='childID',
otherColumn='childID')


Your question brings another one to mind, about additional criteria on the
select used for joins (both RelatedJoin and MultipleJoin). Using your
relationships:

All children addresses with zipcode '98661'
All parent addresses in the same city as the current record.

I know I've mentioned this before, so this is more a reminder to myself - I
keep finding reasons for this, but it's easy enough to work around, just a
duplication of knowledge about what the joinColumns names are.

It needs to take at least a query argument, and should probably take most of
the select() arguments. Either an additional public method
(joinMethodNameSelect perhaps) or an internal method of the join (which would
need a better way to retrieve join objects -
self._joinNames['joinMethodName'].select perhaps).

If there's interest, I can take this on, since I've been using this idea so
many times lately (on RelatedJoins) but working my way around by manually
rebuilding the select statement. This way I could define:

    def childrenWithZipcode(self, zip):
       return self.childrenSelect(Address.q.zipcode == zip)

    def _get_parentsInSameCity(self):
       return self.parentsSelect(Address.q.city == self.city)

RelatedJoins, and your self-referential example in particular, show the
difficulty in this. If it were a MultipleJoin, say People to many addresses,
the query would default to the query against the foreign table, no need to
specify 'address.city', just 'city'. But in these examples, you'd either have
to specify that clauseTables always apply to the joining side, or give a way
to distinguish between the two sides of the join. And the selects for joins
might actually have to be joins. :)

Hmm. Easy enough to implement for MultipleJoins, messier syntax to decide on
for RelatedJoins but still looks possible to me. Ian, any further plans
for/against making Joins into fuller SelectResults-like objects?

- Luke

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)

Re: [SQLObject] search RelatedJoin

[Ian B. <ia...@co...>]

Luke Opperman wrote:
> Your question brings another one to mind, about additional criteria on the
> select used for joins (both RelatedJoin and MultipleJoin). Using your
> relationships:
> 
> All children addresses with zipcode '98661'
> All parent addresses in the same city as the current record.
> 
> I know I've mentioned this before, so this is more a reminder to myself - I
> keep finding reasons for this, but it's easy enough to work around, just a
> duplication of knowledge about what the joinColumns names are.
> 
> It needs to take at least a query argument, and should probably take 
> most of
> the select() arguments. Either an additional public method
> (joinMethodNameSelect perhaps) or an internal method of the join (which 
> would
> need a better way to retrieve join objects -
> self._joinNames['joinMethodName'].select perhaps).

Yes, that seems reasonable.  Off the top of my head, I'm guessing it 
could almost be like:

def childrenSelect(self, query, **kw):
     query = query & (self.id == ChildTable.q.thisID)
     return ChildTable.select(query, **kw)

Obviously generalized some, so that it allows for string queries and all 
the other join options.  Joins need to be cleaned up some too, so a 
general refactoring might be good.

Another option for joins was to make them live lists, so that appending 
or removing changed the underlying database.  I'm thinking that might be 
unnecessarily clever, though, and building it on a select would be better.

-- 
Ian Bicking  /  ia...@co...  /  http://blog.ianbicking.org

Re: [SQLObject] search RelatedJoin

[Oleg B. <ph...@ma...>]

Hi!

On Wed, Nov 10, 2004 at 01:50:55AM +0200, Ksenia Marasanova wrote:
> Now another question: how to search RelatedJoin? I have a table
> Address with many-to-many relation to it self. Address kan have
> multiple addresses as parents, and can be a parent on it's own. I'd
> like to find all children of given parent. How should I build this
> query with SQLObject?

   The web is full of articles on hierarchical SQL. See, e.g.:

http://www.onlamp.com/lpt/a/5007
http://www.oreillynet.com/lpt/a/2958

Oleg.
-- 
     Oleg Broytmann            http://phd.pp.ru/            ph...@ph...
           Programmers don't die, they just GOSUB without RETURN.