How to query external database for Count

Question

How do I query the below SQL query to an external database table from Drupal 8 custom module,

Select Count(requestID) FROM requestDB.request where request.dateCreated >= DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY) and ek_borrowerID = 111;

I have configured the settings.php already. Below is the query I tried to build, but it does not work

Database::setActiveConnection('requestDB');
$db = Database::getConnection();
$db->select('request')
    ->fields('request', array('Count(requestID)'))
    ->condition('dateCreated','DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)', '>=')
    ->condition('ek_borrowerID','111')
    ->execute();
Database::setActiveConnection();

Answer 1

You're not selecting the default database for the new connection.

Try:

$select = Database::getConnection('default', 'requestDB')->select('request');
$select->addExpression('Count(requestID)', 'rec_count');
$select->condition('dateCreated','DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)', '>=');
$select->condition('ek_borrowerID','111');
$select->execute();

With this method you dont need to switch back to your original database as this just creates a temporary connection.

Also you need to use addExpression for your count as mentioned by Charles Bamford below

Answer 2

You need to use ->addExpression() instead of ->fields()