ActiveRecord Return Row id

[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

[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?

[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).

[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 DESC

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

[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.

[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.