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();
Improve this question
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. – Neograph734 Jun 17 '19 at 19:03
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

Improve this answer
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? – Akhil Mohandas Jun 19 '19 at 19:12
  • 1
    i had to use '<=' and it worked! cheers – Akhil Mohandas Jun 19 '19 at 19:17
1

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

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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