How to get all the content types that don't have nodes?

Question

In Drupal 7 if you want a list with the content types that don't have nodes you can do it with the following query:

SELECT type, name, COUNT(nid) as total
              FROM node_type LEFT JOIN node USING(type)
              GROUP BY type
              HAVING total = 0
              ORDER BY name

But in Drupal 8 we don't have the {node_type} table and you can't have access to all the available content types unless you use the 'entity.manager' service or you go to look inside the {config} table that have a column named name, but in the name column you will find this:

node.type.CONTENT_TYPE_MACHINE_NAME1
node.type.CONTENT_TYPE_MACHINE_NAME2

There is not an easy way to get the information from this table.

I found an example in the Entity Query support for aggregated queries page:

Let's assume you want the amount of nodes per node type.

$query = Drupal::entityQueryAggregate('node');

$result = $query
  ->groupBy('type')
  ->aggregate('nid', 'COUNT')
  ->execute();

But only works for content types with more than 1 node, why? Again there is not way to find content types in the database that don't have nodes.

I really don't know the reason for this missing important part (and I can't find it) but I think that this is very important because the only way now to obtain the content type label is through the 'entity.manager' service.

In short, how to get all the content types that don't have nodes?

Answer 1

I know that you can use the 'entity.manager' service to get all the available content types and then through a loop look for all the nodes for each content types. But this way is very long and involve many database queries.

This is the short way that I found to obtain this information:

// Getting the database connection.
$connection = \Drupal::service('database');
$query_content_types = "SELECT DISTINCT SUBSTR(name, 11) as type FROM config WHERE name LIKE 'node.type.%'";
// Creating a temporary table.
$content_types_temporary_table_name = $connection->queryTemporary($query_content_types);

$query = "SELECT node_type.type, COUNT(nid) AS total
          FROM $content_types_temporary_table_name node_type LEFT JOIN {node} USING(type)
          GROUP BY type
          HAVING COUNT(nid) = 0
          ORDER BY type ASC";

$result = $connection->query($query);
dpm($result->fetchCol('type'));