ATLChris ATLChris - 1 year ago 73
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.

`movies`.`id` as 'movie_id',
`movies`.`title` as 'movie_title',
`cnt_lists`.`id` as 'cnt_list_id',
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 Source

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'`


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%'

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download