0

Hello Guys i have the code to get the invoices from my database. its work on localhost, But on online server it get me error.

the code is:

$users = User::whereHas('subscriptions', function ($q) {
                    $q->where([
                        'status' => 1
                    ]);
                })->with([
                    'subscriptions' => function ($q) {
                        $q->where([
                            'status' => 1
                        ]);
                    },'invoices' => function ($q) {
                        $q->withCount([
                            'payments as amount' => function ($q) {$q->select(DB::raw("CAST(SUM(amount) AS INTEGER)"));}
                        ]);
                }])->whereHas('invoices')->paginate(30);

it work perfectly on localhost. but on online server get me this error

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'INTEGER) from `payments` where `invoices`.`id` = `payments`.`invoice_id`) as `am' at line 1 (SQL: select `invoices`.*, (select CAST(SUM(amount) AS INTEGER) from `payments` where `invoices`.`id` = `payments`.`invoice_id`) as `amount` from `invoices` where `invoices`.`user_id` in (12, 13, 14, 15, 16, 17, 18, 20, 21, 22, 23, 24, 26, 27, 28, 29, 30, 31, 32, 33, 37, 38, 39, 40, 44, 45, 46, 47, 48, 49) and `season_id` = 1)

can you help me?

Hasan Hatem
  • 15
  • 5
  • can you please post the query run? (https://stackoverflow.com/questions/18236294/how-do-i-get-the-query-builder-to-output-its-raw-sql-query-as-a-string) – Alberto Sinigaglia Sep 07 '20 at 23:56
  • Maybe your localhost and your server have different versions of mysql or their settings are different, check mysql version of both – Itamar Garcia Sep 08 '20 at 00:56

1 Answers1

0

Try using CAST(SUM(amount) AS UNSIGNED) or CAST(SUM(amount) AS SIGNED) instead

Chan Yung Keat
  • 605
  • 6
  • 13