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.