How to get all rows with group by with codeigniter

Question

I have a table to manage the votes for a list of videos and I need to get how much votes the single entry have.

This is my table:

id | user_id | entry_id | vote

And this an example of entries:

1  |    1  |    729  |  3  
2  |    2  |    729  |  4  
3  |    3  |    729  |  4  
4  |    4  |    729  |  1  
5  |    5  |    729  |  4

I need to get how much users vote 1, 3 and 4 and the total users that voted that entry.

In this case I shoud get:

Users that voted 1: 1  
Users that voted 3: 1  
Users that voted 4: 3  
Total user: 5

In this case I can create the percent with php.

This is what I did with active records:

$this->db->select('COUNT(*) as tot_users, vote', FALSE);
$this->db->from('voti_video');
$this->db->where('entry_id', $id_entry);
$this->db->group_by('vote');

With this code I get exactly what users voted and how many of them voted.
How can I now get the total numbers of users without make a new query?
Is it possibile?

score 1 · Answer 1 · answered Jun 23 '17 at 17:08

I used this code .

$this->db->select( 'NAME' );
$this->db->select_sum( 'COUNTER' );
$this->db->from( 'TABLE' );
$this->db->order_by( 'NAME', 'ASC');
$this->db->group_by( 'NAME' );
$this->db->limit( 5 );
$result = $this->db->get();
return ( $result->num_rows() > 0 ? $result->result_array() : false );

score 0 · Answer 2 · answered Jan 04 '14 at 10:09

You need to have a subquery in your select list giving you total rows

$this->db->select('count(*)'); 
$this->db->from('voti_video');

// Render the subquery to a string 
$subQuery = $this->db->compileselect();

// Reset active record 
$this->db->resetselect();

// Generate the main query as posted by you and include the subquery 

$this->db->select('COUNT(*) as tot_users, vote', FALSE);
$this->db->select("($subQuery) as TotalVotes");
$this->db->from('voti_video');
$this->db->where('entry_id', $id_entry);
$this->db->group_by('vote');

I get a Fatal error on `compileselect`, seems that it has been removed from the core — Christian Giupponi, Jan 04 '14 at 10:41

score 0 · Answer 3 · edited May 23 '17 at 12:11

0

Please try something simmilar to this

$query = $this->db->query("
            select count(*) total,
                sum(case when entry_id = '".$id_entry."' then 1 else 0 end) tot_users,
            from voti_video
            group by vote");

Idea ~~stolen~~ from here and here.

edited May 23 '17 at 12:11

Community

1
1

answered Jan 04 '14 at 11:21

Kyslik

8,217
5
54
87

Nope, there is an error in the query at the end of sum() you son't need the `,`. By the way with your code, on the above example I get this: http://img202.imageshack.us/img202/5234/emhc.png that is the same output of mine but I haven't the total count of users – Christian Giupponi Jan 04 '14 at 12:52

How to get all rows with group by with codeigniter

3 Answers3