CODEWITHSUNDEEP
phpmysqlcodeigniter
Edward
Edward

Reputation: 3081

Codeigniter 3.0 query bug

Duplicate this table: User_Posts

ID     | Upvotes | Downvotes | CAT  |
___________________________________
42134  |   5     |      3    | Blogs|
------------------------------------
12342  |   7     |      1    | Blogs|
-------------------------------------
19344  |   6     |      2    | Blogs|
------------------------------------

I need to get the rank of an item within it's category. Therefore ID: 19344 will have Rank position 2, with 4 upvotes, behind 12342 with 6 upvotes. Rank is determined by (upvotes-downvotes) count within it's category.

So I wrote this MySQL query.

SELECT rank FROM (SELECT *, @rownum:=@rownum + 1 AS rank
FROM User_Posts where CAT= 'Blogs' order by 
(Upvotes-Downvotes) DESC) d, 
(SELECT @rownum:=0) t2 WHERE POST_ID = '19344'

Returns to me (Rank = 2) when run directly in mysql. This is the correct result

However when I try to build it out through code-igniter's query builder I get the

$table = 'User_Posts'; 
$CAT= 'Blogs'; 
$POST_ID = '19344';

 $sql = "SELECT rank FROM (SELECT *, @rownum:=@rownum + 1 AS
 rank FROM $table where CAT= ? 
 order by (Upvotes-Downvotes) DESC) d, 
(SELECT @rownum:=0) t2 WHERE POST_ID= ?";

$query= $this->db->query($sql, array($CAT,$POST_ID))->row_array();

returns to me an empty result: array(rank=>);

so then my question is... but why?

I will also accept an answer will an alternative way to run this query from code-igniters query builder, but ideally I would like to know why this thing is broken.

Upvotes: 14

Views: 630

Answers (4)

Vinie
Vinie

Reputation: 2993

Exactly I don't know why your code is not working. I wrote another solution it will work. Try below code.

$select="FIND_IN_SET( (upvote-downvote), (SELECT GROUP_CONCAT( (upvote-downvote) ORDER BY (upvote-downvote) DESC ) as total FROM (User_Posts))) as rank";
$this->db->select($select,FALSE);
$this->db->from('(User_Posts)',FALSE);
$this->db->where('ID',19344);
$this->db->where('CAT','Blogs');
$query = $this->db->get();

Upvotes: 5

Arth
Arth

Reputation: 13110

I'm not entirely sure if this is the problem, but I'd be initialising @rownum in the subquery:

SELECT rank 
  FROM (
     SELECT *, 
            @rownum:=@rownum + 1 AS rank 
       FROM $table
       JOIN (SELECT @rownum := 0) init
      WHERE CAT= ? 
   ORDER BY (Upvotes-Downvotes) DESC
       ) d 
 WHERE post_id = ?

Otherwise I'd be worried that @rownum is undefined (NULL) and stays that way while rank is calculated (NULL + 1 = NULL), only being assigned the value of 0 afterwards. Thus rank is returned as NULL and you get ['rank'=>].

Running this again in a constant connection (directly in MySQL) would then give you the correct result as @rownum would start from the value 0 from the previous query and rank would be calculated correctly.

I'm guessing codeigniter starts a new connection/transaction each time the query is run and @rownum starts at NULL each time, giving ['rank'=>].

Upvotes: 1

Rick James
Rick James

Reputation: 142433

Write a Stored Function to do the query. Then have Codeigniter merely do

query("SELECT PostRank(?,?)", $CAT, $POST_ID);

Restriction: Since you cannot do PREPARE inside a Stored Function, this function will necessarily be specific to one table, User_Posts.

Upvotes: 2

ahmad
ahmad

Reputation: 2729

I've had a similar issue in the past, turns out I had to initialize the variable with a separate query first, I am not sure if this is still the case, but give it a try anyway.

//initialize the variable, before running the ranking query.
$this->db->query('SELECT 0 INTO @rownum');
$query= $this->db->query($sql, array($CAT,$POST_ID))->row_array();

Upvotes: 5

Related Questions