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

Search only this category: pre-mod

Quote Reply
Search only this category: pre-mod
Hi,

I've been trying to work on the 'Search only this category' mod as I think it will benefit many of us and I wondered if I could get some help on bits of it as my knowledge of perl and sql stuff is quite limited. Here's what I've done so far:

< snip > - See later post for modified version (removed code here to keep the thread shorter)

All the best
Shaun

Quote Reply
Re: Search only this category: pre-mod In reply to
I don't know why you are attempting to use two parameters:

search_cat and search_type

I would recommend using only the search_cat and set up your form like the following:

Code:

<select name="search_cat">
<option value="all">All Categories</option>
<option value="<%category_id%>">This Category</option>


OR as radio buttons:

Code:

<input type="radio" name="search_cat" value="all">All Categories
<input type="radio" name="search_cat" value="<%category_id%>">This category


I don't see the point of passing search_type and search_cat.

Regards,

Eliot Lee

Quote Reply
Re: Search only this category: pre-mod In reply to
Good point Eliot, thanks for the input Smile - any ideas on the search routine in DBSQL.pm anyone?

All the best
Shaun

Quote Reply
Re: Search only this category: pre-mod In reply to
Nope...but may be, Alexander (Alex404) will have some ideas since he has done some extensive code hacking of DBSQL.pm and Search.pm although the code hack he released for the boolean search does nothing in my site.

Regards,

Eliot Lee

Quote Reply
Re: Search only this category: pre-mod In reply to
< snip > - See later thread

All the best
Shaun

Quote Reply
Re: Search only this category: pre-mod In reply to
Looks like it...but I have not played around too much with DBSQL and Search modules.

Regards,

Eliot Lee

Quote Reply
Re: Search only this category: pre-mod In reply to
Ok, yet a little further on (although I'm into the realms of the unknown here - maybe someone could unravel what I've done to make better sense of it Smile):

else {
# Get category name
my $id = $sc;
my ($name, $rec);

$name = $CATEGORY_NAME{$id};
if (! $name) {
if (! $CATDB) {
$CATDB = new Links::DBSQL $LINKS{admin_root_path} . "/defs/Category.def";
}
$rec = $CATDB->get_record ($id, 'HASH');
$name = $rec->{'Name'};
$CATEGORY_NAME{$id} = $name;
}

# Get a list of ID's that have the same starting catergory name

$table = new Links::DBSQL $LINKS{admin_root_path} . "/defs/Category.def";

$cat_query = qq!
SELECT *
FROM $table
WHERE Name LIKE $name
$sb
LIMIT $offset, $maxhits
!;
$sth = $DBH->prepare($cat_query) or return $self->error ('CANTPREPARE', $cat_query, $DBI::errstr);
$sth->execute() or return $self->error ('CANTEXECUTE', $cat_query, $DBI::errstr);
$self->{'last_query'} = $cat_query;

# Update WHERE to match against list of category ID's?

$where .= qq! CategoryID LIKE $cat_query!

{
$query = qq!
SELECT *
FROM $table
$where
$sb
LIMIT $offset, $maxhits
!;
$sth = $DBH->prepare($query) or return $self->error ('CANTPREPARE', $query, $DBI::errstr);
$sth->execute() or return $self->error ('CANTEXECUTE', $query, $DBI::errstr);
$self->{'last_query'} = $query;
}

Quote Reply
Re: Search only this category: pre-mod In reply to
I didn't read through all this, as I'm not quite myself still.

But, what about a 'trick' I suggested awhile back.

Do a select from the category table where the Category.Name begins with the category string you want to search for (that will yank out the Category.ID of all the categories).

Then, search for links that meet the criteria, but restricted by:

'with CategoryID IN (list_of_returned_values)'

This is similar to how the list of available categories to edit is selected for the Editors.

I don't know what sort of additional changes this will require, but the biggest might be storing the CategoryID with the Link_Word_Scores (or whatever) to prevent having to do a secondary lookup. That's simply a minor change to the Indexing routine.

Wouldn't that do the same thing? Then all you need is a swtich to turn that on or off from the search template?



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

Quote Reply
Re: Search only this category: pre-mod In reply to
Hi pugdog,

Here's the shorter quick-view of it;

1) Add a variable to search form $sc which is either 'all' or 'Cat ID'.
2) When searching in DBSQL.pm, use Cat ID to get the Cat Name.
3) Use Cat Name to find all sub-cats, i.e.; Business/Companies/Plumbing*
4) Put all the sub-cat ID's together.
5) When searching for cat or link matches, drop any that don't have a Cat ID from the list we've just made.

So far, with some help, I've managed to get to step 3 (at least I think its OK up to there) but need help with the rest as I don't really understand the sql queries for the search criteria.

I know its a lot to look through, so I'll wait until people have the time to read it and see what comes.

All the best
Shaun

Quote Reply
Re: Search only this category: pre-mod In reply to
That's the point of what I'm saying.

If you find a list of categories that the link is allowed to be in, all you need to do is run the search, but add the qualifier:

"AND CategoryID IN (list_of_allowed_cats)"

You shouldn't have to make all sorts of reciprocal changes, and edit a lot of things.

When you limit by sub category, you just want to pass in the above string to the query that runs the search, or if the query is testing link-by-link validity, you just want to add the above check.

I haven't looked at the search routines, and how they do it, but finding the subcategories in a tree is possible relatively easily, and with the "IN (list)" operator on the WHERE clause, you can quickly figure out which links past the test.

Before going forward, I'd take a step back, and look at what you are doing, and see if you can't change the logic a bit to allow the routines that are already there to do the work.

Generate the "list" initially, and then because it's a quick test, test for membership in that set before analyzing the link (on full-text type searches) or on the first iteration of any indexed search.

It's not immediately obvious, but you don't have to do a lot of work if you do it this way.

When you select a link -- "Is it a member of the subcategory_list if not, select the next link"

If you select a list of links based on some other criteria, just check for membership in this set _BEFORE_ inserting them into any list.

You can create a perl list from the found values of Category.ID, and the check for existence in that list from a perl statement, or using an "IN (list)" construct within MySQL.

Make sense?

The more I think about it, the more simple it seems to become, with the only 'penalty' being the hit or two to check if "subcategory_restriction" is on.

Optimization would depend on how the search routine locates 'matching' links. I haven't looked at that in a long while.

Code:
# Search the category listings.
$catdb = new Links::DBSQL "$LINKS{admin_root_path}/defs/Category.def";
$cat_hits = $catdb->query ( { query => $query, mh => $mh, nh => $nh } );
$cat_count = $catdb->hits || 0;
$cat_errors = $catdb->query_errors;

# Now let's search the links table, but first figure out any filters.
$linkdb = new Links::DBSQL "$LINKS{admin_root_path}/defs/Links.def";
my %filter = ();
foreach my $col (@{$linkdb->{db_cols}}) {
if ($in->param($col)) {
$filter{$col} = $in->param($col);
}
}
$link_hits = $linkdb->query ( { query => $query, mh => $mh, nh => $nh, filter => \%filter, ww => $ww } );
$link_count = $linkdb->hits || 0;
$link_errors = $linkdb->query_errors;
This is where you do the first scan for categories. You'd want to find the allowed subcategories _before_ this point, and create your list of allowed subcategories.

In the 'query' routine, you want to have a switch so that if the $subcategory_restrict variable is passed, and contains a list, you pass a formated "AND CategoryID IN $subcategory_restrict" to the query statement.

The categories returned would then be only those in the subcategory tree.
And the links returned would be only those in that tree as well.

Now, if that logic doesn't work, and the 'query' routine is too complex to fiddle with, take the values returned, and iterate through them, making sure that the value in the "Category.ID" or "CategoryID" field is a member of that list. When you yank the value out of the returned values, just 'next' if the category or link fails the test before adding it to the output or returned list, and before incrementing any counters (or DECREMENT the '$Hits' value for each link skipped).

making sense?




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

Quote Reply
Re: Search only this category: pre-mod In reply to
Hi Qango,

I think you are trying to solve this problem the hard way.

Think about using already built-in functions!
Here's the logical aproach I use on my site:

1. In search.htm pass a parameter Catid=12 (just an example)
2. In search.cgi query the db for the categoryname of Catid 12
3. In search.cgi query the db for Category Names beginning with categoryname
( SELECT ID FROM Category WHERE Name LIKE 'categoryname%'
4.In search.cgi retrieve a comma seperated list of CategoryID's and add the searched cat to it. Save it in the string $sub_list.

5.In search.cgi set $in->param ( -name => 'CategoryID' , -value => $sub_list );
be shure to insert it before :
# Now let's search the links table, but first figure out any filters.

6. call the query as normal.

7. Hack Search.pm

7a find the: sub query{
(Alex fixed this bug in a thread some days ago)
find the lines :

my %needed = map { $$_[0] => 1 } @query_results;
foreach $row ( $sth->fetchrow_hashref () ) {
delete $needed { $$row { $id_col } };
}

change them to:

my %needed = map { $$_[0] => 1 } @query_results;
while ( $row = $sth->fetchrow_hashref () ) {
delete $needed { $$row { $id_col } };
}

7b. modify the filter-build routines to accept a list of CategoryID's instead of only one.
In Search.pm sub query:

find:

# ... setup the filter if it was asked for
my $filterref = $self->{filter};
my $filter = undef;
my $where;

if ( $filterref ) {
$filter = (" and (( " .
join ( ") and ( ", map { "$_ = \"". quotemeta($$filterref{$_}) ."\"" } keys %$filterref )
. "))" );

$where = "$id_col in (" .
join ( ",", map ( $$_[0], @query_results) ) .
") $filter";

change it to:

# ... setup the filter if it was asked for
my $filterref = $self->{filter};
my $filter = undef;
my $where ;


if ( ($filterref) and (@query_results)) {
$filter = " and (";
foreach my $flt(keys %$filterref){
if ($flt ne 'CategoryID'){
$filter.= "(" . $flt . " like '%" . $filterref->{$flt} . "%') and ";
} else {
$filter.= "(" .$flt . " in (" . $filterref->{$flt} . ")) and ";
}
}
chop($filter);chop($filter);chop($filter);chop($filter);
$filter .= ")";

thats it. Happy hacking Smile

regards, alexander

Quote Reply
Re: Search only this category: pre-mod In reply to
Hi Eliot,

In Reply To:
although the code hack he released for the boolean search does nothing in my site.
Please send me your files (Search.cgi, DBSQL.pm, Search.pm) by email (Silberling@t-online.de) I will fix them for you.

regards, alexander

Quote Reply
Re: Search only this category: pre-mod In reply to
Pugdog & Alex,

Thanks for your help so far, you've given me some good advice and after reading what you'd said I've now revamped it some more. Hopefully its more in-line with what you've suggested and so far its going well, but I've still got a problem where the search results are not restircted to the category IDs in the list.

Here's the latest version:

search.cgi

# Set catid
$catid = $in->param('catid'); # Blank if not selected from the category page

# Check catgeory ID's for 'only this cat type search'

if ($catid eq ""){$cat_name=""} else {

# Get Category Name

$cat_name = &get_category_name ($catid); # now uses routine already in DB_Utils.pm

# Get a list of category IDs where the name starts with $cat_name

$sub_list = &get_category_id_list ($cat_name); # uses new routine in DB_Utils.pm to get list of IDs
$in->param ( name => 'CategoryID' , value => $sub_list );
}

DB_Utils.pm
New sub to get comma separated list of matching category IDs (hacked from sub get_category_list):

sub get_category_id_list {
# --------------------------------------------------------
# Get a list of category IDs based on match with start of category name.
#
my $value = shift;
my ($query, $sth, $id, $name, $output, $time);

$output = $CATEGORY_LIST{$value};
$time = time();

if (!$output) {
if (! $CATDB) {
$CATDB = new Links::DBSQL $LINKS{admin_root_path} . "/defs/Category.def";
}
$query = qq!
SELECT ID, Name
FROM Category
WHERE Name LIKE '$value%'
ORDER BY Name
!;
$sth = $CATDB->prepare ($query);
$sth->execute() or die "Can't Execute: $DBI::errstr";
while (($id) = $sth->fetchrow_array) {
($output .= "$id, ");
}
$sth->finish;
$CATEGORY_LIST{$value} = $output;
}
return $output;
}

I've also made the changes Alex suggested to Search.pm to add the CategoryID filter.

I've printed the values for $cat_name and $sub_list to the search results screen and they display OK, but the search routine still doesn't restrict found cats/links to those in the category IDs list.

Any help you could offer on the last part would be appreciated.

Thanks again, you guys Smile

All the best
Shaun

Quote Reply
Re: Search only this category: pre-mod In reply to
OK, I've now changed this (search.cgi):

$in->param ( name => 'CategoryID' , value => $sub_list );

... to this ...

$in->param ( 'CategoryID' => $sub_list );

... and it now appears to pass the category ID list to Search.pm, but unfortunately the filter doesn't seem to work and the same links are returned.

All the best
Shaun
Quote Reply
Re: Search only this category: pre-mod In reply to
Hi,

1. There is an error in your CategoryID list. It has a comma as last char.
use chop($sub_list); (in Search.cgi) to cut it off.

2. Im not shure but there could be a closing bracket to much in the sql statement.

regards, alexander

Quote Reply
Re: Search only this category: pre-mod In reply to
Ähm..no,

the brackets are ok! its the last comma!

Quote Reply
Re: Search only this category: pre-mod In reply to
Alex,

OK, I've added the 'chop' and that has sorted out the hanging comma at the end ... but the results are still the same, all the matching links are displayed regardless of the cat_id_list filter.

All the best
Shaun
Quote Reply
Re: Search only this category: pre-mod In reply to
Ok,

Let's debug.

1. check #7a of my prevoius post. This is very important for filters to work.

2. Find in Search.pm in the sub query{

my $query="SELECT count(*) FROM " . $self->{target_table} . " WHERE $where";

insert after this line:

print $query;

do a search. have a look at the unformatted output and post it here. I'll have a look at it.

regards, alexander

Quote Reply
Re: Search only this category: pre-mod In reply to
Alex,

Here's the search I did:

http://www.qango.com/cgi-local/or/search.cgi?query=net&catid=14

I double checked 7a as per your previous post and it is correct. The Search.pm query output is as follows:

Code:
count(*) FROM Links WHERE ID in
(296,598,143,502,500,552,623,153,418,432,8,313,100,388,506,225,481,391,474,166,347,394,437,172,301,
510,489,209,26,107,293,295,214,387,357,286,223,99,297,1,601,126,605,248,284,168,289,3,134,333,253,
290,292,419,456,497,71,105,501,421,422,261,425,264,427,428,429,586,116,350,471,517,398,600,519,
306,103,20,365,135,661,170,527,614,378,328,212,604,46,280,323,512,257,68,204,203,95,29,237,572,10,
277,61,438,568,276,9)
and ((CategoryID in
(14,458,459,460,461,462,463,464,465,1602,1603,1604,466,467,468,2470,2471,469,1595,1596,1597,470,
471,472,473,474,1195,1196,1554,1555,1197,1198,1199,1200,1201,475,476,477,478,479,480,481,483,484,
485,22,1556,1557,1558,2485,2484,1551,1560,1561,1562,1563,1564,2128,1565,1566,1567,1568,2446,1569,
1570,1571,1572,1573,1574,1575,486,2129,2124,1598,2130,487,2131,1599,2472,2473,2474,2475,2476,
2477,2478,2479,2480,2481,488,489,1553,1306,490,2486,2487,1740,491,1741,2363,2362,1576,1577,2448,
2447,2449,2450,3137,3138,3139,3140,3141,3142,3143,3144,3145,3146,3147,3148,3149,3150,3151,2451,
2452,2453,2454,2456,1582,1583,1584,1585,1586,1587,25,1552,1588,1589,1590,499,1591,1592,1594,1593,
2455,3152,3153,3154,3155,3156,3157,3158,3159,3160,3161,3162,3163,3164,3165,3166,3167,3168,3169,
3170,492,493,494,495,496,497,498,1578,2361,1579,1580,1581))
)
NOTE: I've manually wrapped the lines to keep the width of the post nominal.

Hope this helps :)

All the best
Shaun
Quote Reply
Re: Search only this category: pre-mod In reply to
Hi,

The links search seems to work. As far as I see from the output on your site only Links of the category 14 (Internet) are shown. The problem is the category results.

To change the category results, you must also change the query for the cats. In search.cgi find the line:

....
deleted ( because not working)
....

regards, alexander

Quote Reply
Re: Search only this category: pre-mod In reply to
Alex,

That works great, but it reports '0' (zero) categories found each time and doesn't display a list of cats, only found links.

All the best
Shaun

Quote Reply
Re: Search only this category: pre-mod In reply to
Hi Quango,

are you shure to blank-out the category results? It would be a nice option to have a list of subcategorys corresponding to the query. Especially if you have big category trees. In your case (I am in the category internet and search for "webhosting" ) wouldn't it be great to get the categorys:
internet->companys->ISP->webhosting
internet->software->webhosting tools

aswell as the appropriate links?

regards,alexander

Quote Reply
Re: Search only this category: pre-mod In reply to
Oops..

It can't work that way because the make filter in search.pm only accepts a list for CategoryID... sorry I wil edit this post.. and think of a better solution...

regards, alexander

Quote Reply
Re: Search only this category: pre-mod In reply to
Thanks Alex - you're a star Smile

Once we get the categories working as well it'll be complete, so I'll set about posting the complete mod in the forum for other users as I'm sure it'll be much appreciated.

All the best
Shaun

Quote Reply
Re: Search only this category: pre-mod In reply to
Ok..

in search.pm change the modification #7b some messages upward
from:
if ($flt ne 'CategoryID'){
to:
if ($filterref->{$flt} !~ m/,/){

now you can send any list to build a filterSmile

regards, alexander

Quote Reply
Re: Search only this category: pre-mod In reply to
Alex,

Fantastic - that did it Smile thanks a lot, your help is much appreciated. I'll write it all down as a mod now and post it in a new thread.

All the best
Shaun



Quote Reply
Re: Search only this category: pre-mod In reply to
The full mod with instructions is now posted in its own thread in the forum:

http://www.gossamer-threads.com/...w=collapsed&sb=5

All the best
Shaun