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

Question

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.

Can you debug the generated sql-statement and compare the result? http://stackoverflow.com/questions/14536165/get-the-query-executed-in-laravel-3-4 — dschniepp, Feb 17 '15 at 17:44
Please try to get the __real__ statement from the `DB::getQueryLog()` and you will see the difference. — dschniepp, Feb 17 '15 at 21:38
Thank you. Can you use the `->get()` method to return the objects from the database instead of counting the hits? — dschniepp, Feb 18 '15 at 18:53
Thank you to, I just need count of rows, not data from database — Safoor Safdar, Feb 18 '15 at 20:29
Let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/71206/discussion-between-dschniepp-and-safoor-safdar). — dschniepp, Feb 18 '15 at 20:48

dschniepp · Answer 1 · 2015-02-17T21:39:42.723

1

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.

edited Feb 17 '15 at 21:39

answered Feb 17 '15 at 21:34

dschniepp

1,083
12
19

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

1 Answers1