Is it possible to make this query using Query Builder, if not how can I make it safe?

Here is an example of the query just as a string that I pass to the query function. This works as I expected.

Code:

$query_string = "SELECT SUM(author = '$author' AND email = '$email' AND approved = 1) AS email_author_approved, " .
       "SUM(author = '$author' AND email = '$email' AND spam = 1) AS email_author_spam, " .
       "SUM(author = '$author' AND spam = 1) AS author_spam, " .
       "SUM(email = '$email' AND spam = 1) AS email_spam " .
       "FROM comments " .
       "WHERE author = '$author' OR email = '$email'";
       
       $query = $this->db->query($query_string);
       $result = $query->result_array()[0];

I would like to use the Query  Builder class instead.  In the user guide I have seen the select_sum function, but no examples that uses multiple sums together after each other.  Can this be done? If not possible to use query builder class how can I make this safe.

I'll now explain what this code does in case it's unclear. It's basically my idea of a very simple spam filter.

It uses the sum function to count how many times this email/name combination has had comments approved and displays the results in a column called "email_author_approved.

It uses the sum function to count how many times this email/name combination has had comments marked as spam and displays results in a column called "email_author_spam".

Then does the same thing separately for the email and the author. If i understand correctly the information I pass to the sum function will result in either true or false which is the same as 1 or 0. So i can get a count. The where is to stop it counting all the rows.

Similar response with more query buider.

I would make this query like following:

PHP Code:

$author = $this->db->escape($author);
$email = $this->db->escape($email);

$select = "SELECT ".
          "SUM(author = '$author' AND email = '$email' AND approved = 1) AS email_author_approved, ".
          "SUM(author = '$author' AND email = '$email' AND spam = 1) AS email_author_spam, ".
          "SUM(author = '$author' AND spam = 1) AS author_spam, ".
          "SUM(email = '$email' AND spam = 1) AS email_spam";

$query = $this->db->select($select, false)
       ->from('comments')
       ->where('author', $author)
       ->or_where('email', $email);
       
       $result = $query->row(); 


Greetings.