query CodeIgniter

Question

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);
}

How this can be done using active Records

Its not working :(

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 i.e., 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

`SELECT COUNT('user_id')` does not do what you think it does. You mean either `SELECT COUNT(DISTINCT user_id)` or `SELECT COUNT(*)` depending on what you are trying to do. — Mark Byers, Jul 27 '12 at 09:40
Codeigniter's AR doesn't support subqueries so you can't do it with AR without any trick. — uzsolt, Jul 27 '12 at 11:24

score 1 · Accepted Answer · answered Jul 27 '12 at 10:21

1

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

answered Jul 27 '12 at 10:21

bogtan

825
2
13
23

thanks, can you please help me to write it using Active records in codeigniter – avinashse Jul 27 '12 at 10:27
http://stackoverflow.com/questions/656622/codeigniter-how-to-do-a-select-distinct-fieldname-mysql-query see this question for more details but I think you should execute a pure mysql query. Codeigniter is a little bit lazy when it comes to parsing complex mysql queries. – bogtan Jul 27 '12 at 10:32

query CodeIgniter

1 Answers1