CodeIgniter active record query to count records in a related table for each unique userId

I have written an SQL query which I have to do using CodeIgniter.

My query is:

SELECT COUNT('user_id')
FROM tbl_tickets_replies
WHERE user_id IN (
    SELECT id
    from tbl_users
    WHERE username IN (
        SELECT username
        FROM tbl_tickets
        WHERE site_referers_id = 1
    )
)

I am doing this in my model

function getCommentNumbers() {
    $sql = "SELECT COUNT('user_id') FROM tbl_tickets_replies WHERE user_id IN (SELECT id from tbl_users WHERE username IN (SELECT username FROM tbl_tickets WHERE site_referers_id =1))";
    return $this->db->query($sql);
}

I have three different tables which are:

tbl_users(id,username);

tbl_tickets(id,username,site_referers_id)

tbl_tickets_replies(id,user_id,comments)

What I want to do is select all comments belonging to particular username having site_referers_id=1.

I thought to select distinct username from tbl_tickets having site_referes_id =1 and then get the id of selected username from tbl_users and use that id to count how many comments he have and display it according to the username.

My query is not doing so, it is displaying total comments of all users. For example, suppose there are two users A and B with users id 1 and 2 having 10 and 15 comments then it should display like :

A   10
B   15

rather my query is showing

A
B  25

How this can be done using active record syntax (using query builder methods)?

What you're missing is the GROUP BY aggregate function.

Try this:

SELECT DISTINCT user_id, COUNT('user_id') FROM tbl_tickets_replies WHERE user_id IN
    (SELECT id from tbl_users WHERE username IN 
        (SELECT username FROM tbl_tickets WHERE site_referers_id =1)) GROUP BY user_id