Views hook_views_data add multiple joins to custom table based on row condition

I’m using hook_views_data to add a custom table and its fields to Views. The table relates to nodes, and looks like this:

+ node id   | my_value   | time_period  + |-----------|------------|--------------| | 1         | 100        | 1 year       | | 1         | 200        | 1 month      | | 2         | 300        | 1 year       | | 2         | 400        | 1 month      | +---------------------------------------+ 

I have defined an implicit relationship to my custom table, so I have a view with nodes as the base table and I can access the my_value field from my custom table.

The time_period column is new, however. Now there are multiple rows per node, with different time_period values, and each of these results in a different output row in my view.

Rather than having a my_value field for each row, I would like to have my_value (1 year) and my_value (1 month) fields, so that each output row represents one node – i.e. rows for the same node are collapsed and their fields are separated.

I’ve cobbled together the SQL that achieves this, but I can’t figure out how to accomplish this with hook_views_data:

SELECT      node.title AS node_title,      node.nid AS nid,      custom_table_1month.my_value AS my_value_1month,     custom_table_1year.views AS my_value_1year FROM      node  LEFT JOIN     custom_table custom_table_1month ON custom_table_1month.node_id = node.nid AND custom_table_1month.time_period = '1 month' LEFT JOIN     custom_table custom_table_1year ON custom_table_1year.node_id = node.nid AND custom_table_1year.time_period = '1 year' 

I have tried to add two base tables to views, called custom_table_1month and custom_table_1year, but these value names are used verbatim as the table name whereas I really want them as aliases, so Views complains that these tables don’t exist, and I can’t seem to specify the correct table name anywhere.

I’m feeling like this has to be possible, because I’ve got the SQL for it! I just can’t for the life of me figure out how to do this with Views.

Sponsored by SupremePR
This article was republished from its original source.
Call Us: 1(800)730-2416

Pixeldust is a 20-year-old web development agency specializing in Drupal and WordPress and working with clients all over the country. With our best in class capabilities, we work with small businesses and fortune 500 companies alike. Give us a call at 1(800)730-2416 and let’s talk about your project.

FREE Drupal SEO Audit

Test your site below to see which issues need to be fixed. We will fix them and optimize your Drupal site 100% for Google and Bing. (Allow 30-60 seconds to gather data.)

Powered by

Views hook_views_data add multiple joins to custom table based on row condition

On-Site Drupal SEO Master Setup

We make sure your site is 100% optimized (and stays that way) for the best SEO results.

With Pixeldust On-site (or On-page) SEO we make changes to your site’s structure and performance to make it easier for search engines to see and understand your site’s content. Search engines use algorithms to rank sites by degrees of relevance. Our on-site optimization ensures your site is configured to provide information in a way that meets Google and Bing standards for optimal indexing.

This service includes:

  • Pathauto install and configuration for SEO-friendly URLs.
  • Meta Tags install and configuration with dynamic tokens for meta titles and descriptions for all content types.
  • Install and fix all issues on the SEO checklist module.
  • Install and configure XML sitemap module and submit sitemaps.
  • Install and configure Google Analytics Module.
  • Install and configure Yoast.
  • Install and configure the Advanced Aggregation module to improve performance by minifying and merging CSS and JS.
  • Install and configure Metatag.
  • Configure robots.txt.
  • Google Search Console setup snd configuration.
  • Find & Fix H1 tags.
  • Find and fix duplicate/missing meta descriptions.
  • Find and fix duplicate title tags.
  • Improve title, meta tags, and site descriptions.
  • Optimize images for better search engine optimization. Automate where possible.
  • Find and fix the missing alt and title tag for all images. Automate where possible.
  • The project takes 1 week to complete.