opsway / Doctrine Dbal Postgresql
Licence: mit
Add JSON query support to Doctrine DBAL and DQL
Stars: ✭ 114
Labels
Projects that are alternatives of or similar to Doctrine Dbal Postgresql
Doctrinejsonfunctions
Doctrine DQL functions for SQL JSON data type
Stars: ✭ 325 (+185.09%)
Mutual labels: doctrine, postgresql
Postgresql For Doctrine
PostgreSQL enhancements for Doctrine. Provides support for advanced data types (json, jssnb, arrays), text search, array operators and jsonb specific functions.
Stars: ✭ 181 (+58.77%)
Mutual labels: doctrine, postgresql
Doctrine Postgis
Spatial and Geographic Data with PostGIS and Doctrine.
Stars: ✭ 161 (+41.23%)
Mutual labels: doctrine, postgresql
Doctrine Json Odm
An object document mapper for Doctrine ORM using JSON types of modern RDBMS.
Stars: ✭ 420 (+268.42%)
Mutual labels: doctrine, postgresql
Docker Nginx Postgres Django Example
Example using Docker, Django, multiple Postgres databases, NginX, Gunicorn, pipenv, GitLab CI and tox.
Stars: ✭ 110 (-3.51%)
Mutual labels: postgresql
Express Postgres Starter
A starter project for Node.js with Express and Postgres running on Docker Compose
Stars: ✭ 107 (-6.14%)
Mutual labels: postgresql
Pg stat kcache
Gather statistics about physical disk access and CPU consumption done by backends.
Stars: ✭ 106 (-7.02%)
Mutual labels: postgresql
Serverless Postgraphql
Serverless GraphQL endpoint for PostgresSQL using AWS, serverless and PostGraphQL
Stars: ✭ 105 (-7.89%)
Mutual labels: postgresql
Dckerize
Supercharged Rails development using Docker containers
Stars: ✭ 112 (-1.75%)
Mutual labels: postgresql
Next
Directus is a real-time API and App dashboard for managing SQL database content. 🐰
Stars: ✭ 111 (-2.63%)
Mutual labels: postgresql
Xgenecloud
XgeneCloud is now https://github.com/nocodb/nocodb
Stars: ✭ 1,629 (+1328.95%)
Mutual labels: postgresql
Spring Kotlin Exposed
playground for spring-boot 2.*, kotlin , jetbrains-exposed, postgres, jsonb, flyway, docker
Stars: ✭ 106 (-7.02%)
Mutual labels: postgresql
Honeyeql
HoneyEQL is a Clojure library enables you to query database using the EDN Query Language.
Stars: ✭ 111 (-2.63%)
Mutual labels: postgresql
Postgresql Dart
Dart PostgreSQL driver: supports extended query format, binary protocol and statement reuse.
Stars: ✭ 105 (-7.89%)
Mutual labels: postgresql
Ship Hold
data access framework for Postgresql on nodejs
Stars: ✭ 110 (-3.51%)
Mutual labels: postgresql
Ng Admin Postgrest
Demo of an integration between ng-admin and PostgREST
Stars: ✭ 108 (-5.26%)
Mutual labels: postgresql
Haproxy Configs
80+ HAProxy Configs for Hadoop, Big Data, NoSQL, Docker, Elasticsearch, SolrCloud, HBase, MySQL, PostgreSQL, Apache Drill, Hive, Presto, Impala, Hue, ZooKeeper, SSH, RabbitMQ, Redis, Riak, Cloudera, OpenTSDB, InfluxDB, Prometheus, Kibana, Graphite, Rancher etc.
Stars: ✭ 106 (-7.02%)
Mutual labels: postgresql
doctrine-dbal-postgresql
This component allows you to manage some native PostgreSQL data types, operators and functions with the Doctrine DBAL component.
Usage
Add to composer.json
php composer.phar require opsway/doctrine-dbal-postgresql:~0.1
To use the new types you should register them using the Custom Mapping Types feature.
To use the new functions you should register them using the DQL User Defined Functions feature.
Custom Types
- Array Integer (integer[])
- Array BigInt (bigint[])
- TsVector (tsvector)
Custom DQL functions
- CONTAINS - 'OpsWay\Doctrine\ORM\Query\AST\Functions\Contains'
- CONTAINED - 'OpsWay\Doctrine\ORM\Query\AST\Functions\Contained'
- GET_JSON_FIELD - 'OpsWay\Doctrine\ORM\Query\AST\Functions\GetJsonField'
- GET_JSON_FIELD_BY_KEY - 'OpsWay\Doctrine\ORM\Query\AST\Functions\GetJsonFieldByKey'
- GET_JSON_OBJECT - 'OpsWay\Doctrine\ORM\Query\AST\Functions\GetJsonObject'
- GET_JSON_OBJECT_TEXT - 'OpsWay\Doctrine\ORM\Query\AST\Functions\GetJsonObjectText'
- ANY_OP - 'OpsWay\Doctrine\ORM\Query\AST\Functions\Any'
- ALL_OP - 'OpsWay\Doctrine\ORM\Query\AST\Functions\All'
- ARR - 'OpsWay\Doctrine\ORM\Query\AST\Functions\Arr'
- ARR_AGGREGATE - 'OpsWay\Doctrine\ORM\Query\AST\Functions\ArrayAggregate'
- ARR_APPEND - 'OpsWay\Doctrine\ORM\Query\AST\Functions\ArrayAppend'
- ARR_REPLACE - 'OpsWay\Doctrine\ORM\Query\AST\Functions\ArrayReplace'
- REGEXP_REPLACE - 'OpsWay\Doctrine\ORM\Query\AST\Functions\RegexpReplace'
- ARR_REMOVE - 'OpsWay\Doctrine\ORM\Query\AST\Functions\ArrayRemove'
- ARR_CONTAINS - 'OpsWay\Doctrine\ORM\Query\AST\Functions\ArrayContains'
- TO_TSQUERY - 'OpsWay\Doctrine\ORM\Query\AST\Functions\ToTsquery'
- TO_TSVECTOR - 'OpsWay\Doctrine\ORM\Query\AST\Functions\ToTsvector'
- TS_CONCAT_OP - 'OpsWay\Doctrine\ORM\Query\AST\Functions\TsConcat'
- TS_MATCH_OP - 'OpsWay\Doctrine\ORM\Query\AST\Functions\TsMatch'
- UNNEST - 'OpsWay\Doctrine\ORM\Query\AST\Functions\Unnest'
- JSON_AGG - 'OpsWay\Doctrine\ORM\Query\AST\Functions\JsonAgg'
- JSONB_ARRAY_ELEM_TEXT - 'OpsWay\Doctrine\ORM\Query\AST\Functions\JsonbArrayElementsText'
Custom DQL function usage
For an example the CONTAINS function requires your table column in your datbase to be of the type jsonb
.
Otherwise PostgreSQL will not recognize the operator needed to perform this action. (@>)
- Tip: Based on the function you want to use, check if there are any specific column type requirements.
Example query:
$result = $this->em->createQuery(
'SELECT l FROM Foo\Bar\Baz l WHERE CONTAINS(l.metaData, :value) = true')
->setParameter('value', json_encode(['foo'=>'bar']))
->getResult();
Setting the column type to jsonb
.
/**
* @var array
*
* @ORM\Column(type="json", nullable=true, options={"jsonb": true})
*/
private $metaData;
Custom Name | PostgreSql | Usage in DQL | Result in SQL |
---|---|---|---|
CONTAINS | @> | CONTAINS(field, :param) | (field @> '{value}') |
CONTAINED | <@ | CONTAINED(field, :param) | (field <@ '{value}') |
GET_JSON_FIELD | ->> | GET_JSON_FIELD(field, 'json_field') | (table_field->>'json_field') |
GET_JSON_FIELD_BY_KEY | -> | GET_JSON_FIELD_BY_KEY(field, 'json_field') | (table_field->'json_field') |
GET_JSON_OBJECT | #> | GET_JSON_OBJECT(field, 'json_field') | (table_field#>'json_field') |
GET_JSON_OBJECT_TEXT | #>> | GET_JSON_OBJECT_TEXT(field, 'json_field') | (table_field#>>'json_field') |
ANY_OP | ANY | ANY_OP(field) | ANY(field) |
ALL_OP | ALL | ALL_OP(field) | ALL(field) |
ARR | ARRAY | ARR(field) | ARRAY[field] |
ARR_AGGREGATE | array_agg | ARR_AGGREGATE(field) | array_agg(field) |
ARR_APPEND | array_append | ARR_APPEND(field, :param) | array_append(field, param) |
ARR_REPLACE | array_replace | ARR_REPLACE(field, :param1, :param2) | array_replace(field, p1, p2) |
REGEXP_REPLACE | regexp_replace | REGEXP_REPLACE(field, :param1, :param2) | regexp_replace(field, p1, p2) |
ARR_REMOVE | array_remove | ARR_REMOVE(field, :param) | array_remove(field, param) |
ARR_CONTAINS | && | ARR_CONTAINS(field, :param) | (field && param) |
TO_TSQUERY | to_tsquery | TO_TSQUERY(:param) | to_tsquery('param') |
TO_TSVECTOR | to_tsvector | TO_TSVECTOR(field) | to_tsvector(field) |
TS_MATCH_OP | @@ | TS_MATCH_OP(expr1, expr2) | expr1 @@ expr2 |
TS_CONCAT_OP | |||
UNNEST | UNNEST | UNNEST(field) | UNNEST(field) |
JSON_AGG | json_agg | JSON_AGG(expression) | json_agg(expression) |
JSONB_ARRAY_ELEM_TEXT | jsonb_array_elements_text | JSONB_ARRAY_ELEM_TEXT(field, 'json_field') | jsonb_array_elements_text(field) |
Note that the project description data, including the texts, logos, images, and/or trademarks,
for each open source project belongs to its rightful owner.
If you wish to add or remove any projects, please contact us at [email protected].