Eloquent: Query the length of field in Laravel

Question

I want to do something like that in Laravel (valid sqlite query):

select * from 'tbUsers' where  length(name)>50;

I tried

User::with('Permissons')->where('LENGTH(name)','>','50')->get();

But it seems not to work........

note: other queries works without problem:

User::with('Permissons')->where('active','=','1')->get();

Solution 1

Try this whereRaw( string $sql, array $bindings = array(), string $boolean = 'and')

User::with('Permissons')->whereRaw('LENGTH(name) > 50')->get();

Solution 2

Use whereRaw:

User::with('Permissons')->whereRaw('LENGTH(name) > ?', [50])->get();

Solution 3

In your test you tried 'LENGTH(name)', but this converts to a string which means the select statement becomes more like:

select * from 'tbUsers' where 'LENGTH(name)' > 50;

Which is bad. In order to stop treating it as a string you need to let where() know what you're entering is raw-sql, to do that you can use DB::raw:

User::with('Permissons')->where(DB::raw('LENGTH(name)'),'>','50')->get();

Comments

[JLM]

I don't understand the reason but your solution doesn't work for me. I'm using the solution provided by @vijaykumar.

[iateadonut]

I think this one, with bound variables, is more correct.

[Admin]

@iateadonut it is more secure if you allow user input. With a literal, hard-coded value they are equally correct.