1

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?

2
+50

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

  • Hm, when I implement the hook_query_TAG_alter I get a 502 Bad Gateway error on the views page, any clues about that? In any case, using a query tag and the example for showing the query are useful steps, but I'm afraid they don't address the core of my question, which is indeed more SQL related - and I agree that's the hard part :S – arjan Jan 22 '14 at 20:39
  • In this case I'm wondering what the SQL should look like in the first place, or if the table structure needs to be different to make this possible. Given these example content types and fields, surely the SQL would be the same for everyone? The problem I see is that field_language_target_id is in a separate table, so to use it in an ON clause, that table needs to be joined already or you get 'unknown column' error. But if we want to join field_data_field_language, we need to first join the word nodes to the concept nodes, since language is referenced from word nodes...? – arjan Jan 22 '14 at 20:43
  • Re adding field_language to the view: this adds just 'node' AS field_data_field_language_node_entity_type to the SELECT statement. It seems Views uses a secondary query to get actual field values in the results. – arjan Jan 22 '14 at 20:46
  • right, Views does additional PHP based processing for simple values of entities like node.title and other simple fields. Views doesnt make a raw SQL query you might write in phpmyadmin to retrieve the same information. Your goal is simply to take a Views query that is somewhat lacking in JOIN or fields and use either a Tag or api.drupal.org/api/views/views.api.php/function/… to change the db_select to what you want. It's not simple persay; but it's doable. – tenken Jan 22 '14 at 20:56
0

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.

Not the answer you're looking for? Browse other questions tagged or ask your own question.