0

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();
1
  • 1
    Did you already check the documentation on count queries? I am pretty sure the fields-section should match with actual database fields and you cannot use expressions in them. Commented Jun 17, 2019 at 19:03

2 Answers 2

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

2
  • I am able to get the count using the addExpression but the dateCreated condition does not return any records. Do you know how I can fetch the last 30 days records? Commented Jun 19, 2019 at 19:12
  • 1
    i had to use '<=' and it worked! cheers Commented Jun 19, 2019 at 19:17
1

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

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.