I like problem solving. My good friend Oliver knows this and recently came to me with a Drupal problem that involved nodes related to other nodes and presenting them to the user in the correct order. For the Food Republic, he had galleries which contained story nodes which you could go through via a pager (showing the photo content one item at a time - you can see what I mean by clicking here). To illustrate the issue, below is a rough table diagram of how the structure looks:

Naturally, this is not an accurate representation of how Drupal stores this content - The photo and gallery tables are the same table (called 'node') with the other data being in 2 separate tables. There are certain issues that came up:

If we treat the galleries to be the primary data and make a relationship with the photo, the delta sorting works. However, if you use any sort of node access module (workflow, og, domain, etc), the query will be altered to run and find DISTINCT(nid) on the gallery. So it will only return one photo result.
If we treat the photos to be the primary data and make a relationship with the gallery, all the nodes show up. However, the delta sorting is not respected and if you try to use the delta, you would get 25 results based on the image above.

Between the two options, (1) was not possible to work around because this is something done within the node module in Drupal. And modifying Drupal out of a core behavior like this is a very bad idea. So (2) was the approach we went with. With (2) there were, again, 2 approaches we could take to the problem:

Use another mechanism to work with the weight of the photos as part of the gallery. Draggable Views works exceptionally well at this as it allows you to drag and reorder your content quite easily - I actually use this at CalArts. However, the content editor has to go into another screen to do the reordering (whereas setting the photos up in the gallery edit page, reordering the photos in there is much easier to do as it is already in front of the content editor).
Keep things the way they are and figure out what is happening in the query to be causing the amount of content squared resultset.
Keep things the way they are but write a custom query. The advantage would be that the joins and base filters would be exactly how we need it. The disadvantage would be that we are not able to use views for all the other goodies (adding additional filters, fields, etc) and frankly, its not a sustainable solution.

We decided we'll try to keep things the way they are and debug the query and see what is going on. Based on what I was seeing in the query, I realized that due to the kind of relationships and joins being made, the resultset was actually returning the photo along with a relationship to ALL the photos in the gallery. So if we were returning data for gallery 1, we were getting back:

PHOTO, GALLERY, PHOTO-REF, DELTA
Photo1, Gallery1, Photo2, 1
Photo1, Gallery1, Photo3, 2
Photo1, Gallery1, Photo1, 3
Photo1, Gallery1, Photo6, 4
Photo1, Gallery1, Photo4, 5
Photo2, Gallery1, Photo2, 1
Photo2, Gallery1, Photo3, 2
and so on...

Essentially, no relationship was being made with photo 1 as the primary data and photo 1 as part of the gallery which contained the delta info. So Photo1 gets linked to all the other photos in gallery 1 and so on. And within views, there is no way to link up the first column with the last column (or filtering the PHOTO-REF.nid to equal PHOTO.nid - there is an old issue in the Drupal Forums: Views Filter by Node ID which has not really been solved. Both an issue of views and *not* an issue of views, we needed to figure out a way to continue utilizing views while giving correct results. Since we were on a time crunch (making a more generic solution would involve more time - I can envision using tokens to figure out the value to filter against for the join), we needed to alter the query. Luckily, the Views module has a hook you can use precisely for this reason: hook_views_query_alter.

/**
 * Implementation of hook_views_query_alter().
 */
function food_gallery_views_query_alter(&$view, &$query) {
  if ($view->name == 'gallery_photos') {
    // Check that the field_ref_galleries field was added in
    if (isset($query->tables['node']['node_node_data_field_ref_galleries_node_data_field_ref_photos'])) {
      $query->where[0]['clauses'][] = $query->tables['node']['node_node_data_field_ref_galleries_node_data_field_ref_photos']['alias'] .'.field_ref_photos_nid = node.nid';
    }
  }
}

What code above does is check if we are dealing with the appropriate view and checks if the relationship between the galleries and its photo references was created. If it was, then add in a filter that ensure the the referencing photo nid is equal to the primary photo id (field_ref_photos_nid = node.nid). With that, our view now returns the correct result, the site administrator can continue using views with its goodies and the content editor can continue creating new galleries the way they have been.

BTMash

Blob of contradictions

Articles tagged with problem solving

Correcting views queries using views_query_alter

Written 2011-06-09