ATLChris ATLChris - 14 days ago 5
MySQL Question

Third where statement in MySQL breaks query

In the statement below, you can see a commented outline. When I uncomment that line, the statement returns no results. If I leave the line commented I get the results shown in the image, which are correct.

select
`movies`.`id` as 'movie_id',
`movies`.`title` as 'movie_title',
`cnt_lists`.`id` as 'cnt_list_id',
`cnt_lists`.`list_id`,
`cnt_lists`.`listable_id`,
`cnt_lists`.`listable_type`
from `movies`
inner join `cnt_lists`
on `movies`.`id` = `cnt_lists`.`listable_id`
where `movies`.`has_poster` = '1'
and `cnt_lists`.`list_id` in ('3285')
# and `cnt_lists`.`listable_type` = 'App\Models\Movie'
order by `order` desc
limit 1;


results with line commented out

Can anyone shed some light on why the third where statement would result in no results? This is a query I am debugging from a laravel relationship.

Answer

it seems like an encoding problem. You may check if NO_BACKSLASH_ESCAPES is on, if it's OFF (and this is very likely), then you need to escape "\". This means you need to write the and condition as and `cnt_lists`.`listable_type` = 'App\\Models\\Movie'`

Reference: http://dev.mysql.com/doc/refman/5.7/en/string-literals.html

Update: Just forgot to mention that, please also check if there's any white space around the "App\Models\Movie". If not sure, you could use like to avoid the uncertainty: `cnt_lists`.`listable_type` like '%App\Models\Movie%' or if escaping is needed: `cnt_lists`.`listable_type` like '%App\\Models\\Movie%'

Comments