How to filter a view on a property of optional entityreference-related content (table join / views relationship)?

Question

Consider this example: we have three content types: Concept, Language, and Word.

The Word content type has two entityreference fields:

• field_concept references concept nodes, and
• field_language references a language node.

The view shoud list all existing concepts, with related words (referencing the concept via entityreference) in a second column. The words are optional; we always want to show all concept nodes. We can accomplish this by creating a non-required Views relationship based on the field_concept entityreference (Entity Reference: Referencing entity). (This results in a LEFT JOIN in SQL).

The problem is how to filter words on the field_language value, without filtering out any of the concepts. Consider the following data.

Concept | Word   | Language
---------------------------
CAT
DOG     | Hund   | German
DOG     | chien  | French
FIRE    | fuego  | Spanish
WATER   | Wasser | German

Say we want related words (if any) filtered on a particular language. The desired views result for German is this:

CAT
DOG     | Hund
FIRE
WATER   | Wasser

We cannot use regular (grouped) filters with a condition like field_language = German OR field_language = NULL: while this would show CAT, DOG and WATER, it would remove "FIRE" from the results, since there is a Spanish result for FIRE.

So it seems we need to have a condition on the JOIN itself, preventing any words other than German ones to be joined. This question somewhat addresses how this should work, but: how would I access the field_language values? This is the basic query created by Views to show the concept and word titles - I'm not seeing how I should modify this:

SELECT node.title AS node_title, node.nid AS nid, field_concept_node.title AS field_concept_node_title
FROM 
{node} node
LEFT JOIN {field_data_field_concept} field_data_field_concept ON node.nid = field_data_field_concept.field_concept_target_id AND (field_data_field_concept.entity_type = 'node' AND field_data_field_concept.deleted = '0')
LEFT JOIN {node} field_concept_node ON field_data_field_concept.entity_id = field_concept_node.nid
WHERE (( (node.status = '1') AND (node.type IN  ('concept')) ))

It seems we cannot use the field_data_field_language.field_language_target_id value as a condition on the first LEFT JOIN without having another JOIN first, which would defeat the purpose - or am I missing something?

Can this be achieved with Views? Or are there alternative ways of doing this?

Answer 1

It seems we cannot use the field_data_field_language.field_language_target_id value as a condition on the first LEFT JOIN without having another JOIN first, which would defeat the purpose - or am I missing something?

Why would adding the column defeat the purpose? Add field_data_field_language.field_language_target_id from your Word and exclude it from the Views display.

Then in your view under Advanced Options add a tag to the query such as concept_word_lang_matrix_view

Then in a custom module implement hook_query_alter() to add your custom join (but not muck with the View fields or anything directly persay -- your Join is just refining results fetched by your View configured in the Views Admin UI)

A sample implementation of hook_query_TAG_alter looks like:

/**
 * Implements hook_query_TAG_alter for the Submission Is Open authentication
 * check of my business logic.
 */
function MYMODULE_query_SubmissionIsOpen_alter(QueryAlterableInterface $query) {
  #dpm(_MYMODULE_get_raw_sql_string($query));
  #dpq($query);
}

/**
 * This is a helper function to output the raw SQL from a db_select() type
 * query.
 *
 * Found via:
 * http://drupal.stackexchange.com/questions/51333/print-the-query-which-is-built-using-db-select
 */
function _MYMODULE_get_raw_sql_string(SelectQueryInterface $query) {
  $string = (string) $query;
  $arguments = $query->arguments();

  if (!empty($arguments) && is_array($arguments)) {
    foreach ($arguments as $placeholder => &$value) {
      if (is_string($value)) {
        $value = "'$value'";
      }
    }
    $string = strtr($string, $arguments);
  }
  return $string;
}

dpm() and dqp() are from the Devel module, they let you preview the SQL running from Views from within your module (where you're tweaking it).

Without having your server/DB I cannot just give you the SQL (nobody can do your homework for you). Writting the correct SQL is the hard part; altering the Views SQL is somewhat tricky but outlined here. goodluck.

EDIT you may also find this documentation helpful: https://drupal.org/node/1705998

Answer 2

In the end I just had to turn my query around. While starting with a listing of concepts makes sense conceptually, this would require a subquery in order to join only words in a particular language to those concepts.

Instead, we can list all items in a particular language, and then RIGHT JOIN all concepts. The Views UI doesn't support RIGHT JOINs, but we can use hook_views_query_alter to change the query to use a RIGHT JOIN, as pointed out here.