There is a bunch of information about how to do table joins in the Advanced Help for Views 7, topic "api-tables.html", which is not in hook_views_data() for Drupal 8.

This is fairly essential information and needs to be added.

At least, I needed it today to work on #2218065: Need to join fields to the entity field data tables, not entity tables, or filtering increases number of results, so I think it's essential today. :)

I'll make a patch...

Support from Acquia helps fund testing for Drupal Acquia logo

Comments

jhodgdon’s picture

Some notes:
- in that other issue we're adding the ability to use 'left_field' instead of 'value' in "extra" in joins
- I do not think %alias works in "extra" in joins as documented in the Advanced help. Use 'left_field' instead.

jhodgdon’s picture

Status: Active » Needs review
FileSize
1.96 KB

Here's a patch. It is not actually true until #2218065: Need to join fields to the entity field data tables, not entity tables, or filtering increases number of results and its change adding the 'left_field' syntax to 'extra' gets in. I guess we could roll it into that other patch, but even if that never gets in we should I think document the rest.

Status: Needs review » Needs work

The last submitted patch, 2: 2321995.patch, failed testing.

Status: Needs work » Needs review

mgifford queued 2: 2321995.patch for re-testing.

Status: Needs review » Needs work

The last submitted patch, 2: 2321995.patch, failed testing.

jhodgdon’s picture

Status: Needs work » Needs review
FileSize
2.8 KB

Here's a new patch. Since no one reviewed this yet, I did not bother with an interdiff. I hope that's OK.

jhodgdon queued 6: 2321995-v2.patch for re-testing.

jhodgdon queued 6: 2321995-v2.patch for re-testing.

jhodgdon queued 6: 2321995-v2.patch for re-testing.

jhodgdon’s picture

Amazingly enough, this patch still apparently applies. Would be nice to get a review sometime...

dawehner’s picture

Looks pretty solid for me.

+++ b/core/modules/views/views.api.php
@@ -185,6 +188,28 @@ function hook_views_data() {
+    // 'extra' is an array of extra WHERE conditions for the join.

Should we replace the 'extra' here with 'additional'?

jhodgdon’s picture

FileSize
3.18 KB

Sure. Fixed this in two places.

Uck. Due to patch fuzz, interdiff didn't work. Anyway I only changed two comments to say:

    // 'extra' is an array of additional conditions on the join.

and everything else is the same.

dawehner’s picture

+++ b/core/modules/views/views.api.php
@@ -221,6 +224,28 @@ function hook_views_data() {
+  // You can also do a more complex join. For instance, since this table
+  // has a language code field, you might want to join to node-based views
+  // via the node_field_data table, rather than the base node table, and you
+  // might want to join on both the nid field and the langcode field. To do
+  // this:
+  $data['example_table']['table']['join']['node'] = array(
+    // 'node' above is the base we're joining to in Views. 'left_table'
+    // is the table we're actually joining to, in order to get to 'node'. It
+    // has to be something that Views knows how to join to 'node'.
+    'left_table' => 'node_field_data',
+    'left_field' => 'nid',
+    'field' => 'nid',
+    // 'extra' is an array of additional conditions on the join.
+    'extra' => array(
+      // This syntax matches additional fields in the two tables.
+      array('left_field' => 'langcode', 'field' => 'langcode'),
+      // This syntax adds a condition on our table. 'operator' defaults to
+      // '=' for non-array values, or 'IN' for array values.
+      array('field' => 'numeric_field', 'value' => 0, 'numeric' => TRUE, 'operator' => '>'),
+    ),
+  );

+1 for the general part. What about adding an example how the resulting SQL query would look like then?

jhodgdon’s picture

FileSize
8.27 KB
7.49 KB

OK. The previous join stuff didn't have an example of the SQL, so I added that to both parts of the docs.

And... well this example doesn't work any more, because the base table for joining to nodes is now node_field_data, not node. So I made up a fictitious example instead.

And I noticed the comments on the first join information were saying 'node' but the join was to 'node_field_data' ... wasn't fully updated.

So there are a few diffs.

dawehner’s picture

Status: Needs review » Reviewed & tested by the community

And... well this example doesn't work any more, because the base table for joining to nodes is now node_field_data, not node. So I made up a fictitious example instead.

Great observation!

IMHO this is a good step forward, so let's get it in.

catch’s picture

Status: Reviewed & tested by the community » Fixed

Committed/pushed to 8.1.x and cherry-picked to 8.0.x. Thanks!

  • catch committed a005807 on 8.1.x
    Issue #2321995 by jhodgdon: More info needed in hook_views_data docs/...

  • catch committed 05b63b4 on 8.0.x
    Issue #2321995 by jhodgdon: More info needed in hook_views_data docs/...

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.