MySQL count from two JOIN tables

[eluser]bapobap[/eluser]
Hi there,

I have three tables, users, votes, comments. The tables are fairly simple, for each vote or comment a user makes, an entry is made in the appropriate table. I'm trying to get a count for both of these for a user using this:

Code:

$business = $this->db
        
        ->select('u.username')

        ->select('COUNT(v.ident) AS votes', FALSE)
        ->select('COUNT(c.ident) AS comments', FALSE)

        ->join('votes v', 'v.ident = u.id AND v.active = 1', 'LEFT')
        ->join('comments c', 'c.ident = u.id AND c.active = 1', 'LEFT')

        ->where('u.id', 1)
        
        ->group_by('v.ident')
        ->group_by('c.ident')
        
        ->get('users u')
        ->row();

However I'm getting really weird counts back. If I comment out either one of the JOIN tables, I get the correct result but as soon as I bring in the other table JOIN I get a weird number. I'm not even sure what the number represents, sometimes it looks like it's adding something together and sometimes multiplying.

Is this just something you can't do in one query?

[eluser]davidbehler[/eluser]
try

Code:

$business = $this->db
        
        ->select('u.username')

        ->select('COUNT(v.ident) AS votes', FALSE)
        ->select('COUNT(c.ident) AS comments', FALSE)

        ->join('votes v', 'v.ident = u.id AND v.active = 1', 'LEFT OUTER')
        ->join('comments c', 'c.ident = u.id AND c.active = 1', 'LEFT OUTER')

        ->where('u.id', 1)
        
        ->group_by('u.username')
        
        ->get('users u')
        ->row();

[eluser]bapobap[/eluser]
Thanks but nope, still getting the same result!

[eluser]TheFuzzy0ne[/eluser]
How about this:

Code:

$business = $this->db
      
->select('u.username')
->select('COUNT(v.ident) AS votes', FALSE)
->select('COUNT(c.ident) AS comments', FALSE)

->from('users u')

->join('votes v', 'v.ident = u.id', 'LEFT OUTER')
->join('comments c', 'c.ident = u.id', 'LEFT OUTER')

->where('u.id', 1)
->where('v.active', 1)
->where('c.active', 1)

->group_by('u.username')
        
->get()
->row();

If that doesn't work, then you might have to do it without the Active Record class.

[eluser]bapobap[/eluser]
Thanks, doesn't seem to be working for me, I've just reverted to separating the comments stuff into another query, works fine that way for some reason!

Thanks

[eluser]TheFuzzy0ne[/eluser]
One final thought. You can add $this->output->enable_profiler(FALSE); to your controller constructor/method, and so long as you don't do a redirect, you should see a list of database queries at the bottom of the page. See where the AR query differs from a man-made query, and see if you can work with that. It sounds a little bit dodgy, but I can't understand why it wouldn't work with AR.