PHP code example of cuppett / cakephp-pg_utils

    
        
<?php
require_once('vendor/autoload.php');

/* Start to develop here. Best regards https://php-download.com/ */

    

cuppett / cakephp-pg_utils example snippets

	public $default = array(
		'datasource' => 'Database/Postgres',
		'persistent' => false,
		'host' => 'localhost',
		'login' => 'user',
		'password' => 'password',
		'database' => 'database_name',
		'prefix' => '',
		'encoding' => 'utf8',
		'settings' => array(
			'intervalstyle' => 'iso_8601'
		)		
	);

class Task extends AppModel {
    public $actsAs = array(
        'PgUtils.Interval' => array(
            'fields' => array(
                'estimate',
                'actual',
                'remain'
            )
        ),
        'PgUtils.Json' => array('fields' => array('custom_attributes'))
    );

$this->Behaviors->load(
    'PgUtils.Search', array(
        'column' => 'searchable_text',
        'weights' => array(
            'name' => 'A',
            'description' => 'D'
        )
    )
);

$results = $this->PrimaryObject->search($this->request->data['Search']['query'],
    array(
        'limit' => 25,
        'headline' => array('name', 'description'),
        'fields' => array('id', 'name', 'description', 'modified')
     )
);

sql
CREATE TABLE primary_objects (
  id UUID NOT NULL default uuid_generate_v4(),
  "name" varchar(255) not null,
  description text,
  created timestamp with time zone not null default CURRENT_TIMESTAMP,
  modified timestamp with time zone not null default CURRENT_TIMESTAMP,
  searchable_text tsvector
);

CREATE OR REPLACE FUNCTION updateVector() RETURNS trigger AS $$
BEGIN
	NEW.searchable_text = 
		setweight(to_tsvector('pg_catalog.english', coalesce(NEW."name", '')), 'A') ||
		setweight(to_tsvector('pg_catalog.english', coalesce(NEW.description, '')), 'D');

	RETURN NEW;	
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER indexObjects
	BEFORE INSERT OR UPDATE OF "name", description ON primary_objects
	FOR EACH ROW
	EXECUTE PROCEDURE updateVector();