1 Question: Laravel 4.2: Eloquent whereRaw returning 0 while running query(in string) working fine in mysql

Question created on Sunday February 26, 2017

Basically my desire task to get count of the row from my table with condition to serialized data column(ans). and get the attempted choice count of the each choice which stored in 'ans' col.

If answer type was radio then answered (under ans col when unserialize) will be int, if answered comma-separated string then it was checkbox otherwise its will be comment answer mean will be text.

Database table Schema(test11_answer):

+-------------+------------------+------+-----+---------------------+----------------+
| Field       | Type             | Null | Key | Default             | Extra          |
+-------------+------------------+------+-----+---------------------+----------------+
| id          | int(10) unsigned | NO   | PRI | NULL                | auto_increment |
| survey_id   | int(10) unsigned | NO   |     | NULL                |                |
| question_id | int(10) unsigned | NO   |     | NULL                |                |
| user_id     | int(10) unsigned | NO   |     | NULL                |                |
| ans         | text             | NO   |     | NULL                |                |
| created_at  | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
| updated_at  | timestamp        | NO   |     | 0000-00-00 00:00:00 |                |
+-------------+------------------+------+-----+---------------------+----------------+

Data in Table(test11_answer):
+----+-----------+-------------+---------+-------------------------------------------------------------------+---------------------+---------------------+
| id | survey_id | question_id | user_id | ans                                                               | created_at          | updated_at          |
+----+-----------+-------------+---------+-------------------------------------------------------------------+---------------------+---------------------+
|  1 |         1 |           1 |       1 | a:2:{s:8:"answered";s:1:"1";s:4:"type";s:5:"radio";}              | 2015-02-09 08:10:23 | 2015-02-09 08:10:23 |
|  2 |         1 |           2 |       1 | a:2:{s:8:"answered";s:3:"5,6";s:4:"type";s:3:"box";}              | 2015-02-09 08:10:23 | 2015-02-09 08:10:23 |
|  3 |         1 |           3 |       1 | a:2:{s:8:"answered";s:15:"kasdkfjasldkfj";s:4:"type";s:4:"text";} | 2015-02-09 08:10:23 | 2015-02-09 08:10:23 |
|  4 |         1 |           3 |       1 | a:2:{s:8:"answered";s:15:"kasdkfjasldkfj";s:4:"type";s:4:"text";} | 2015-02-09 08:10:23 | 2015-02-09 08:10:23 |
|  5 |         1 |           1 |       1 | a:2:{s:8:"answered";s:1:"1";s:4:"type";s:5:"radio";}              | 2015-02-09 08:10:23 | 2015-02-09 08:10:23 |
|  6 |         1 |           2 |       1 | a:2:{s:8:"answered";s:3:"5,6";s:4:"type";s:3:"box";}              | 2015-02-09 08:10:23 | 2015-02-09 08:10:23 |
+----+-----------+-------------+---------+-------------------------------------------------------------------+---------------------+---------------------+

so first, if i want to check count of choice (by choice id) and type is radio, the raw mysql query im using:

SELECT COUNT(*) AS total FROM `test11_answer` 
    WHERE `question_id` = 1 AND `survey_id` = 1   AND 
    TRIM(BOTH '\"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(`ans`,';',2),':',-1)) = 1 AND 
    TRIM(BOTH '\"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(`ans`,';',4),':',-1)) LIKE '%radio%';    

+-------+
| total |
+-------+
|     2 |
+-------+
1 row in set (0.00 sec)

But when this query convert to laravel eloquent model in my case:

Answer::where('question_id', '=', 1)
->where('survey_id', '=', 1)
->whereRaw('TRIM(BOTH \'"\' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(ans,\';\',2),\':\',-1)) IN (?)', array(1))
->whereRaw('TRIM(BOTH \'"\' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(ans,\';\',4),\':\',-1)) LIKE (\'%?%\')', array("radio"))
->count();

By the way Answer is the Eloquent Model, which would return (0). Eventually the generated query (in string) working fine in mysql but this functions return 0 always.

Update Generated Query object is:(according to laravel)

array (size=3)
      'query' => string 'select count(*) as aggregate from `test11_answer` where `question_id` = ? and `survey_id` = ? and TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(ans,';',2),':',-1)) IN (?) and TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(ans,';',4),':',-1)) LIKE ('%?%')' (length=263)
      'bindings' => 
        array (size=4)
          0 => string '1' (length=1)
          1 => string '1' (length=1)
          2 => string '1' (length=1)
          3 => string 'radio' (length=5)
      'time' => float 0.28

And the final query would be (to run into database)

select count(*) as aggregate from `test11_answer` 
    where `question_id` = 1 and `survey_id` = 1 and 
    TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(ans,';',2),':',-1)) IN (1) and 
    TRIM(BOTH '"' FROM SUBSTRING_INDEX(SUBSTRING_INDEX(ans,';',4),':',-1)) LIKE ('%radio%')

This generated query working properly and providing proper result as require in mysql.

In my though its look kind of bug in Method but little confuse its really or not.

Similarly I need to get count for 'text' and 'box' type

Let me know if its still unclear, I will try to explain/update my question further in detail.

3
1 answer                              3                         

The result of the eloquent query is a Collection on which the count() method is called and because your it seams to be empty the result is 0.

1
Tuesday February 17, 2015
source posted here