#### 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 ..

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 ..

I have only one table and I want to join that table multiple times. How do I perform multiple join in same table? From the image, I got Taplejung containing id 4708 and I got Taplejung Municipality which has parent_id 4708. I just want that two value in same column. For example: Taplejung – Taplejung ..

How to write laravel migration to create column with point data type without postGIS extension, postGres alone does allow a point type for columns even without postGIS, Sourc..

I have an API that basically receives a lot of information and inserts it into the database. In some cases, this amount of information can reach 5,000 records. In some situations, when the api client closes the connection, the database continues with the active connection and never closes. even with the idle_in_transaction_session_timeout attribute set to ..

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 ..

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 ..

im working on a function that returns some data. It’s nested in another function wich returns more data. Currently is not handling very well scalability (the overall response time gets up to 2mins in some cases, but if i don’t use this function it takes only a few seconds) The function that is being affected ..

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 ..

I am using the laradock and Postgres in my laravel project but when I try to run PHP artisan migrate it gives me the error, could not find the driver. below is my .env file DB_CONNECTION=pgsql DB_HOST=postgres DB_PORT=5432 DB_DATABASE=vv DB_USERNAME=default DB_PASSWORD=secret When I run my project it fetches the data, but when I try to ..

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 ..

I am aware that there’s a question similar to this, with postgres, but that uses raw Sql, and I am looking for a mostly ORM solution. That’s why this question is different. That may in the long run be the right answer but I thought I’d pose it to the community. Sometimes my data set ..

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) ..

I have two tables on two different database connections in laravel. Mysql: Products id code name 1 null abc 2 null def 3 null ghi PostgreSQL Products id code name 44 z1 abc 55 x2 def 88 y3 ghi I want to update Mysql Products table with the code column from PostgreSQL Products table where ..

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 ..

I am using this formula. SELECT id, ( 3959 * acos( cos( radians(14.685327) ) * cos( radians( latitude ) ) * cos( radians( longitude ) – radians(80.054889) ) + sin( radians(14.685327) ) * sin(radians(latitude)) ) ) AS distance FROM kr_user HAVING distance < 50 ORDER BY distance But I got error : How to solve ..

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 ..

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 ..

This topic is related to my previews one Join two tables with all records I’m tryng now to join 3 or more tables in my Laravel controller code, and view them in one Datatable. table1 +——————–+———+ | recordtime | tempout | +——————–+———+ | 4.12.2020 10:00:00 | 1.1 | | 4.12.2020 10:30:00 | 1.2 | | ..

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 ..

I have two Laravel functions that use Eloquent to retreive information from the DB(Postgres) which are virtually the same except that the where in one is a greater or equal than and the other one is less than (dates) First function has a where like this at the end: ->where(‘start’, ‘>=’, date(‘Y-m-d’)); And is returning ..

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, ..

I’m attempting to build a very simple function to convert units to/from a ‘Base Unit of Measurement’ (ie: various different units to/from KGs), but I’m running into some inconsistent results. When I convert a value to the Base UOM I get the expected value; however, when I convert it back, I’m getting a result that ..