I am using Drupal 7 and Views 7.x-3.7 . I am trying to display posts on my site using a SQL query that looks like this:

SELECT DISTINCT node.title AS node_title, node.nid AS nid, node.language AS node_language


{node} node

ORDER BY node_title ASC

The problem with this query is that if I have multiple posts with the same title,then all are being displayed because the nid, which is the primary key, was added by Drupal. I have been trying to remove the nid by using the hook_views_query_substitutions() method, but I have had no success. The method is getting called, but my query is not being updated.

I call zz_views_api() from my zz.module, and have the following zz.views.inc

function zz_views_query_substitutions($view) {
  // description of  zz_views_query_substitutions().

  return array(
    'node.nid AS nid,' => ' ',

I think this is a similar situation to a question I asked a little while ago.

I solved it by using hook_views_query_alter() to remove the fields I didn't need in the query. In your case, it would probably look like:

function MYMOODULE_views_query_alter(&$view, &$query) {
  if ($view->name == 'MY_VIEW') {

dpm() the $query to see what array keys are in $query->fields if that doesn't work.

  • This plus enabling pure distinct works, thanks. Can anybody still explain why the query substitution does not work ? – zzz711 May 6 '14 at 15:19

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.