How to use SQL raw query in Laravel

Question

SQL table:

SELECT id,
       account_name,
       parent_id
FROM
  (SELECT id,
          account_name,
          parent_id,
          CASE
              WHEN id = 1 THEN @idlist := CONCAT(id)
              WHEN FIND_IN_SET(parent_id, @idlist) THEN @idlist := CONCAT(@idlist, ',', id)
          END AS checkId
   FROM chart_of_account
   ORDER BY id ASC) AS T
WHERE checkId IS NOT NULL

When I run this query in MySQL it works fine and the result is fetched perfectly, but when I run it in Laravel like this:

$accountId = DB::select('SELECT id,account_name,parent_id FROM
                                    (SELECT id,account_name,parent_id,
                                        CASE WHEN id = '.$account_id.' THEN @idlist := CONCAT(id)
                                                 WHEN FIND_IN_SET(parent_id,@idlist) THEN @idlist := CONCAT(@idlist,', ',id)
                                            END as checkId
                                     FROM chart_of_account
                                     ORDER BY id ASC) as T
                                WHERE checkId IS NOT NULL');

it gives an error.

Argument 1 passed to Illuminate\\Database\\Connection::prepareBindings() must be of the type array, string given,

If there be any chance of your query working in Laravel, you would probably have to execute a raw query. And even then, I'm not sure that session variables would work. — Tim Biegeleisen
– Tim Biegeleisen, Commented Feb 18, 2019 at 11:14

Abhay Maurya · Accepted Answer · 2019-02-18 14:05:29Z

2

Try this:

$query = 'YOUR_QUERY_THE_BIG_ONE:)';
$result = DB::select($query,[]);
dd($result);

Optionally, you can use ? sign in your query wherever you are using user inputs to prevent mySQL injection issue and then provide their value in second parameter array of select function. One example would be:

$inputUserEmail = $request->input('email');
$query = 'SELECT * FROM users WHERE email=?';
$result = DB::select($query,[$inputUserEmail]);
dd($result);

I hope it gives you an idea

answered Feb 18, 2019 at 14:05

Abhay Maurya

12.3k9 gold badges50 silver badges67 bronze badges

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

1 Comment

Noman shah Over a year ago

its run perfectly tanx alot sir

Collectives™ on Stack Overflow

How to use SQL raw query in Laravel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related