Working with a database in Drupal 7?

Question

PHP

Hello.
You need to redo the database query to display tags according to the dictionary with the number of nodes on Drupal 7, here is the query itself

db_query("SELECT tid, name, (SELECT COUNT(*) FROM {taxonomy_index} ti LEFT JOIN {node} n ON ti.nid = n.nid WHERE ti.tid = td.tid AND n.status = 1) node_count FROM {taxonomy_term_data} td WHERE vid = :vid ORDER BY weight", array(':vid' => 3));

needs to be converted to Drupal requests. For those who don't understand, here's an example:

$query = db_select('node', 'n');
$query->innerJoin('users', 'u', 'n.uid = u.uid');
$query->fields('n', array('title'));
$query->fields('u', array('name'));
$nodes = $query->execute()->fetchAll();

Answer 1

$query = db_select('taxonomy_term_data', 'td');
$query->fields('td', array('tid', 'name'));
$query->condition('td.vid', 3);
$query->addExpression("(SELECT COUNT(*) FROM {taxonomy_index} ti LEFT JOIN {node} n ON ti.nid = n.nid WHERE ti.tid = td.tid AND n.status = 1)", 'node_count');
$terms = $query->execute()->fetchAll();