Category : postgresql

to display the data is fine, but when doing a search an error will occur. I do not know what happened. Controller : public function data() { if(request()->ajax()) { return datatables() ->of(DB::table(‘data_pmks’)->select(‘id_pmks’,’tgl_pelaksanaan’, ‘nama’, ‘jenis_kelamin’,’jenis_pmks’,’jenis_pelayanan’,’jenis_intervensi’) ->orderBy(‘id_pmks’, ‘desc’) ->where(‘is_active’,’1′)->where(‘jenis_intervensi’,Session::get(‘intervensi’))) ->editColumn(‘mybutton’, static function ($row) { return ‘<a class="btn btn-sm btn-success detail" href="/opd/pmks/data/edit/’.$row->id_pmks.’">Detail</a> <a class="btn btn-sm btn-danger detail" href="/opd/pmks/data/hapus/’.$row->id_pmks.’">Hapus</a>’; ..

Read more

I’m trying to group data by month for the current year. The query below works on MySQL database but gives an error when connected to PostgreSQL. $results = DB::table(‘visits’) ->select(DB::raw(‘month(visitdate) month, count(*) data’)) ->where(‘team_id’, 2) ->whereYear(‘visitdate’, now()->year) ->groupBy(‘month’) ->get(); This error was generated: SQLSTATE[42601]: Syntax error: 7 ERROR: syntax error at or near "month" LINE ..

Read more

I recently migrated from MySQL to Postgres and I haven’t been able to translate this MySQL query to a Postgres one: function aggregate($query, $table, string $order): array { $by = $order === ‘day’ ? ‘date’ : ‘month’; $from = $by === ‘date’ ? now()->subDays(14) : now()->subYears(2); $dateKey = $by === ‘date’ ? "DATE($table.created_at)" : "EXTRACT(YEAR_MONTH ..

Read more

I acomplished this when user make input like this: $CalcPrevNumb = model::latest()->first(); $student = new model([ ‘recordtime’ => $request->get(‘recordtime’), ‘obem_sprqmo_pr_den’ => $request->get(‘cname2’) – $CalcPrevDay->cname2 ]); $student->save(); But now the goal is to do this when for example csv file is imported in to the DB(pgsql). Now I need to substract the last created and the ..

Read more

First time using seeds in Laravel. I created the seeds like: <?php use IlluminateDatabaseSeeder; class DatabaseSeeder extends Seeder { /** * Seed the application’s database with all data needed for unit tests. * * @return void */ public function run() { DB::table(‘t0710_caixas_cad_inic’)->insert( [ ‘caixa_ci_codigo’ => ‘xxxxx’, ’emp_codigo’ => ‘001’, ‘caixa_ci_id’ => ‘1’, ‘caixa_ci_nome’ => ‘Test ..

Read more

I have two models Task and Step where each task has many steps. I want to get all tasks fields and get the count of completed steps for each one + ratio for completion for each task. I tried this: Task::withCount([‘steps AS countSteps’, ‘completedSteps AS countCompletedSteps’]) ->selectRaw(‘countCompletedSteps / countSteps AS completionRatio’) ->get(); Where steps is ..

Read more

I have a problem using wherebetween to filter data by timestamp (datetime) To test query, this is my route Route::get(‘/test3/{iduser}/{waktu}’, ‘[email protected]_person’); and this is URL test http://localhost:8000/test3/32/2020-12-08 2010:15:00 this is my controller public function fgetxy_person($iduser, $waktu){ $time = Carbon::createFromFormat(‘Y-m-d H:i:s’, $waktu, ‘Asia/Jakarta’); // dd($time->addMinutes(-5)); //output: 10:10:00 // dd($time->addMinutes(30)); //output: 10:45:00 $xybeacon = DB::table(‘transaksi as tb’) ..

Read more

I have a CASE raw statement that generates a status for each row. I want to filter by this status should the user not want to see all items but a specific set. $query = Device::leftJoin(‘devices_meters’, ‘devices_meters.device_id’, ‘=’, ‘devices.id’) ->leftJoin(‘device_measurements’, ‘device_measurements.device_id’, ‘=’, ‘devices.id’) ->select( ‘devices.*’, DB::raw(" CASE WHEN devices.retired = false AND devices.last_reported_utc_at > :dateFilter ..

Read more

how if I filter the id but the data searched does not exist but still shows up the results in laravel i have query builder like this how when the searched id deparment does not exist but still displays the view table results based on the date filter above my code $body_report = db::table(‘pur_tra_purchase_request_report_requestor’) ->where(‘pur_tra_purchase_request_report_requestor.pr_date’,’>=’,$dateFrom) ..

Read more

I have created this function in postgres. train_route_timings(first_station, last_station, nth_route), this function required three parameters(first_station, last_station, nth_route) with their as follow first_station as text, second_station as text and n_route as integer Running this query below on PGadmin runs successfully select * from train_route_timings(‘Goodwood’, ‘Stock Road’, 2); how can i call this query/function in my controller ..

Read more

I’m trying to select all records based on date/time. I have this timestamps in postgreSQL: 13/12/2020 11:00:00 14/12/2020 11:31:00 14/12/2020 12:30:00 14/12/2020 13:00:00 15/12/2020 02:00:00 I have a code in the controller getting all records: $start_date = date(‘d/m/Y 00:00:00’); $end_date = date(‘d/m/Y 23:59:59’); if($request->start_date != ” && $request->end_date != ”) { // if user fill ..

Read more

I need to have Laravel with Postgres I have wampserver and enabled pgsql and pdo_pgsql extensions for php my env file is same as below: DB_CONNECTION=pgsql DB_HOST=127.0.0.1 DB_PORT=5432 DB_DATABASE=mehri DB_USERNAME=postgres DB_PASSWORD=123456 I installed postgres and pgAdmin on windows Also I added LoadFile "C:/Program Files/PostgreSQL/8.4/bin/libpq.dll" to httpd.conf Also I tried to fix this error by adding ..

Read more

I have a problem occur when I run my code. The problem is: SQLSTATE[42703]: Undefined column: 7 ERROR: column sites.sites_id does not exist LINE 1: …me", "roles".* from "roles" inner join "sites" on "sites"."s… ^ HINT: Perhaps you meant to reference the column "sites.sites_id". (SQL: select "sites"."site_code", "sites"."site_name", "roles".* from "roles" inner join "sites" on ..

Read more

I want to create a Postgres database from PHP. This is my code: public function handle() { $schemaName = ‘new_database’; $port = env(‘DB_PORT’) ?: ‘5432’; // Connecting to postgres $pdo = $this->getPDOConnection(env(‘DB_SAMPLE_HOST’), $port, env(‘DB_SAMPLE_USERNAME’), env(‘DB_SAMPLE_PASSWORD’)); $pdo->exec(sprintf( ‘CREATE DATABASE %s;’, $schemaName, ‘utf8’, ‘utf8_unicode_ci’ )); } private function getPDOConnection($host, $port, $username, $password) { return new PDO(sprintf(‘pgsql:host=%s;port=%d;sslmode=prefer’, $host, ..

Read more