+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: ActiveRecord Return Row id (/showthread.php?tid=30307)
ActiveRecord Return Row id - El Forum - 05-10-2010
[eluser]chrislorenz[/eluser]
Hi all!
First off I just want to say what a great framework and what an amazing community. This is the first time I have had to ask a question since my start with codeigniter due to the wealth of info on these forums and the userguide.
My question may be noob in nature so bare with me.
I currently am created a voting system where users can vote on individual submissions that people create.
I have created a function in my model that joins various tables and can give me how many votes an individual submission has, which works great. Now my issue is that I want to be able to reference that query but give me a "rank" of the submission by finding what row id is returned.
Below is the function in my model:
Code:
function get_submissions_rank($subid)
{
$this->db->select('*');
$this->db->select('COUNT(vote.vote_id) AS VoteCount');
$this->db->from('submission');
$this->db->join('vote','vote.submission_id = submission.submission_id', 'inner');
$this->db->group_by('vote.submission_id');
$this->db->order_by('VoteCount', 'DESC');
$this->db->where('submission.submission_id', $subid);
$query = $this->db->get();
return $query->result_array();
}Now I know that I can do a foreach loop and reiterate i++ to find the rank but it seems like it would be an inefficient way to do it. (especially since this query would be running a lot and the amount of records has the potential to be pretty large).
Does anyone know the best way to handle something like this? Let me know if I need to explain further.
Thanks in advance!
Chris
@chrislorenz
ActiveRecord Return Row id - El Forum - 05-11-2010
[eluser]danmontgomery[/eluser]
Hi Chris,
Which row id do you need? Do you just mean the row number in the query result? Or some sort of rank within each submission?
ActiveRecord Return Row id - El Forum - 05-12-2010
[eluser]chrislorenz[/eluser]
Sorry if I didn't explain it right.
Basically, if I ran that query above and got my total list of submission results(and DESC by how many votes each sub has). I would like to know where in that list sits a specific submission (by submission_id).
ActiveRecord Return Row id - El Forum - 05-12-2010
[eluser]chrislorenz[/eluser]
I am realizing that I don't think this can be done in Active Record.
To simplify does anyone know how to get "rank" based off of the vote count. SQL query below.
Code:
SELECT s.submission_id
, Count( v.submission_id ) AS VoteCount
FROM submission AS s
LEFT JOIN vote AS v
ON v.submission_id = s.submission_id
WHERE 1=1
GROUP BY s.submission_id
ORDER BY VoteCount DESCActiveRecord Return Row id - El Forum - 05-13-2010
[eluser]chrislorenz[/eluser]
Well I was able to figure out this query on my own. I don't think there is any solid way to do this in active record, but I have included the mysql statement below in case anyone is trying to figure this out in the future.
Code:
SET @rank := 0;
SELECT rank, VoteCount, submission_id FROM (
SELECT @rank:=@rank+1 AS rank, s.submission_id
, Count( v.submission_id ) AS VoteCount
FROM submission AS s
LEFT JOIN vote AS v
ON v.submission_id = s.submission_id
WHERE 1=1
GROUP BY s.submission_id
ORDER BY rank ASC
) AS t;ActiveRecord Return Row id - El Forum - 05-13-2010
[eluser]danmontgomery[/eluser]
You can't run multiple queries at once with PHP's mysql implementation... You can try fussing with mysqli, but the best/easiest way to do this is really just keep a counter when you loop through the results.
ActiveRecord Return Row id - El Forum - 05-13-2010
[eluser]chrislorenz[/eluser]
Yeah, I realized that once I started piecing everything together. I worked around it by doing this:
Code:
function get_submissions_rank()
{
$resetrank = 'SET @rank := 0';
$this->db->query($resetrank);
$query = 'SELECT rank, VoteCount, submission_id FROM (
SELECT @rank:=@rank+1 AS rank, s.submission_id
, Count( v.submission_id ) AS VoteCount
FROM submission AS s
LEFT JOIN vote AS v
ON v.submission_id = s.submission_id
WHERE 1=1
GROUP BY s.submission_id
ORDER BY rank ASC
) AS t';
$result = $this->db->query($query);
}This seems to work but after going through all of this I think I may just end up iterating with a counter.