CODEWITHSUNDEEP
sqlcodeigniter
Christian Giupponi
Christian Giupponi

Reputation: 7618

How to get all rows with group by with codeigniter

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?

Upvotes: 0

Views: 11926

Answers (3)

André Santos
André Santos

Reputation: 11

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

Upvotes: 1

Kyslik
Kyslik

Reputation: 8385

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.

Upvotes: 0

Mudassir Hasan
Mudassir Hasan

Reputation: 28741

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

Upvotes: 0

Related Questions