Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to select words start with a number in Laravel eloquent ORM?

I have this MYSQL query. I want to select all the words starting with a number. This is the working pure MYSQL query.

SELECT 'seriesid', 'seriesname', 'imagepath', 'views', 'scenes'
    FROM `series`
    WHERE `seriesname` regexp '^[0-9]+'

Now I want to implement this using Laravel. I made some codes. But not working. Here's my Laravel code for above query.

DB::table('series')->
    select('seriesid', 'seriesname', 'imagepath', 'views', 'scenes')->
    where('seriesname', 'regexp', '\'^[0-9]+\'')->
    get();

What's the error in ths code ? It always returns an empty array [].

like image 498
Chanaka De Silva Avatar asked Jun 30 '16 04:06

Chanaka De Silva


2 Answers

In your original MySQL query, you aren't looking for quotes in the column data; in your Laravel one, you are. Try removing them.

DB::table('series')->
    select('seriesid', 'seriesname', 'imagepath', 'views', 'scenes')->
    where('seriesname', 'regexp', '^[0-9]+')->
    get();
like image 170
miken32 Avatar answered Oct 25 '22 19:10

miken32


To use regex in laravel you have to use whereRaw(). So change this code

DB::table('series')->select('seriesid', 'seriesname', 'imagepath', 'views', 'scenes')->where('seriesname', 'regexp', '\'^[0-9]+\'')->get();

TO

DB::table('series')->select('seriesid', 'seriesname', 'imagepath', 'views', 'scenes')->whereRaw('seriesname regexp ?', array('\'^[0-9]+\''))->get();

If you are unable to generate the query you need via the fluent interface, feel free to use whereRaw https://laravel.com/docs/5.0/eloquent

like image 45
Amit Ray Avatar answered Oct 25 '22 20:10

Amit Ray