Gossamer Forum
Home : Products : Gossamer Links : Version 1.x :

search in cat and subcats only , the 4th.

Quote Reply
search in cat and subcats only , the 4th.
Again im hanging about this problem never solved before:
at the moment i see two ways to do:

First as mentioned in another thread; searching all but restrict the passed values to them from actuall cat, and all subcats, while doing an extra loop for the ids in CategoryAlternates.

The second solution, but more tricky, is to add another WHERE-Clause to sub query in dbsql.
Something like:

if ($in->('Category') then:

later ...

WHERE $where AND $where2

Now for this second $where2

In words we need:

Select all the links [and the links from CategoryAlternates where CategoryID=from actual or subs)
from Links WHERE $where AND WHERE CategoryID = actual or subcat.

Im trying this with a) the parameter IN ('a',...,'z')
or with the parameter EXITS and some other SELECTs inside.

I would be very glad if Alex could do some work on this, cause he will solve it shurely in minutes, while i will sit here for the next days. And cause so many users would like to have this mod; there is a real must for this.

Maybe some other people would like to collect some money to let this be done; i really dont have the time at the moment to spend some days on this; but as i have see Alex has nothing done on it in the last month though it is one of the most wished mod.

Some ideas to do it better or translate my thoughts into perl?

I will search and download now the old links 2.0 mod, maybe i will find there some ideas to it better.

Robert


Quote Reply
Re: search in cat and subcats only , the 4th. In reply to
The way the category hierarchy is built, you can't do that with a single select statement.

If you look in nph-build.cgi the category tree is traversed level by level. You'd have to do that with any search.

If you are dead set on this, the easiest way would be to add a field to the Link record, that contains the FULL category name.

Use an UPDATE/SET to set it equal to the name of the CategoryID.

Then, in any category search you could create a sub_cat string:

$subcat_string = "\". $query . '%'. "\";

then pass it to the select or query as part of the where clause:

Where Full_Name LIKE "$subcat_string"


Simple, ugly, effective, violating a dozen rules of database normalizing.
Can be done in about 10-15 lines of code and some template stuff. You can always delete that column later.



http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/

Quote Reply
Re: search in cat and subcats only , the 4th. In reply to
Thank you Pugdog, why should i have the cat_name twice?
I think my solution is practicable, but i need more help from men knowing perl better than mine.

Robert

Quote Reply
Re: search in cat and subcats only , the 4th. In reply to
What you are trying to do with two where clauses is not valid SQL, and MySQL doesn't handle sub-selects.

In what I proposed to you, you don't have the category name twice. You have an ID in the Link record which the program uses normally, _AND_ I'm saying ADD the category Name to the link record that only the search.cgi will care about, and you can do sub-category selects by passing in the first part of the category name.

To try to do the "join" on ID numbers will create a lot of problems. You need to do a select to get a list of categories that fit the criteria (check out the maintain.cgi) then you have to do a select 'where CategoryID IN ($list)' to find all the links.

There are probably some other technical problems, and overhead problems in doing it that way, when just adding the category name to the links once, and maybe re-freshing it on each build -- once -- would allow you to do simple "selects" on the database. Since this is hopefully fixed (a method exists to do it) in the next version, trying to make the more elegant solution work is a waste of time in the long run. But if you feel you want to do it, go ahead.

http://www.postcards.com
FAQ: http://www.postcards.com/FAQ/LinkSQL/