当我们使用Raw SQL时，如何在Laravel中使用like子句？

Question

I'm using Laravel raw sql query feature. And have to perform a like query. There are so many joins and checking in this code. So it's better to use raw SQL. Everything is working fine. But when I use the like , there's an error.

$Stars = DB::select('select v.videoid,s.seriesshortname, v.videotitle, v.VideoShortName, v.VideoImagepath, v.Views, v.Likes, v.Dislikes, v.Rating, v.videocategory, v.lastupdated,v.videocategory,v.seriesid,v.genreid,v.studioid from tblvideo v, tblpornstarvideo pv,tblseries s,tblpornstar p where v.videoid = pv.videoid and v.seriesid = s.seriesid and upper(v.Active) = \'Y\' and pv.psid = p.psid and pv.psid = :id and (v.site = 1 or v.site=3) and v.videotitle like \':letter%\' order by v.videotitle limit 6 offset :offset', ['id' => $id, 'offset' => $offset]);

Please check the like code. (v.videotitle like \':letter%\')

And please tell me how to make that working. I followed the documentation here. https://laravel.com/docs/5.1/database

Answer 1

Add the wildcards to your variable, not the query, and don't add the quotes. You're also not passing in the letter variable:

$Stars = DB::select("select v.videoid,s.seriesshortname, v.videotitle, v.VideoShortName, v.VideoImagepath, v.Views,
v.Likes, v.Dislikes, v.Rating, v.videocategory, v.lastupdated,v.videocategory,v.seriesid,v.genreid,v.studioid
from tblvideo v, tblpornstarvideo pv,tblseries s,tblpornstar p
where v.videoid = pv.videoid and v.seriesid = s.seriesid and
upper(v.Active) = 'Y' and pv.psid = p.psid and pv.psid = :id and (v.site = 1 or v.site=3)
and v.videotitle like :letter order by v.videotitle limit 6 offset :offset", ['id' => $id, 'letter'=> $letter.'%', 'offset' => $offset]);