1

I am using the following code to sort the published nodes basing on the value stored from the Statistics module.

$query = \Drupal::entityQuery('node')
      ->condition('status', 1)
      ->range(0, 5)
      ->sort('totalcount', 'DESC' );

$result = $query->execute();

The code is causing the following exception.

Drupal\Core\Entity\Query\QueryException: 'totalcount' not found in Drupal\Core\Entity\Query\Sql\Tables->ensureEntityTable() (line 316 of core/lib/Drupal/Core/Entity/Query/Sql/Tables.php).

How do I sort nodes basing on the totalcount value stored from the Statistics module?

2 Answers 2

3

The code you are showing doesn't work because totalcount is not a Node property the node entity. That is a value saved in a different database table, not used from the Node entity. See statistics_get(), which retrieves the value you are looking for.

if ($nid > 0) {
  // Retrieve an array with both totalcount and daycount.
  return db_query('SELECT totalcount, daycount, timestamp FROM {node_counter} WHERE nid = :nid', array(':nid' => $nid), array('target' => 'replica'))->fetchAssoc();
}

Since the entity query doesn't allow to join tables, you need to:

  • Add a tag to the entity query you are using
  • Implement hook_query_TAG_alter() to alter the queries with the tag you added (Replace TAG with the tag you added to the entity query)

node_query_node_access_alter() is the implementation of that hook done from the Node module to allow an entity query to return just the nodes the currently logged-in user has access. You can use its code as guide to write your own implementation.

For example, I would start trying with the following code.

The code for the query

$query = \Drupal::entityQuery('node')
  ->addTag('statistics_totalcount')
  ->condition('status', 1)
  ->range(0, 5)
  ->sort('totalcount', 'DESC');

$result = $query->execute();

The hook implementation

function mymodule_query_stastics_totalcount_alter(Drupal\Core\Database\Query\AlterableInterface $query) {
  $query->join('node_counter', 'nc', '%alias.nid = n.nid');
}
0

In case this helps anyone else, for me, I had to move the sort clause into the hook_query_TAG_alter() function because of a validation issue on the entity query object.

$query->leftJoin('node_counter', 'nc', '%alias.nid = node_field_data.nid');
$query->orderBy('totalcount', 'DESC');

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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