Describe the bug
I'm trying to generate a chart, however when I pass the parameter "filter" to SQL Builder, more specifically the file "TotalRecordsController" assembles the query as if I were using MySQL, instead of mounting it for PostgreSQL.
Laravel: 8.40 PHP: 7.4 PostgreSQL: 12 Nova: 3.30 ChartJS: 0.3.5
My Code:
public function cards(Request $request)
{
$process_statuses= [
["label"=>"INITIAL" ,"color" => "#007BFF"],
['label'=>'CONVERTED' ,'color' => '#28A645'],
['label'=>'ERROR' ,'color' => '#DC3544'],
['label'=>'WAITING_TO_ERICH' ,'color' => '#17A2B8'],
['label'=>'WAITING_TO_CONVERT','color' => '#17A2B8']
];
$series = [];
foreach ($process_statuses as $status){
$new_serie = [
'label' => $status['label'],
'filter'=>[
'key'=>'process_status',
'value'=>$status['label']
],
'barPercentage' => 0.5,
'backgroundColor' => $status['color'],
];
$series = [...$series,$new_serie];
}
return [
(new BarChart())
->title('Today Status')
//->model(\App\Models\GoogleOfflineConversion::class)
->model('\App\Models\FacebookOfflineConversion')
->series($series)
->options([
'btnRefresh'=>true,
'uom' => 'hour',
])->width('full'),
];
}
Error:
SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "'INITIAL'"
LINE 1: ... process_status = 'INITIAL' then 1 else 0 end) as 'INITIAL',...
^ (SQL: select HOUR(facebook_offline_conversions.created_at) AS cat, HOUR(facebook_offline_conversions.created_at) AS catorder, sum(1) counted, SUM(CASE WHEN process_status = 'INITIAL' then 1 else 0 end) as 'INITIAL', SUM(CASE WHEN process_status = 'CONVERTED' then 1 else 0 end) as 'CONVERTED', SUM(CASE WHEN process_status = 'ERROR' then 1 else 0 end) as 'ERROR', SUM(CASE WHEN process_status = 'WAITING_TO_ERICH' then 1 else 0 end) as 'WAITING_TO_ERICH', SUM(CASE WHEN process_status = 'WAITING_TO_CONVERT' then 1 else 0 end) as 'WAITING_TO_CONVERT' from "facebook_offline_conversions" where facebook_offline_conversions.created_at >= 2021-10-28 00:00:00 group by "catorder", "cat" order by "catorder" asc)
Migration of the my database:
Schema::create('facebook_offline_conversions', function (Blueprint $table) {
$table->uuid('id')->primary();
$table->foreignUuid('company_id')->nullable();
$table->integer('refer_site_id')->nullable();
$table->foreignUuid('site_id')->nullable();
$table->foreignUuid('facebook_ads_site_credential_id')->nullable();
$table->string('crm_order_id')->nullable();
$table->string('fbc')->nullable();
$table->longText('document_number')->nullable();
$table->longText('email')->nullable();
$table->longText('phone_number')->nullable();
$table->float('value')->nullable();
$table->string('currency')->nullable();
$table->longText('city')->nullable();
$table->longText('state')->nullable();
$table->longText('zip_code')->nullable();
$table->longText('country')->nullable();
$table->timestamp('conversion_time')->nullable();
$table->longText('ip_address')->nullable();
$table->longText('browser')->nullable();
$table->string('process_status')->nullable();
$table->integer('tries')->nullable();
$table->boolean('approximated')->default(false);
$table->json('conversion_response')->nullable();
$table->timestamps();
});
I'm resolving this problem. The solution is to change the file "src/api/TotalRecordsController" inside the folder "~/vendor/coroowicaksono/chart-js-integration" so that it suits PostgreSQL.