postgresql – Laravel Questions

Laravel Postgres SQLSTATE[42601]: Syntax error: 7 ERROR: zero-length delimited identifier at or near “”“”

2nd March 2021 datatable, laravel, postgresql, yajra-datatable

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

28th February 2021 laravel, postgresql

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

Aggregate and count data by date in Laravel and PostgreSQL

27th February 2021 aggregate-functions, laravel, postgresql

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

How do I join same table miltiple times?

23rd February 2021 laravel, postgresql

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

22nd February 2021 database-migration, laravel, laravel-migrations, php, postgresql

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

Close DB Connection when Script Stop from the cliente side

19th February 2021 database, laravel, pdo, php, postgresql

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

19th February 2021 laravel, postgresql

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

Laravel db:seed command throws database auth error

18th February 2021 homestead, laravel, laravel-seeding, php, postgresql

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

JSON Query optimization in Laravel

17th February 2021 jquery, laravel, php, postgresql, query-optimization

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

How to divide two count from different models in laravel

15th February 2021 eloquent, laravel, postgresql

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

Could not find driver for postgres using laradock

29th December 2020 docker, laradock, laravel, php, postgresql

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

Laravel wherebetween datetime

27th December 2020 laravel, php, postgresql, query-builder

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

24th December 2020 eloquent, laravel, postgresql

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

21st December 2020 eloquent, laravel, php, postgresql

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

21st December 2020 laravel, postgresql, sql

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

Laravel update table with data from another table

18th December 2020 database, laravel, mysql, php, postgresql

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

calling postgres functions that require parameter in laravel controller

16th December 2020 laravel, php, postgresql

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

16th December 2020 laravel, php, postgresql

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

One hour interval – Laravel

14th December 2020 laravel, postgresql

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

PDOException::("could not find driver") laravel pgsql

10th December 2020 laravel, php, postgresql, wamp, wampserver

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

Outer join 3 or more tables in Laravel 8

10th December 2020 full-outer-join, laravel, left-join, postgresql, union

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

9th December 2020 database, laravel, php, postgresql

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

8th December 2020 eloquent, json, laravel, php, postgresql

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

7th December 2020 laravel, laravel-artisan, php, postgresql

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

6th December 2020 laravel, php, postgresql

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

Category : postgresql

Recent Posts

Web Development Company

Tags