PHPackages cuppett/cakephp-pg\_utils - PHPackages - PHPackages [Skip to content](#main-content)[PHPackages](/)[Directory](/)[Categories](/categories)[Trending](/trending)[Leaderboard](/leaderboard)[Changelog](/changelog)[Analyze](/analyze)[Collections](/collections)[Log in](/login)[Sign up](/register) 1. [Directory](/) 2. / 3. [Database & ORM](/categories/database) 4. / 5. cuppett/cakephp-pg\_utils AbandonedArchivedCakephp-plugin[Database & ORM](/categories/database) cuppett/cakephp-pg\_utils ========================= CakePHP PostgreSQL Utilities 1.0.1(11y ago)752MITPHPPHP >=5.3.3 Since Feb 1Pushed 11y ago3 watchersCompare [ Source](https://github.com/cuppett/cakephp-pg_utils)[ Packagist](https://packagist.org/packages/cuppett/cakephp-pg_utils)[ Docs](https://github.com/cuppett/cakephp-pg_utils)[ RSS](/packages/cuppett-cakephp-pg-utils/feed)WikiDiscussions master Synced 1mo ago READMEChangelog (1)Dependencies (1)Versions (2)Used By (0) CakePHP PostgreSQL Utilities ============================ [](#cakephp-postgresql-utilities) PgUtils provides a small set of behaviors useful for working with Postgres datasources. Requirements ------------ [](#requirements) The master branch has the following requirements: - CakePHP 2.2.0 or greater. - PHP 5.3.0 or greater. Features -------- [](#features) - Search behavior - Performing text searches on models. Allows sophisticated full text searches to be performed. Also includes a simplified query parser. - JSON behavior - Retrieving and storing json datatypes. Values are exploded into associative arrays upon retrieval and collapsed back down on save. - Array behavior - Retrieving and storing array datatypes. Values are exploded into arrays upon retrieval and collapsed back down on save - Interval behavior - Retrieving and storing interval datatypes. PHP intervals are converted to ISO8601 format and stored into this special PostgreSQL datatype column. Installation ------------ [](#installation) - Clone/Copy the files in this directory into `app/Plugin/PgUtils` - Ensure the plugin is loaded in `app/Config/bootstrap.php` by calling `CakePlugin::load('PgUtils');` - To use the IntervalBehavior, you must ensure values are stored/retrieved using iso8601 via your database connection. ``` 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' ) ); ``` - Full text search requires a ts\_vector column to be created and maintained. The simplest way is with a trigger. ### Using Composer [](#using-composer) Ensure `require` is present in `composer.json`. This will install the plugin into `Plugin/AwsUtils`: ``` { "require": { "cuppett/cakephp-pg_utils": "1.0.*" }, "extra": { "installer-paths": { "app/Plugin/PgUtils": ["cuppett/cakephp-pg_utils"] } } } ``` Examples -------- [](#examples) ### Using Interval/JSON/Array types in a model [](#using-intervaljsonarray-types-in-a-model) If your database table contains interval, json or array types in their definition, you can configure behaviors to help translate them into more PHP-friendly types and values via the afterFind and beforeSave callbacks. They are configured on the model by identifying the fields to convert. One potential enhancement would be to automatically detect these fields; however, it may be useful to not have all converted automatically as well. ``` class Task extends AppModel { public $actsAs = array( 'PgUtils.Interval' => array( 'fields' => array( 'estimate', 'actual', 'remain' ) ), 'PgUtils.Json' => array('fields' => array('custom_attributes')) ); ``` ### Integrating the search column into your database. [](#integrating-the-search-column-into-your-database) The search behavior requires a ts\_vector column to be defined. The column must be maintained separately of the behavior. Here is an example set of SQL to add & update the column when the database is updated: ``` 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(); ``` You can add other columns & weights to the search; you can add a gist or gin index to the table/column to speed up the results; etc. ### Using search from your application [](#using-search-from-your-application) In your Model class: ``` $this->Behaviors->load( 'PgUtils.Search', array( 'column' => 'searchable_text', 'weights' => array( 'name' => 'A', 'description' => 'D' ) ) ); ``` From your Controller: ``` $results = $this->PrimaryObject->search($this->request->data['Search']['query'], array( 'limit' => 25, 'headline' => array('name', 'description'), 'fields' => array('id', 'name', 'description', 'modified') ) ); ``` Then you can display the results in your view like normal. Potential searches include freeform text such as "cat and dog", "cat or dog", etc. The current set of operators are "and", "or", and "-" to exclude certain terms. You can also prefix the term with a field name such as "name:cat". From the above setup examples, this will change the query such that only the "A" category will be searched for the term "cat". Please see the topic [Controlling Text Search](http://www.postgresql.org/docs/9.4/static/textsearch-controls.html) in the PostgreSQL documentation for more information on these topics. I've tried to expose the most commonly desired knobs for my use cases, but I'm open to other suggestions. Reporting issues ---------------- [](#reporting-issues) If you have a problem with PgUtils please open an issue on [GitHub](https://github.com/cuppett/cakephp-pg_utils/issues). ### Health Score 28 — LowBetter than 54% of packages Maintenance20 Infrequent updates — may be unmaintained Popularity13 Limited adoption so far Community8 Small or concentrated contributor base Maturity58 Maturing project, gaining track record Bus Factor1 Top contributor holds 100% of commits — single point of failure How is this calculated?**Maintenance (25%)** — Last commit recency, latest release date, and issue-to-star ratio. Uses a 2-year decay window. **Popularity (30%)** — Total and monthly downloads, GitHub stars, and forks. Logarithmic scaling prevents top-heavy scores. **Community (15%)** — Contributors, dependents, forks, watchers, and maintainers. Measures real ecosystem engagement. **Maturity (30%)** — Project age, version count, PHP version support, and release stability. ### Release Activity Cadence Unknown Total 1 Last Release 4124d ago ### Community Maintainers ![](https://www.gravatar.com/avatar/c5fd317ab85116ec9b985d98190e835fc5a9789469725d3b9d77eb71e1f64723?d=identicon)[cuppett](/maintainers/cuppett) --- Top Contributors [![cuppett](https://avatars.githubusercontent.com/u/239904?v=4)](https://github.com/cuppett "cuppett (10 commits)") --- Tags jsonsearchpostgresqlpgsqlcakephp ### Embed Badge ![Health badge](/badges/cuppett-cakephp-pg-utils/health.svg) ``` [![Health](https://phpackages.com/badges/cuppett-cakephp-pg-utils/health.svg)](https://phpackages.com/packages/cuppett-cakephp-pg-utils) ``` ### Alternatives [doctrine/dbal Powerful PHP database abstraction layer (DBAL) with many features for database schema introspection and management. 9.7k578.4M5.6k](/packages/doctrine-dbal)[martin-georgiev/postgresql-for-doctrine Extends Doctrine with native PostgreSQL support for arrays, JSONB, ranges, PostGIS geometries, text search, ltree, uuid, and 100+ PostgreSQL-specific functions. 4485.3M4](/packages/martin-georgiev-postgresql-for-doctrine)[scienta/doctrine-json-functions A set of extensions to Doctrine that add support for json query functions. 58723.9M36](/packages/scienta-doctrine-json-functions)[apix/cache A thin PSR-6 cache wrapper with a generic interface to various caching backends emphasising cache taggging and indexing to Redis, Memcached, PDO/SQL, APC and other adapters. 114542.8k6](/packages/apix-cache)[amphp/postgres Asynchronous PostgreSQL client for Amp. 110509.8k27](/packages/amphp-postgres)[tigrov/yii2-pgsql Improved PostgreSQL schemas for Yii2 3467.0k](/packages/tigrov-yii2-pgsql) PHPackages © 2026 [Directory](/)[Categories](/categories)[Trending](/trending)[Changelog](/changelog)[Analyze](/analyze)