Laravel 5.4 query builder and MySQL functions

Question

I'm building query using Laravel query builder and I'm having an issue with MySQL functions.

I have a Mysql functions that receives the user_id and some other paramenters to calculate a user's score. This query must return only users with score above a certain value. The query is giant, so I'm pasting only the part that is causing be trouble:

$query = $query->where(DB::raw($fstr.' >= 70'));

Generates this :

AND f_user_matching_score(4, admin__user.id_user, 1, 0, 0, 0, 0, ',') >= 70 IS NULL

This:

$query = $query->where($fstr,'>=',  70));

Generates this:

AND `f_user_matching_score(4, admin__user.id_user, 1, 0, 0, 0, 0, ',')` >= 70

Both are invalid. The first is adding this IS NULL and the second the back ticks.

How can I solve this?

@aynber Perfect, it worked. Thanks. Please post an answer so I can give you the points. — André Luiz
– André Luiz, Commented Jan 18, 2019 at 13:31

aynber · Accepted Answer · 2019-01-18 13:43:17Z

3

You need to use whereRaw() for fully raw where queries. This way it will not expect any other variables being passed in, though you can if need be.

$query->whereRaw($fstr.' >= 70');

If you needed to pass in variables for quoting, you'd just pass them in as an array:

$query->whereRaw("f_user_matching_score(?, admin__user.id_user, ?, ?, ?, 0, 0, ?) >= 70", [4, 1, 0, 0, ',']);

answered Jan 18, 2019 at 13:43

aynber

23.2k9 gold badges57 silver badges69 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Laravel 5.4 query builder and MySQL functions

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related