Re: ObjectLinkRef table

[Jason Pincin <chardros@linuxkb.org>]

> Q1:  ObjectID is the "parent" and LinkObjectID is the child?  Ie:

Yes.

> Q2:  What is a good algorithm\SQL for finding specific kinds of links in
> this table?  Ie, how do I find all the links which link a category under
> another category?  Or link an article under a secondary
> category/application?  Right now it seems that I have to do a select for
> each ObjectID of the type I'm looking for.  So if there are 1000 articles
> of which say 50 have 2ndary categories, I have to do 1000 queries, one for
> each article.  This sounds very non-optimal performance wise, but I
> understand that this is part of normalizing a database (don't store the
> same info twice).

No.  While you cannot retreive the object type from the ObjectLinkRef table (It's basicly like a sym
link... the link itself has no attributes), you can get the ObjectID (obviously).  So... if your looking
for ALL... Articles that are in the LinkRef table as "Linkees", you'd do this:

select LinkObjectID, ObjectType from ObjectLinkRef,KBObjects where LinkObjectID = KBObjects.ObjectID AND
ObjectType
= 6;

That would give you all articles from the LinkRef table that are linked UNDER "something".  If you wanted
to look for Articles that are linked under a "certain something" only you COULD do:

 select ObjectLinkRef.ObjectID, LinkObjectID, ObjectType from ObjectLinkRef,KBObjects where (LinkObjectID
= KBObjects.ObjectID OR ObjectLinkRef.ObjectID = KBObjects.ObjectID) AND (ObjectType = 6 OR ObjectType =
8);

And only pluck the ones out of the list returned where your ID has BOTH ObjectTypes of the child your
looking for and the parent you want.  It's quite ugly... anyone come up with a cleaner query?  It's pretty
early for me... I just woke up so... BUT that WILL work... and both queries will get what your looking for
in ONE query... not 100.  And thats a GOOD THING (tm).  Lemme know what you think.

-- 
Jason Pincin
Linux Knowledge Base Project Leader (http://www.linuxkb.org)
http://vodka.linuxkb.org/~chardros