How to generate block with Views, Entity Reference and Taxonomy Terms

Question

I have a site with content types: book-page & ride.

These are organized so that each book-page displays several rides (using Entity Reference), and each ride is tagged with several taxonomy terms.

I'd like to create a block which displays links to other pages whose rides share a taxonomy term with any of the rides on the current page.

Looking at the table structure I can see that the following query will yield the desired results (for node id 9).

SELECT DISTINCT your_page.title
FROM node my_page
INNER JOIN field_data_field_rides my_page_rides ON my_page.nid = my_page_rides.entity_id
INNER JOIN node my_ride ON my_page_rides.field_rides_target_id = my_ride.nid
INNER JOIN taxonomy_index my_ride_terms ON my_ride.nid = my_ride_terms.nid
INNER JOIN taxonomy_term_data my_term ON my_ride_terms.tid = my_term.tid
LEFT JOIN taxonomy_index your_ride_terms ON my_term.tid = your_ride_terms.tid
INNER JOIN node your_ride ON your_ride.nid = your_ride_terms.nid
INNER JOIN field_data_field_rides your_page_rides ON your_page_rides.field_rides_target_id = your_ride.nid
INNER JOIN node your_page ON your_page.nid = your_page_rides.entity_id
WHERE my_term.vid = 2
  AND my_page.nid = 9
  AND (your_page.nid IS NOT NULL AND your_page.nid != my_page.nid)
  AND your_page.status = '1'
  AND your_page.type IN ('book')
  AND your_page_rides.entity_type = 'node'
  AND your_page_rides.deleted = '0'

My question is - can the same thing be done through the Views UI using a Relation?

I'm using D7 with Views 3.

Answer 1

Many thanks to @flocondetoile for responding, but I think the problem I posed was a join too far for Views. That is, whereas @flocondetoile needed to join:

parent-node -> child-node -> parent-node

I needed to join:

parent-node -> child-node -> taxonomy-term -> child-node -> parent-node

Try as I might I could find no way to do this directly in the Views UI. I did though find an elegant solution which I share here in case anyone else finds it useful.

Step 1

Save your query as a database view, i.e.,

CREATE VIEW view_name AS ...

Step 2

Describe your database view (or table) to the Views module. This is done by implementing hook_views_data (you can find the Views version in node.views.inc). It's a big method, but not too scary. In effect you just build up an array of meta-information about your database view. I based mine fairly closely on the code in node.views.inc. This step is described in some detail on this blog page, which also includes a worked example. Be sure to check out the slides linked at the bottom of the page.

Step 3

Views will now know about your custom database view/table. You can query it directly.

Answer 2

What are you use for you entity relation ? Is the module Relation or the module Entity Reference ? If it's Entity Reference, you should read this post http://drupal.org/node/1714188. It could help you