Taxonomy-restricted faceted search environment returns no results

Sorry for the delayed response. This is a views-related problem. The faceted search returns a normal result set when I use Teasers (didn't try Extracts).

However, I'm trying to use views to restrict the result set, using the presence of a particular taxonomy term as my constraint. That's the only way I can see to use faceted search to display only nodes with a particular term assigned to them (not just nodes of a particular node type). If there's another solution, I'd love to know.

Basically what I'm trying to do is make it so that I can have faceted search views that display only the secular content on a website for a Christian nonprofit. We already have a faceted search that displays all the content. The problem is that the secular content & the faith content are of the same nodetypes and are only differentiated by a taxonomy term. So I can't just create a different environment; I also have to use a view to add additional restrictions.

I can confirm that both the Teasers & Extracts display styles work. The Views style doesn't work for me - whether I set the argument to "Display all values" or "Summary - unsorted." Is there another setting on the argument which I'm missing?

I noticed that the refinements on UrbanResource.net work - that is, if you click on a particular facet, the other facets will update in the block to indicate how many nodes there are of that facet. However, in the main content window, it always says "Your search yielded no results." Maybe there's a conflict with another module, or the display for the faceted search is set up incorrectly.

I just added a page & block view, thinking that might help, but it seems to have made no difference. Possibly it has to do with the fact that when I use views, I can't filter by a taxonomy term when there is only one taxonomy term in the vocabulary (it doesn't show up). So I had to add another term. But when I added the new term the term I wanted (UrbanResource.net) showed up.

Below is the code for the view. Thanks so much for your help debugging this!

 $view = new stdClass();
  $view->name = 'urbanresource_search';
  $view->description = 'Search view for UrbanResource.net content';
  $view->access = array (
);
  $view->view_args_php = '';
  $view->page = TRUE;
  $view->page_title = '';
  $view->page_header = '';
  $view->page_header_format = '1';
  $view->page_footer = '';
  $view->page_footer_format = '1';
  $view->page_empty = '';
  $view->page_empty_format = '1';
  $view->page_type = 'teaser';
  $view->url = 'ursearch-results';
  $view->use_pager = TRUE;
  $view->nodes_per_page = '15';
  $view->block = TRUE;
  $view->block_title = '';
  $view->block_header = '';
  $view->block_header_format = '1';
  $view->block_footer = '';
  $view->block_footer_format = '1';
  $view->block_empty = '';
  $view->block_empty_format = '1';
  $view->block_type = 'teaser';
  $view->nodes_per_block = '15';
  $view->block_more = FALSE;
  $view->block_use_page_header = FALSE;
  $view->block_use_page_footer = FALSE;
  $view->block_use_page_empty = FALSE;
  $view->sort = array (
  );
  $view->argument = array (
    array (
      'type' => 'faceted_search_results',
      'argdefault' => '3',
      'title' => '',
      'options' => 'none',
      'wildcard' => '',
      'wildcard_substitution' => '',
    ),
  );
  $view->field = array (
  );
  $view->filter = array (
    array (
      'tablename' => 'term_node_4',
      'field' => 'tid',
      'operator' => 'AND',
      'options' => '',
      'value' => array (
  0 => '3',
),
    ),
  );
  $view->exposed_filter = array (
  );
  $view->requires = array(term_node_4);
  $views[$view->name] = $view;

Our server is running PHP 5.2.5. It might be that we're getting error messages because it's conflicting with one of the other search modules that we have installed.

We have Search 404 5.x-1.1, Active Search 5.x-1.1, and Google Co-op CSE 5.x-1.4 currently active. I don't know whether any of those modules have functionality that conflicts with the way Faceted Search uses Views to display nodes that match a particular facet.

Our Views install is 5.x-1.6.

Taxonomy is 5.7 & has the following dependent modules enabled:
Required by: Ad (enabled), Article (enabled), Forum (enabled), GCG Taxonomy (enabled), Image Gallery (enabled), Simplenews (enabled), Taxonomy Access Control Lite (enabled), Tagadelic (enabled), Taxonomy Batch Operations (enabled), Taxonomy Facets (enabled), Taxonomy Manager (enabled), Taxonomy Theme (enabled), Catalog (enabled).

Using the Devel module on our test site, I think I was able to determine where the process is failing. It regards table prefixing for the temporary tables (see the bottom for my question). Also, I think this issue may a duplicate of http://drupal.org/node/251760.

On normal faceted_search environments (ones that don't use the Views integration), it creates the temporary table that it needs in order to populate the view with nodes. For example:

CREATE TEMPORARY TABLE temp_faceted_search_results_1 SELECT DISTINCT(n.nid) AS nid, 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), MAX(c.last_comment_timestamp)) - 1211832181) * 6.43e-8) + 5 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 0.0555555555556)) AS score FROM um_node AS n INNER JOIN um_term_node AS term_node_148 ON n.nid = term_node_148.nid LEFT JOIN um_node_comment_statistics AS c ON n.nid = c.nid WHERE ((n.status = 1) AND (n.type IN ('audio','blog','amazon_node','ccdapage','cityvision','event','forum','grants','image','job','weblink','amazon','page','poll','resume','review','testimony','upload','video','volunteer_opportunity','webcast','book')) AND (term_node_148.tid = 148)) GROUP BY n.nid ASC ORDER BY score DESC

Then it executes the following statements (using http://www.urbanministryjobs.org/tag/john-perkins as an example):

SELECT COUNT(*) FROM temp_faceted_search_results_1; SELECT 85; SELECT * FROM temp_faceted_search_results_1 LIMIT 0, 15.

By contrast, the faceted search environment which uses views (http://www.urbanministryjobs.org/views_filter/results/taxonomy%3A78) creates the table as follows:

CREATE TEMPORARY TABLE temp_faceted_search_results_11 SELECT DISTINCT(n.nid) AS nid, 5 * POW(2, (GREATEST(MAX(n.created), MAX(n.changed), MAX(c.last_comment_timestamp)) - 1211832181) * 6.43e-8) + 5 * (2.0 - 2.0 / (1.0 + MAX(c.comment_count) * 0.0555555555556)) AS score FROM um_node AS n INNER JOIN um_term_node AS term_node_78 ON n.nid = term_node_78.nid LEFT JOIN um_node_comment_statistics AS c ON n.nid = c.nid WHERE ((n.status = 1) AND (n.type IN ('weblink','page','video','book')) AND (term_node_78.tid = 78)) AND n.nid IN (SELECT DISTINCT(node.nid) FROM um_node node ) GROUP BY n.nid ASC ORDER BY score DESC

Then it executes the following statement:
SELECT COUNT(*) FROM temp_faceted_search_results_11;

Then it tries to do the following INNER JOIN, generating a table not found error:

SELECT count( DISTINCT(node.nid)) FROM um_node node INNER JOIN um_temp_faceted_search_results_11 temp_faceted_search_results_11 ON node.nid = temp_faceted_search_results_11.nid.

The error is as follows:

Table 'techmi5_drupal0528.um_temp_faceted_search_results_11' doesn't exist query: pager_query SELECT DISTINCT(node.nid) FROM um_node node INNER JOIN um_temp_faceted_search_results_11 temp_faceted_search_results_11 ON node.nid = temp_faceted_search_results_11.nid LIMIT 0, 15 in /home/techmi5/public_html/urbanministryjobs/includes/database.mysql.inc on line 172.

Is it possible that this INNER JOIN is what's causing the problem? What is causing the INNER JOIN between a prefixed table & a non-prefixed table? Something in the views module?

Alternatively, there are lengthy queries that cache_set does on um_cache_views prior to the CREATE TEMPORARY TABLE statement. I could reproduce one of them, if you think it would be relevant. Thanks again for your help.

Update:

I noticed that load_categories is working fine (for example, on http://www.urbanministryjobs.org/views_filter - the home search page for the environment). Following this line:
SELECT COUNT(*) FROM temp_faceted_search_results_11
, it executes the following statement:

SELECT COUNT(DISTINCT(n.nid)) AS count, term_data.tid AS term_data_tid, term_data.name AS term_data_name FROM um_node AS n INNER JOIN temp_faceted_search_results_11 AS results ON n.nid = results.nid INNER JOIN um_term_node AS term_node ON n.nid = term_node.nid INNER JOIN um_term_data AS term_data ON term_node.tid = term_data.tid WHERE ((term_data.vid = 8) AND (n.type IN ('weblink','page','video','book'))) GROUP BY term_data_tid ASC ORDER BY count DESC, term_data.weight ASC, term_data.name ASC LIMIT 0, 11

Note that it is doing an INNER JOIN on temp_faceted_search_results_11, but not adding in the non-existent um_temp_faceted_search_results_11.

By contrast, the bad query on the results pages is being executed as part of the pager_query function.

I found that this issue was a duplicate of http://drupal.org/node/227634 (but with a different version number of the module).

Following the workaround for Views prefixing temporary tables stated in http://drupal.org/node/129838, I was able to get the Views integration to work.

I added the following to our settings.php:

 $db_prefix = array(
   'default'   => '[your_default_prefix]_',
   'temp_faceted_search_results_[env_id]'     => '',
   ); 

Note that you can find out what the env_id is for any given faceted search environment by opening your Drupal database & executing the following SQL query:
SELECT * FROM [username]_drupal.[your_default_prefix]_faceted_search_variables ORDER BY env_id

That lets you see the settings for all the faceted search environments you've created.

David, would it be possible for the information about Views integration w/table prefixing to go in the readme?