1

I need to alter the query below to only return one row for each unique 'name'. The row returned should be the most recent based on the contents of 'date'. 'date' is an integer.

$query = db_select('updates_summary', 'n')
->fields('n', array('upid', 'name', 'date','previous_version','new_version'))
->orderBy('date', 'DESC') //Most recently updated first.
->execute(); 
return $query;

I have looked at the Drupal page on Expressions, another Drupal answers question and questions on drupal.org like this and this. Unfortunately I am new to the db-api and am struggling to get anywhere.

This works to return a single date but nothing else in the other columns:

  $query = db_query("SELECT MAX(upid) as max_id FROM updates_summary")->fetchCol();
  return $query;  
}

NEW ADDITIONS:

This is the mySQL query I need an equivalent of:

SELECT `name`, MAX(date) FROM `updates_summary` GROUP BY `name`

Adding ->groupBy('name') to my query seems to do what I need it to. However, i am confused as to why this seems to be working without specifying that I want to return the maxium from the date column. My current code that seems to work is:

  $query = db_select('updates_summary', 'n')
->fields('n', array('upid', 'name', 'date','previous_version','new_version'))
->groupBy('name')
->execute(); 

return $query;

I would like to add a MAX(date) as I did in the mySQL statement above. This answer suggests that adding the following should work: ->addExpression('MAX(date)', 'max_date') but i get Fatal error: Call to a member function fields() on a non-object.

0

You can use range() to limit your results

$query = db_select('updates_summary', 'n')
->fields('n', array('upid', 'name', 'date','previous_version','new_version'))
->orderBy('date', 'DESC')->range(0,1);
$query = $query->execute(); 
return $query;
  • Using range like this only returns one row. Instead I need to return a a row the most recent row for each name. – Paul Trotter Jul 10 '15 at 9:11
  • The range seem to be applied before the order by, so it returns only the first result the query finds, rather than gathering all the results, ordering them and then only returning the first – Paul Trotter Jul 13 '15 at 9:54
0

The solution I found is to loop through each unique name, then specify which name I am looking for in my query using a condition: ->condition('name', $name,'='). i also use a ->orderBy('date', 'DESC') to get all the fields ordered by date.

Aftwerwards perform a fetch. Fetch only returns the first result, and because the results are ordered in the query it returns the correct row:

$query = db_select('updates_summary', 'n')
      ->fields('n', array('date','name','previous_version','new_version'))
      ->condition('name', $name,'=')
      ->orderBy('date', 'DESC') 
      ->execute();

if ($query->rowCount() > 0) {

      // results are returned

      $myrecord = $query->fetch();  // just fetches the first row, which is why we order the $query by date 

      // now do anything you want with the returned result. For now just dpm it (needs devel module installed)
      dpm ('FETCH RESULTS: ' . $myrecord->name . ' | new version: ' . $myrecord->new_version);

    }

I've got a feeling that there might be a more elegant solution, but since this works fine I thought I would post it in case it helps someone else.

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.