1

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.

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.

0

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

  • Thanks @flocondetoile. Looks useful. My mistake - I'm using 'Entity Reference' – neil Aug 21 '12 at 5:30

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.