Call to a member function toSql() on integer

Question

I have a code:

$update= DB::table('Appraiser_commands')->where('cycle_id', $cycleid)->where('user_id',$userId)->update(['mode' => $mode,'Appraiser_commnd'=>$json])->toSql();
        echo $update;exit;

I am trying to convert laravel query to mysql using toSql()

But i got an error like

Call to a member function toSql() on integer

Then i tried

 DB::table('Appraiser_commands')->where('cycle_id', $cycleid)->where('user_id',$userId)->update(['mode' => $mode,'Appraiser_commnd'=>$json])
         DB::enableQueryLog();
        $queries = DB::getQueryLog();
        dd(end($queries));

But it returns output as 'false' i dint get the expected output.I don't know why this happened.Any help would be appreciated.

Expcted output:

UPDATE table_name
SET Appraiser_commnd=value, mode=value2,...
WHERE cycle_id=some_value

echo this and not update echo DB::table('Appraiser_commands')->where('cycle_id', $cycleid)->where('user_id',$userId)->update(['mode' => $mode,'Appraiser_commnd'=>$json])->toSql(); — Exprator, Jul 20 '17 at 07:13
@Exprator:thanks for the reply..but still the same issue..please help — Shanu k k, Jul 20 '17 at 07:15
Possible duplicate of [How do I get the query builder to output its raw SQL query as a string?](https://stackoverflow.com/questions/18236294/how-do-i-get-the-query-builder-to-output-its-raw-sql-query-as-a-string) — Mihai Matei, Jul 20 '17 at 07:16
@MateiMihai i refer this link..but i dint get the expected output — Shanu k k, Jul 20 '17 at 07:28
Possible duplicate of [How to get the raw sql for a laravel delete/update/insert statement?](https://stackoverflow.com/questions/45193727/how-to-get-the-raw-sql-for-a-laravel-delete-update-insert-statement) — apokryfos, Jul 20 '17 at 07:55

patricus · Accepted Answer · 2017-07-20T20:08:55.083

When you call update(), it executes the query and returns the number of affected rows (an integer). You're then calling toSql() on the integer. This is causing the error you are seeing.

If you're trying to see the SQL without running the query, you'll need to do this:

// start the query but don't execute it
$query = DB::table('Appraiser_commands')
    ->where('cycle_id', $cycleid)
    ->where('user_id', $userId);

// use the grammar object to get the update sql
$sql = $query
    ->getGrammar()
    ->compileUpdate($query, ['mode' => $mode, 'Appraiser_commnd' => $json]);

If you do want to execute the query, but also get the SQL, you can do this:

// start the query but don't execute it
$query = DB::table('Appraiser_commands')
    ->where('cycle_id', $cycleid)
    ->where('user_id', $userId);

// use the grammar object to get the update sql
$sql = $query
    ->getGrammar()
    ->compileUpdate($query, ['mode' => $mode, 'Appraiser_commnd' => $json]);

// actually run the update statement
$updated = $query
    ->update(['mode' => $mode, 'Appraiser_commnd' => $json]);

Additionally, you could view the sql in the query log, but you must enable the query log before executing the statement:

// enable the query log
DB::enableQueryLog();

// execute the update
DB::table('Appraiser_commands')
    ->where('cycle_id', $cycleid)
    ->where('user_id', $userId)
    ->update(['mode' => $mode, 'Appraiser_commnd' => $json]);

// view the query log
dd(DB::getQueryLog());

@JeffPuckett You're absolutely correct. I have updated the provided code to pass in the builder object. Thanks for pointing it out. — patricus, Jul 20 '17 at 20:10
what is mode => $mode. Please provide complete answer. Link to a documentation would also be helpful — Xitcod13, Apr 21 '22 at 19:36
@Xitcod13 That comes from the code in the original post. The answer is as complete as the original question. Additionally, none of this is documented, so a link to the documentation doesn't exist. — patricus, Apr 21 '22 at 21:11
no worries just the guidelines state that you should make the answer as useful for everybody as possible. So it would be slightly improved if you included the part of the code from the question that is being reused. Pity about the documentation stuff. — Xitcod13, Apr 22 '22 at 18:28

score 3 · Answer 2 · answered Jul 20 '17 at 07:18

3

Do the following:

a)At the start of the function :

DB::enableQueryLog();

b)After executing the query do this:

dd(DB::getQueryLog());

It will give the whole query

answered Jul 20 '17 at 07:18

Aditya Singh

704
2
12
20

Exprator · Answer 3 · 2017-07-20T08:11:05.430

1

    DB::enableQueryLog();    
$update= DB::table('Appraiser_commands')->where('cycle_id', $cycleid)->where('user_id',$userId)->update(['mode' => $mode,'Appraiser_commnd'=>$json])

    $queries = DB::getQueryLog();
    dd(end($queries)); // only last query

remove the toSql and try this

edited Jul 20 '17 at 08:11

answered Jul 20 '17 at 07:19

Exprator

26,992
6
47
59

:it returns false only – Shanu k k Jul 20 '17 at 07:30
that means there is an error with the query, try to find it – Exprator Jul 20 '17 at 07:31
@Exprator-I know there is an error.that's why i am tried to convert it to sql.so i can find error easy – Shanu k k Jul 20 '17 at 07:33
i dont know whether wrong query is printed or not, try without the end and check if you get all the queries – Exprator Jul 20 '17 at 07:35

linktoahref · Answer 4 · 2017-07-20T08:05:58.997

Return type of update is int and toSql() expects an Builder instance.

If using AND in where clause you could change the query to the following and try dumping its sql as

$update = DB::table('Appraiser_commands')
            ->where(
                ['cycle_id', '=', $cycleid],
                ['user_id', '=', $userId]
            )->update([
                'mode' => $mode,
                'Appraiser_commnd' => $json
            ]);

If using OR in where clause you could try as

$update = DB::table('Appraiser_commands')
            ->where('cycle_id', $cycleid)
            ->orWhere('user_id', $userId)
            )->update([
                'mode' => $mode,
                'Appraiser_commnd' => $json
            ]);

After executing the query

$queries = DB::getQueryLog();
dd($queries);

score 1 · Answer 5 · answered Jul 20 '17 at 08:05

You can not get the raw query for the update query in Laravel query builder and ->toSql() is only work for select query.
But if you want to get previous query which execute in laravel than follow this

How to get the query executed in Laravel 5 ? DB::getQueryLog returning empty array

Call to a member function toSql() on integer

5 Answers5