view finder with taxonomy and taxonomy terms

Looks to be a bug at a glance - I will check it out further.

same problem for me; it even actually throws error if I select only one criteria.

Forbidden
You don't have permission to access /search_course_catalog/ /Sponsor Users on this server.

here is my finder:

array( 'finder_id' => '1', 'base' => 'views', 'title' => 'Search Course Catalog', 'description' => '', 'path' => 'search_course_catalog', 'block' => '0', 'settings' => array( 'form_on_page' => 1, 'form' => array( 'prefix' => '', 'suffix' => '', 'button_text' => 'Find', 'go_text' => 'Go', ), 'advanced' => array( 'filter' => 0, 'submission' => 1, 'ahah' => 0, 'ahah_effect' => 'none', 'element_combination' => '0', 'pager' => '10', 'match' => 'equals', 'goto' => '', 'hide_args' => 0, 'arg_sep' => ',', 'show_links' => 1, 'show_admin_links' => 1, 'search' => '0', ), 'views' => array( 'view' => 'view_of_course_catalog', 'displays' => array( 'query' => 'default', 'output' => 'default', ), 'results' => '0', 'views_args' => array( 'args' => '', 'delimiter' => '/', ), ), ), 'elements' => array( '0' => array( 'finder_element_id' => '1', 'finder_id' => '1', 'element' => 'select', 'title' => 'Format', 'weight' => '0', 'settings' => array( 'form' => array( 'description' => '', 'prefix' => '', 'suffix' => '', 'default_value' => '', 'required' => 0, 'size' => '', 'multiple' => 0, 'empty' => 'prepend', 'empty_text' => 'please choose', ), 'choices' => array( 'field' => array( 'term_data_2.name' => 'term_data_2.name', ), 'views' => array( 'args' => '', 'delimiter' => '/', ), 'displays' => array( 'query' => 'default', ), ), ), 'element_handler' => array( '#title' => 'Select list', '#module' => 'finder_optionwidgets', ), '#_finder_object' => '1', ), '1' => array( 'finder_element_id' => '2', 'finder_id' => '1', 'element' => 'select', 'title' => 'Target Audience', 'weight' => '1', 'settings' => array( 'form' => array( 'description' => '', 'prefix' => '', 'suffix' => '', 'default_value' => '', 'required' => 0, 'size' => '', 'multiple' => 0, 'empty' => 'prepend', 'empty_text' => 'please choose', ), 'choices' => array( 'field' => array( 'term_data_1.name' => 'term_data_1.name', ), 'views' => array( 'args' => '', 'delimiter' => '/', ), 'displays' => array( 'query' => 'default', ), ), ), 'element_handler' => array( '#title' => 'Select list', '#module' => 'finder_optionwidgets', ), '#_finder_object' => '1', ), ), 'elements_index' => array( '1' => 0, '2' => 1, ), 'base_handler' => array( '#title' => 'Views finder', '#module' => 'finder_views', ), 'admin_links' => array( 'search_course_catalog' => 'View \"Path\"', 'admin/build/finder/1/edit' => 'Edit', ), 'links' => array(), '#_finder_object' => '1', )

It seems that ANDing two WHERE conditions on two joins of the terms table results in two rows being selected, one for each term. I don't know what to do about this.

A working query (match any element):

SELECT 
  node.nid AS nid, 
  node.title AS node_title, 
  node.type AS node_type, 
  node.vid AS node_vid, 
  term_data_2.name AS term_data_2_name, 
  term_data_2.vid AS term_data_2_vid, 
  term_data_2.tid AS term_data_2_tid, 
  term_data_1.name AS term_data_1_name, 
  term_data_1.vid AS term_data_1_vid, 
  term_data_1.tid AS term_data_1_tid 
FROM {node} node 
LEFT JOIN {term_node} term_node 
  ON node.vid = term_node.vid 
LEFT JOIN {term_data} term_data_2 
  ON term_node.tid = term_data_2.tid AND term_data_2.vid = 2 
LEFT JOIN {term_data} term_data_1 
  ON term_node.tid = term_data_1.tid AND term_data_1.vid = 1 
WHERE ((term_data_2.name = '%s')) AND ((term_data_1.name = '%s'))

The bung query (match all elements):

SELECT 
  node.nid AS nid, 
  node.title AS node_title, 
  node.type AS node_type, 
  node.vid AS node_vid, 
  term_data_2.name AS term_data_2_name, 
  term_data_2.vid AS term_data_2_vid, 
  term_data_2.tid AS term_data_2_tid, 
  term_data_1.name AS term_data_1_name, 
  term_data_1.vid AS term_data_1_vid, 
  term_data_1.tid AS term_data_1_tid 
FROM {node} node 
LEFT JOIN {term_node} term_node 
  ON node.vid = term_node.vid 
LEFT JOIN {term_data} term_data_2 
  ON term_node.tid = term_data_2.tid AND term_data_2.vid = 2 
LEFT JOIN {term_data} term_data_1 
  ON term_node.tid = term_data_1.tid AND term_data_1.vid = 1 
WHERE ((term_data_2.name = '%s')) OR ((term_data_1.name = '%s')) 

The only difference is that last line.

If you actually look at a query like that without the WHERE condition you can see in the results how the WHERE will never work.

The results are like:

nid: 71
title: A node
term_data_1: fish
term_data_2: NULL

nid: 71
title: A node
term_data_1: NULL
term_data_2: small

change your way to create view will generate only one record:
when you add a new field to the view default display, choose 'Taxonomy: All terms' then 'Limit terms by vocabulary', pick the first vocabulary; add other vocabulary in same way.

here is the sql:

SELECT node.nid AS nid,
   node_revisions.body AS node_revisions_body,
   node_revisions.format AS node_revisions_format,
   node.title AS node_title,
   node.vid AS node_vid
 FROM node node 
 LEFT JOIN node_revisions node_revisions ON node.vid = node_revisions.vid
 WHERE node.type in ('course_catalog')

but the problem is, even i create view this way, i print the $result by the end of finder_views_finder_find(), but it is still only one term per record.

are you using Views search function? I wonder if that's where the problem is. (I haven't really understood your code and Views2 code yet)

Not quite sure what you mean wang. The way finder uses views is by supplying it's own plugin display (in finder/modules/finder_views/includes) which manipulates the views query.

The only sort of query I can think of that would work is like some sort of subselect or a union of selects but I'm not even sure if views will let me do that.

the sql I provided is from the view (attached).

---------
the Views filters work for me now, so I'll use that for now.

attach my Views export.

did i mix up those two queries in #3 :/

SELECT
  node.nid AS nid,
  node.title AS node_title,
  node.type AS node_type,
  node.vid AS node_vid,
  term_data_1.name AS term_data_1_name,
  term_data_1.vid AS term_data_1_vid,
  term_data_1.tid AS term_data_1_tid
FROM {node} node
LEFT JOIN {term_node} term_node
  ON node.vid = term_node.vid
LEFT JOIN {term_data} term_data_1
  ON term_node.tid = term_data_1.tid AND term_data_1.vid = 1
WHERE IN (
	SELECT
	  node.nid AS nid,
	  node.title AS node_title,
	  node.type AS node_type,
	  node.vid AS node_vid,
	  term_data_2.name AS term_data_2_name,
	  term_data_2.vid AS term_data_2_vid,
	  term_data_2.tid AS term_data_2_tid,
	FROM {node} node
	LEFT JOIN {term_node} term_node
	  ON node.vid = term_node.vid
	LEFT JOIN {term_data} term_data_2
	  ON term_node.tid = term_data_2.tid AND term_data_2.vid = 2
)

now how do i get views to give me that subquery as sql :/

Perhaps I should just create more 'element combination' methods which simply combine the results from multiple queries. Ideally this would be intersected sql statements, but a php intersect is likely too.

I work on this for a few hours every weekend, but mostly I hit dead ends or change my mind about how this should work.
Today i was working on comparing the results using PHP, but that seems like far too much trouble.
Now I'm thinking more along the lines of

SELECT nid, field1, field2
FROM {node}
WHERE nid IN (SELECT nid, field1 as field1 FROM {node} LEFT JOIN {field1tables} WHERE field1 = 'field1 keyword')
AND nid IN (SELECT nid, field2 as field2 FROM {node} LEFT JOIN {field2tables} WHERE field2 = 'field2 keyword')

but I don't even know if that's going to work

It will work in terms of selecting the right nodes, etc.. but I don't know if I can get all the field values to be available in the theme function, and that may be sql version dependant? I have no idea.

Rest assured my work on finder is focused on this issue, and I probably won't release anything else until some solution is implemented.

yeah the plan is to make it flexible enough to do that

it was just holidays and i've had the flu and I'll be honest.... this is not a fun issue

I'll tell you basically what i've done so far

Starting with finder.admin.inc I've changed the element_combination setting like so:

<?php
  $form['settings']['advanced']['element_combination'] = array(
    '#type' => 'radios',
    '#title' => t('Multiple element result combination'),
    '#default_value' => isset($finder->settings['advanced']['element_combination']) ? $finder->settings['advanced']['element_combination'] : 0,
    '#weight' => 40,
    '#description' => t('With multiple elements, how should the results of each element be combined on the results page?'),
    '#options' => array(
      0 => t('<em>Conjunction</em> - Match <em>all</em> elements using the AND operator.'),
      1 => t('<em>Disjunction</em> - Match <em>any</em> element using the OR operator.'),
      2 => t('<em>Intersect</em> - Match <em>all</em> elements using the IN keyword. '),
    ),
  );

?>

So options 0 and 1 are still the same, they just have a different description. The new option we need to implement is option 2.

For now I am happy to only implement this in Finder Views. (Finder Node and Finder User can completely remove the 'Intersect' choice from the admin form with form alter, for all I care - these modules are probably not complicated enough yet to require an implementation of this)

In finder_views.module in finder_views_finder_find()
right after

  if ($view = views_get_view($view_name)) {

I have put

    if ($finder->settings['advanced']['element_combination'] == 2) {
      // We need to split up this request into one request for each element,
      // get the SQL for that element, and then use them together in this request
      // as wheres for an SQL thing.
    }

which is just a placeholder for where I think the implementation should go.

The plan is that, instead of following through and doing the view the normal AND/OR way, it will get each subquery sql (as in - each sql query to get the results for each finder element), using views, and build a query out of that (something like the format I gave in post #12). I don't know whether to try and reuse the function finder_views_finder_find() for that purpose, or create a totally new function to get the subqueries. I have already discovered I can change the last couple lines of finder_views_finder_find() to return the SQL code from the built view, rather than executing the SQL and giving results:

    // Make sure the query is not cached
    $view->is_cacheable = FALSE;

    $view->build($display);
    dsm($view); //<-- it's in there somewhere under 'built' or something like that.

My head is in a totally different place at the moment, but I will try to get back on track and at least attempt to do what I am talking about.
I don't know if I gave all the information I have, there is also a lot of crap in my module from when I tried to combine the element results with PHP - which wound up being a big mess.

I worked on it a few hours today, I made more changes to finder_views_finder_find() and the file finder_views_plugin_display_finder.inc
it's on track, but damn it's mid afternoon and I'm over it
I don't feel like I'm at a point where I can commit my changes yet. My intention is to do a commit in the next few days that will bring the CVS up to speed with what I'm doing.

well it seems to work in my basic use-case, I'll commit this today so you guys can try it soon

I reckon I know what to do - reset the fields when doing the subqueries and prevent any more from being added in

OK so the reason I didn't see this problem before is because my view doesn't use the 'sort' stuff. It's not the fields I need to remove, but the sorts. I have updated the module to fix that problem specifically. Should appear in the dev soon.

I was getting all the results. You sure there isn't an unrelated problem or setting causing that? I would have to be able to reproduce your problem to solve it.

PS, turn finder's caching to 0 in case it is intefering while you're changing the settings around and stuff.

(There are indeed problems with finder's pager, there is another issue about it, for example if you're selecting multiple fields per element.)