custom search questions

Okey dokey, moving along, here is where I am at now,

I am going to keep this a running commentary as I develop this thingy, hopefully it will help others and for me it will be keep my thoughts organized as the day goes by.

First, to recap my project requirements.

1. My client needs a search of only a specific custom node type.
2. The search of the custom node type will return a sortable/pageable table
3. The search page for this node type must be its own page.

My first instinct is to leverage the existing API's to accomplish this, but after studying the search.module code (thank you open source!) I have come to the following (possibly incorrect) conclusions:

Requirement 3 somewhat rules out implementing ld_search (where 'ld' is the name of my custom module; ld.module) Implementing this places a tab on the default user search screen. I managed to get that tab to not appear by ommitting the case 'name' in the switch statement.

Requirement 1. kicked my butt yesterday and was the subject of the previous post in this thread..for the life of me, I cannot get search.modules do_search to consistently return my module types even when I make live hacks to the search_index.word field. there were several problems:

when I changed an indexed word from "leader" to "dude" and left the type as 'node' in both the si and sd tables, the engine would return a result for that node.

when I changed the type from 'node' to 'ld' in both tables, I got no results...

when I changed back to 'node' from 'ld' on both tables, I got no results...(bear in mind, I was a bit batty by the end of the day, so my thinking could have been loopy)

I spent too much time trying to identify the source of the above problem and I have to move on.

since I need custom output from the search that will be displayed in a sortable table with headers I believe that the

During the above contortions, it dawned on me that even if I got the correct node types consistently returned in searches, I would not be able to present the data in the format they required (hook_search_item seems to only allow customizing the output on a per-item basis, not to direct the output of the entire search result set--again, I could be wrong about this.)

So, I decided to move and implement my own custom callbacks, utilizing the parts of the api that I can leverage.

The following posts will be a running commentary on my efforts.

t.

Ok, I left off yesterday with a good result, my custom callback was called by the form
and I managed to get it to print out the values of the $keys argument.

Here is my code in ld_do_search:

		print 'DO a custom leader directory search here.<br>';
		print 'remember, output must be against that tablular ouptut thing.<br>';
		print 'your keys are here: <br>';
		print $_POST['edit']['keys'] .'<br>';

and the output was...

DO a custom leadership directory search here.
remember, output must be against that tablular ouptut thing.
your keys are here:
these should be my keys

ok, that is good news, I have a custom form, a redirect to a place where I can control my own search and output and I have access to the search terms the user entered.

progress.

Now, I still want to leverage the search.module where possible.

From my study of the node.module and search.module, the search.modules do_search() function is the guts of the thing.

I now want to understand exactly what is going on, so I am going to hack at the do_search module returning data/sql at various stages of its progression.

ok, I back track and add some existing search terms from my last comment.

here is the search page with the new search terms

DO a custom leadership directory search here.
remember, output must be against that tablular ouptut thing.
your keys are here:
location united states see map google maps dude
do search
like, dude, its not normalize

so my keys are there, and its not doing squat.

let me change the 'type' in the tables back to node and see if that fixes it.

mysql> update search_index set type = 'node' where type = 'ld';
Query OK, 91 rows affected (0.45 sec)
Rows matched: 91  Changed: 91  Warnings: 0

mysql> update search_dataset set type = 'node' where type = 'ld';
Query OK, 5 rows affected (0.26 sec)
Rows matched: 5  Changed: 5  Warnings: 0

now I rerun the output and get...

DO a custom leadership directory search here.
remember, output must be against that tablular ouptut thing.
your keys are here:
location united states see map google maps dude
do search
like, dude, its not normalize

now I am irritated.

hmmmm.

ok. I can scrap this approach and write my own search or try to grok this and leverage it.

I choose grok. if not grokked by today, then I write my own search on my own tables tomorrow.

bleah.

but why are they being ignored....

lets look at the next step in the do_search function of the search.module.

$normalize = db_result(db_query('SELECT MAX(relevance) FROM temp_search_sids'))

SO I run the sql against my ld_hack table

mysql> SELECT MAX(relevance) FROM ld_hack;
+------------------+
| MAX(relevance)   |
+------------------+
| 0.30103000998497 |
+------------------+
1 row in set (0.05 sec)

mysql> 

know, I am still making the assumption that the value in the temp table is the same as the one in my ld_hack table.

lets check MAX against a zero...

mysql> update ld_hack set relevance = 0;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> SELECT MAX(relevance) FROM ld_hack;
+----------------+
| MAX(relevance) |
+----------------+
|              0 |
+----------------+
1 row in set (0.04 sec)

mysql> 

ok, so max will return a row...whats up with the db result

lets try another hack...lets hack $normalize.....

hnmmm

ok, we are back to where we started to backtrack in this whole thing.

here is the hack in the do_search function

  // Calculate maximum relevance, to normalize it
  $normalize = db_result(db_query('SELECT MAX(relevance) FROM temp_search_sids'));
  $normalize = db_result(db_query('SELECT MAX(relevance) FROM ld_hack'));
	
  if (!$normalize) {
    return 'like, dude, its not normalize';
    return array();
  }
return 'I need a new career';

first , I delete from ld_hack in mysql

then I run it.

with the following code on the ld_do_search page.

function ld_do_search(){
		print 'DO a custom leadership directory search here.<br>';
		print 'remember, output must be against that tablular ouptut thing.<br>';
		print 'your keys are here: <br>';
		print $_POST['edit']['keys'] .'<br>';
		print 'do search <br>';
		$find = 	do_search($_POST['edit']['keys'],'ld');
    print 'find is ' . $find .'<br>';
		foreach ($find as $item){
		 print 'item:'. $item.'<br>';
			foreach($item as $duh){
			 print 'duh:'. $duh.'<br>';
		  }
		}

and we get...

DO a custom leadership directory search here.
remember, output must be against that tablular ouptut thing.
your keys are here:
dude
do search
find is i need a new career

so, I basically backtracked for nothing...ok, lets move on...

but wait, lets check some assumptions.

I am assuming that the $normalize from my hit on ld_hack is the same as the $normalize against the temp table.

lets clean up the db, deactivate my hit and run against the temp table and see if we get the same result.

// Calculate maximum relevance, to normalize it
  $normalize = db_result(db_query('SELECT MAX(relevance) FROM temp_search_sids'));
	//  $normalize = db_result(db_query('SELECT MAX(relevance) FROM ld_hack'));
	
  if (!$normalize) {
		 return 'like, dude, its not normalize';
    return array();
  }
	return 'i need a new career';
	

output is...

DO a custom leadership directory search here.
remember, output must be against that tablular ouptut thing.
your keys are here:
dude
do search
find is i need a new career

ok, assumption was ok.

I need to reread my thread here to see why $normalize originally did not work and the return of 'like dude its not normalize' got triggered.

back after these messages from our sponsor.

t.

the $select2 variable is built next..

 $select2 = str_replace('i.relevance', '('. (1.0 / $normalize) .' * i.relevance)', $select2);
  return $select2;

modify my trap code

function ld_do_search(){
		print 'DO a custom leadership directory search here.<br>';
		print 'remember, output must be against that tablular ouptut thing.<br>';
		print 'your keys are here: <br>';
		print $_POST['edit']['keys'] .'<br>';
		print 'do search <br>';
		$find = 	do_search($_POST['edit']['keys'],'ld');
    print 'select2 is ' . $find .'<br>';

gives me

DO a custom leadership directory search here.
remember, output must be against that tablular ouptut thing.
your keys are here:
dude
do search
select2 is (3.32192793685 * i.relevance) AS score

ok, moving on.

OK, we are sane up to here.

here is the next block in the do_search function of the search.module that I need to grok.
I have already added a return to get the $condtions variable.

 // Second pass: only keep items that match the complicated keywords conditions (phrase search, negative keywords, ...)
  $conditions = '('. $query[0] .')';
	return $conditions;
  $arguments = array_merge($arguments2, $query[1]);
  $result = db_query_temporary("SELECT i.type, i.sid, $select2 FROM temp_search_sids i INNER JOIN {search_dataset} d ON i.sid = d.sid AND i.type = d.type $join2 WHERE $conditions $sort_parameters", $arguments, 'temp_search_results');
  if (($count = db_result(db_query('SELECT COUNT(*) FROM temp_search_results'))) == 0) {
    return array();
  }
  $count_query = "SELECT $count";

here is the output

DO a custom leadership directory search here.
remember, output must be against that tablular ouptut thing.
your keys are here:
dude
do search
conditions is (d.data LIKE '%% %s %%')

ok, some observations.

what table is 'd' and where is d.data.
it looks like the %%%s%% is escaping the % and filling in for quotes and for the % thingy for the d.data LIKE '%s' where s would presumably be 'dude', but lets hold off.

lets move on to the next stage.

the final section of the search.module do_search function reads...

 $count_query = "SELECT $count";

  // Do actual search query
  $result = pager_query("SELECT * FROM temp_search_results", 10, 0, $count_query);
  $results = array();
  while ($item = db_fetch_object($result)) {
    $results[] = $item;
  }
  return $results;

now lets see what to do next.

lets go a step at a time.

lets look at teh pager_query arguments to understand what we are seeing.
from the api docs

Parameters

$query The SQL query that needs paging.

$limit The number of query results to display per page.

$element An optional integer to distinguish between multiple pagers on one page.

$count_query An SQL query used to count matching records.

... A variable number of arguments which are substituted into the query (and the count query) using printf() syntax. Instead of a variable number of query arguments, you may also pass a single array containing the query arguments.

so, this seems to be saying, select from the temp_search_results table limit to 10 results per page with a select count as the max number in the temp_search_results table.

lets see if I get any results by sticking a return statement in the WHILE loop

  while ($item = db_fetch_object($result)) {
			return 'in the while loop';
    $results[] = $item;
  }

I modify my trapping code

function ld_do_search(){
		print 'DO a custom leadership directory search here.<br>';
		print 'remember, output must be against that tablular ouptut thing.<br>';
		print 'your keys are here: <br>';
		print $_POST['edit']['keys'] .'<br>';
		print 'do search <br>';
		$find = 	do_search($_POST['edit']['keys'],'ld');
    print 'while loop entered?   is ' . $find .'<br>';

and I get...

DO a custom leadership directory search here.
remember, output must be against that tablular ouptut thing.
your keys are here:
dude
do search
while loop entered? is in the while loop

Ok.

here is the final line in the search.module do_search function.

 return $results;

because of this discussion, we know that $results is a array of arrays from the db hit against temp_search_results which looks like my ld_hack_2 table

mysql> describe ld_hack_2;
+-------+------------------+------+-----+---------+-------+
| Field | Type             | Null | Key | Default | Extra |
+-------+------------------+------+-----+---------+-------+
| type  | varchar(64)      | YES  |     | NULL    |       |
| sid   | int(10) unsigned | YES  |     | NULL    |       |
| score | float            | YES  |     | NULL    |       |
+-------+------------------+------+-----+---------+-------+
3 rows in set (0.07 sec)

mysql> select * from ld_hack_2
    -> ;
+------+------+-------+
| type | sid  | score |
+------+------+-------+
| ld   |  120 |     1 |
| ld   |  120 |     1 |
+------+------+-------+

so, this is getting really cool.

We now know that the do search function will return an array of *pointers* to node content of my custom type that I can now use to go get hit the db with and return my custom output.

This is a good stage to go grab a salami sandwich, but first, some random thoughts.

the node.module, where it invokes do_search has the following code...

 foreach ($find as $item) {
        $node = node_load($item->sid);

        // Get node output (filtered and with module-specific fields).
        if (node_hook($node, 'view')) {
          node_invoke($node, 'view', false, false);
        }
        else {
          $node = node_prepare($node, false);
        }
        // Allow modules to change $node->body before viewing.
        node_invoke_nodeapi($node, 'view', false, false);

        // Fetch comments for snippet
        $node->body .= module_invoke('comment', 'nodeapi', $node, 'update index');
        // Fetch terms for snippet
        $node->body .= module_invoke('taxonomy', 'nodeapi', $node, 'update index');

        $extra = node_invoke_nodeapi($node, 'search result');
        $results[] = array('link' => url('node/'. $item->sid),
                           'type' => node_get_name($node),
                           'title' => $node->title,
                           'user' => theme('username', $node),
                           'date' => $node->changed,
                           'node' => $node,
                           'extra' => $extra,
                           'snippet' => search_excerpt($keys, $node->body));
      }

ignoring the nodeapi calls, we see that there are no other db calls involved (unless the node_load function has one...lets check that.

well, well, it does here is the code

  // Retrieve the node.
  // No db_rewrite_sql is applied so as to get complete indexing for search.
  if ($revision) {
    array_unshift($arguments, $revision);
    $node = db_fetch_object(db_query('SELECT n.nid, r.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM {node} n INNER JOIN {users} u ON u.uid = n.uid INNER JOIN {node_revisions} r ON r.nid = n.nid AND r.vid = %d WHERE '. $cond, $arguments));
  }
  else {
    $node = db_fetch_object(db_query('SELECT n.nid, n.vid, n.type, n.status, n.created, n.changed, n.comment, n.promote, n.moderate, n.sticky, r.timestamp AS revision_timestamp, r.title, r.body, r.teaser, r.log, r.format, u.uid, u.name, u.picture, u.data FROM {node} n INNER JOIN {users} u ON u.uid = n.uid INNER JOIN {node_revisions} r ON r.vid = n.vid WHERE '. $cond, $arguments));
  }

and that, my friends is the answer to this problem.

I will invoke the do_search function to get a list of pointers to nodes of my custom type.

I will then semi-duplicate the node_load function with something I think up after lunch.

During that node_load semi_duplication, I will do my db calls with the pager_query to get my output .

I am feeling better.

I will summarize in the next comment. after my salami sandwich.

Ok, lets take a breath and recap where we are...

• we create a custom search page utilizing the search.module's search_form() function with arguments telling it to only search for nodes of a certain type.
• the form redirects to a callback page where I do a call to do_search on the search.module
• the do_search returns an array of *pointers* to relevant nodes of the correct type that I can
  no use to go extract my custom content.

so, to continue, I need to

• write a private function to pull back the information I want to display
• write the sql that does exactly that
• call page_query ?? to get my data returned in a format I want
• output themed results

One final thing to keep in mind....

• I am not sure that my nodes stored with type = 'ld' on an ld_insert or if I ran a custom sql update for development purposes. I may need to revisit this after I get the results going
• if not, ld_update_index or ld_nodeapi('update index') could be utilized to git r done

ok, lets clean up the ld_do_search page to do a call to build some sql or something....

hmmm, let me think about this.

ok, I am not going to give away the structure of my client's database, but here is the structure of the ld_s

function ld_do_search(){
  $strSQL = '';

  $find = 	do_search($_POST['edit']['keys'],'ld');
  $strSQL = _build_ld_search_results_sql($find);
 
  print $strSQL;
}

function _build_ld_search_results_sql($arg){
                //for a bad arg return sql that always returns an empty set
		if(!$arg)
		  return 'SELECT * FROM node WHERE 2 = 1';  
  $inclause = '(';
  foreach ($arg as $item) 
    $inclause .= $item->sid . ",";

  $inclause = trim($inclause,',');
  $inclause .=  ')';
	
  $strSQL = 'SELECT FIELDS YOU WANT TO DISPLAY '.
	     			' FROM relevant tables             '.
			      ' WHERE nid IN '. $inclause ;
	
  return $strSQL;
	
}

gives me the following result

SELECT FIELDS YOU WANT TO DISPLAY FROM relevant tables WHERE nid IN (120,120)

I am NOT going to worry about the duplicate node ids at this point, I can clean that up later.
I am going to build my sql and figure out the pager_query stuff or whatever I need to display a sortable, columnized table.

I may have to pick up posting tomorrow because the sql may be a bit tricky to develop.

thanks for listening !

this has helped me, I hope it helps somebody out there.

Short answer, my bad sql/data in a development db with some crufty data.

Here is how I found out.

My custom search hook starts out looking like this...

function ld_do_search($arg = NULL){
  $strSQL = '';
  $s;
  if(!$arg)
    $find = do_search($_POST['edit']['keys'],'ld');  
  else
    $find = do_search($arg,'ld');
  
  $strSQL = _ld_build_search_results_sql($find,'IN');
  $result = pager_query(db_rewrite_sql($strSQL),1);
  $s .= '<table> ';

My _ld_build_search_results _sql() fcn takes the keys,etc returned from the do_search function and returns sql that will return my customer's data.

On a hunch, I wanted to verify that the $find array only had one element, so I modified my code as follows:

function ld_do_search($arg = NULL){
  $strSQL = '';
  $s;
  if(!$arg)
    $find = do_search($_POST['edit']['keys'],'ld'); 
  else
    $find = do_search($arg,'ld');

 foreach ($find as $key){
     print 'key:'. $key.'<br>';
       foreach($key as $value){
         print 'value:'. $value.'<br>';
       }
    }
  $strSQL = _ld_build_search_results_sql($find,'IN');
  $result = pager_query(db_rewrite_sql($strSQL),1);
  $s .= '<table> .....';

and I got.

key:Object
value:ld
value:121
value:1.00000000000053
key:Object
value:ld
value:122
value:1.00000000000053

Which tells me the core drupal search functionality is working exclusivelly against my custom node type (WOO HOO!) and that the problem is with crufty data in my custom tables for my custom node type.

The data portion of this project has been solved.

now onto the presentation.

In order for my new custom nodes content to be indexed by the search engine, I implement hook_update_index.

The pseudo-code for this is basically.

• get a list of all nodes of my custom type that have not been indexed
• retrieve all the data I want indexed
• Wrap that data in tags with important terms wrapped in header tags per the docs in search.module
• invoke the search_index hook on the search.module to get my terms indexed

function ld_update_index() {
 $q = "SELECT nid FROM {node} WHERE type = 'ld' AND nid NOT IN (SELECT sid FROM {search_index} WHERE type = 'ld')";  
	
$r = db_query($q);
while ($outer_rs = db_fetch_object($r)){
  $strSQL = _ld_build_search_update_index_sql($outer_rs->nid);
   $result = db_query($strSQL);
   while($rs = db_fetch_object($result)){
     $s = _ld_build_search_update_index_html($rs);
	if('' == $s)
	  continue;
	else
	  search_index($rs->nid, 'ld', $s);
    }
  }
}

It works.

I have to debug an issue, here.

I have a couple of issues going on to resolve.

Here is one of them.

When I run a search against one term in one node (that has been indexed, correctly) I get correct results.

When I use two terms spanning indexed nodes (my custom node types) I get no results.

	if(!$arg){
	 $find = do_search($_POST['edit']['keys'],'ld');
	 print 'not arg, keys are:';
  	 print $_POST['edit']['keys'] .'<br>';
        foreach ($find as $item){
          print 'item:'. $item.'<br>';
          foreach($item as $duh){
            print 'duh:'. $duh.'<br>';
      }

When I search for one value, I get

not arg, keys are:programming
item:Object
duh:ld
duh:149
duh:1.00000000000008
SELECT l....below above code, I print out the final search sql...

which shows a $find array as discussed earlier.

However with two terms...

I get..

not arg, keys are:exper programming
SELECT 1 FROM leadership_directory WHERE 1 = 2

where the $find array is null and the select statement is arg checking code that returns sql that returns no results.

the big question is why is search.module.do_search no finding the two keys which I have visually identifies as being indexed in the search.index...

mysql> select * from search_index where type='ld' and word in ('exper','programming');
+-------------+-----+------+---------+----------+-------+
| word        | sid | type | fromsid | fromtype | score |
+-------------+-----+------+---------+----------+-------+
| exper       | 157 | ld   |       0 | NULL     |    19 |
| programming | 149 | ld   |       0 | NULL     |    26 |
+-------------+-----+------+---------+----------+-------+
2 rows in set (0.04 sec)

I gotta run for a few hours, but will update as I debug.

t

Ok,

In my earlier work, in this comment: http://drupal.org/node/124963#comment-207924 on this topic, I glossed over the $query=search_parse_query($keywords) function.

Here I pick it up.

First my 'trapper code' in the ld_do_search fcn

function ld_do_search($arg = NULL){
  $strSQL = '';
  $s;

	if(!$arg){
		$find = do_search($_POST['edit']['keys'],'ld');   //do_search is a fcn  in search.module that returns sids and scores
		print '<br>if <br>keys are:';
  	print $_POST['edit']['keys'] .'<br>';
    foreach ($find as $item){
      print 'item:'. $item.'<br>';
      foreach($item as $duh){
        print 'duh:'. $duh.'<br>';
      }
    }			
	}else{
  	$find = do_search($arg,'ld');
	  print 'else <br>';
    foreach ($find as $item){
      print 'item:'. $item.'<br>';
      foreach($item as $duh){
        print 'duh:'. $duh.'<br>';
      }
    }					

Here is I have written some debugging code in the search modules do_search function...

function do_search($keywords, $type,...yada yada yada.... DESC') {
  $query = search_parse_query($keywords);
	print '<br>query is: ' .$query;
	foreach($query as $item){
  	print '<br>item->'.$item;
  	foreach($item as $value)
  	  print '<br>value->'.$value;
  }		

Here is the output:

query is: Array
item->d.data LIKE '%% %s %%' AND d.data LIKE '%% %s %%'
item->Array
value->exper
value->programming
item->i.word = '%s' OR i.word = '%s'
item->Array
value->exper
value->programming
item->2
if
keys are:exper programming

the next portion of the search.module.do_search is

 if ($query[2] == '') {
    form_set_error('keys', t('You must include at least one positive keywo........
  if ($query === NULL || $query[0] == '' || $query[2] == '') {
    return array();
  }

we see that $query[2] contains keywords (I will check it anyways in next comment.

I don't think the other if will happen either, but lets check it.

on to the next post.