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.