postgresql,symfony2,doctrine2,doctrine , Configuring native Point PosgreSQL Type in Doctrine2


Configuring native Point PosgreSQL Type in Doctrine2

Question:

Tag: postgresql,symfony2,doctrine2,doctrine

Trying to do the same as here, with Postgre :

http://codeutopia.net/blog/2011/02/19/using-spatial-data-in-doctrine-2/

I have a problem with the "convertToDatabaseValue" function (see here : http://doctrine-dbal.readthedocs.org/en/latest/reference/types.html#custom-mapping-types)

My Custom Type :

use Doctrine\DBAL\Types\Type;
use Doctrine\DBAL\Platforms\AbstractPlatform;

class PointType extends Type
{
const POINT = 'point';

public function getSqlDeclaration(array $fieldDeclaration, AbstractPlatform $platform)
{
    return 'POINT';
}

public function convertToPHPValue($value, AbstractPlatform $platform)
{
    return new Point($value[0],$value[1]);
}

public function convertToDatabaseValue($value, AbstractPlatform $platform)
{
    return "'".$value->getLongitude().','.$value->getLatitude()."'";
}

public function getName()
{
    return self::POINT;
}
}

Manualy, I can insert this Point with following SQL :

INSERT INTO points (pointtype,name,score) VALUES ('2.43145,42.84214',"My Place",3241)

But doctrine DBAL, creates following code :

'INSERT INTO points (pointtype,name,score) VALUES (?,?,?)' with params ["'2.43145,42.84214'","My Place",3241]

-> My Point is interpreted as a string ...

Can I avoid that ?

CAUTION: I don't want to use PostGIS, juste native Points from PostgreSQL


Answer:

It is because you need to return just string and Doctrine will automatically wrap it with quotes. Try this code:

public function convertToDatabaseValue($value, AbstractPlatform $platform)
{
    return $value->getLongitude().','.$value->getLatitude();
}

It will generate you next SQL query:

INSERT INTO points (pointtype,name,score) VALUES ("2.43145,42.84214","My Place",3241)

Related:


How to use Rails #update_attribute with array field?


ruby-on-rails,ruby,postgresql,ruby-on-rails-4,activerecord
Each question has an array of tags. schema.rb: create_table "questions", force: true do |t| t.text "tags", default: [], array: true How to atomically append to tags? How to prevent dups within the array? I tried question.update_attribute tags: tags << :ruby, but this doesn't work. Rails 4.17 and Postgres. EDIT: This...

Subtract two columns of different tables


sql,postgresql,sum,aggregate-functions,subtract
I have two unrelated tables: contribution(id,amount, create_at, user_id) solicitude(id, amount, create_at, status_id, type_id, user_id) I need to subtract the sum of the amount of the contribution and of the solicitude from a user, but that result can't to be negative. How can I do this? Function or query? I tried...

Syntax error while creating table in PostgreSQL 8.1


postgresql
I have a syntax error in this query: CREATE TABLE test (LIKE original_table INCLUDING INDEXES); ERROR : syntax error at or near "INDEXES" I am using PostgreSQL 8.1 version. Where is the issue in my query?...

Symfony 2 - Class “Mingle\StandardBundle\Entity\Product” is not a valid entity or mapped superclass


php,symfony2,doctrine,entity,bundle
I created a Product.php file in my bundle Mingle\StandardBundle\Entity like this: <?php namespace Mingle\StandardBundle\Entity; use Doctrine\ORM\Mapping as ORM; /* * @ORM\Entity * @ORM\Table(name="product") */ class Product { /* * @ORM\Column(type="integer") * @ORM\ID * @ORM\GeneratedValue(strategy="AUTO") */ protected $id; /* * @ORM\Column(type="string",length=100) */ protected $name; /* * @ORM\Column(type="decimal",scale=2) */ protected $price; /*...

Spring Boot - How to set the default schema for PostgreSQL?


java,spring,hibernate,postgresql
What I am currently doing in my application.properties file is: spring.datasource.url=jdbc:postgresql://localhost:5432/myDB?currentSchema=mySchema Isn't there another property for this? Since it looks hacky and according to a post (still finding the link sorry, will update later), it is only applicable to PostgreSQL 9.4....

Postgres SQL constraint a character type


postgresql,constraints
I have a table definition in Postgres. I would like to add a constraint to a column that is of Character datatype to have only 3 allowed values: CREATE TABLE my_table ( id character varying(255) NOT NULL, uid character varying(255) NOT NULL, my_text text NOT NULL, is_enabled boolean NOT NULL...

Error while trying to insert data using plpgsql


postgresql,timestamp,plpgsql
I have the following plpgsql function: CREATE OR REPLACE FUNCTION test_func(OUT pid bigint) RETURNS bigint AS $BODY$ DECLARE current_time timestamp with time zone = now(); BEGIN INSERT INTO "TEST"( created) VALUES (current_time) RETURNING id INTO pid; END $BODY$ LANGUAGE plpgsql; select * from test_func(); The above gives an error: column...

need help specifying potentially reserved words as strings in postgres query


postgresql
I've been using mysql for close to 10 years, but have recently jumped into a project that's exposed me to postgres for the first time. Most of the differences aren't a bit deal, but I have been running into some small issues along the way that are throwing me off....

How to get second row in PostgreSQL?


sql,postgresql
I have next table: COLUMN_NAME DATA_TYPE PK NULLABLE inc_id bigint YES NO dt_cr timestamp NO NO email varchar(255) NO YES email column is not unique, so I should use GROUP BY email. Question: How can I get from this table minimal date, next row after minimal date and email related...

PostgreSQL can't find column


java,postgresql
I create a database that contains 4 column (ID_PRODUIT,NOM_PRODUIT,QUANTITE, PRIX).I tried to make a methods that allow me to serach Product(my class)using a string key,however, the query failed to identify the "NOM_PRODUIT" column and it turn "NOM_PRODUIT" to "nom_produit" in the messsage error. Connection conn = DriverManager.getConnection(url, user, passwd); PreparedStatement...

In PostgreSQL is it possible to join between table and function?


sql,postgresql
I have a table named Cars and a function named PartsPerCar there is no direct link between carid and partid. the link is though 3 tables in the middle. I do have a function called PartsPerCar which gets carid (it goves via the tables in the middle) and give back...

Logs an entire array using Monolog [closed]


php,symfony2,monolog,symfony-2.6
Is there any way to log an entire array using Monolog? I have been reading several docs but didn't find a way to log the entire array in a readable format, any advice? Docs I've read: Monolog, how to log PHP array into console? http://symfony.com/doc/current/cookbook/logging/monolog.html https://www.webfactory.de/blog/logging-with-monolog-in-symfony2 ...

Using crontab to call wget every 5 minutes


php,symfony2,crontab,wget
I am working on a project where the wget and crontab are used to run a process in the background. I have a php file named "Hello.php" that I want to run this every 5 minutes. I have found that if I want to have a cron job run every...

Symfony change validation message globally


php,symfony2,symfony-2.3
Is there a way to change the basic validation error message to one I need for all entities at once? Currently using Yaml config for every entity, but it's not a way. CF\MyBundle\Entity\User: properties: email: - Email: message: e-mail no válido - NotBlank: message: Campo obligatorio username: -NotBlank: message: Campo...

Store entity from session doesn't work => $em->persist()


php,symfony2,doctrine2
at first: Sorry for my poor english :-) A beginner need help! I have 3 entities/tables: Tables: contact_person id, title, firstname, surname, adress_id address id, street, zip, city, country_id country (fix values) id, name, code 1, Austria, AT 2, Germany, DE ... Entities: /** * ContactPerson * * @ORM\Table(name="contact_person") *...

Unique Entity Error message


api,symfony2,exception-handling,doctrine
I have a user entity which have some unique fields. The code bellow shows you how I defined it. /** * @UniqueEntity(fields={"login"}, message="UNIQUE ERROR MESSAGE") */ ...... /** * @var string * * @ORM\Column(name="login", type="string", length=255, unique=true) */ private $login; Developping an API, I would like to be able to...

Translation of interval


postgresql,datetime,translation,intervals,postgresql-8.4
There is maybe a way to translate automatically interval as it's shown by postgresql (e.g. "1330 days 10:54:54.266684") to other languages? For instance using locales, or other settings or I have to use replace, e.g. regexp_replace((now()-t.another_date)::text, 'day','dzień')?

How to customize the configuration file of the official PostgreSQL docker image?


postgresql,docker
I'm using the the postgres official image https://registry.hub.docker.com/_/postgres/. And now I'm trying to customize its configuration. For this purpose the command sed is used, e.g. to change the max_connections: sed -i -e"s/^max_connections = 100.*$/max_connections = 1000/" /var/lib/postgresql/data/postgresql.conf I tried two methods to apply this configuration. The first is by adding...

Prepared statements: Using unnamed and unnumbered question mark style positional placeholders


postgresql
In SQL systems other than Postgres, such as MySQL for instance, prepared statements can use question marks ? as a placeholder for data in prepared statements. INSERT INTO foo (id, name) VALUES (?, ?), (?, ?); However, in Postgres the only available placeholders seem to be the numbered placeholders, so...

Deserializing or parse XML response in Symfony2


php,xml,symfony2,deserialization,jmsserializerbundle
I am calling a API method through cURL and I got this response: <?xml version="1.0" encoding="UTF-8"?> <jobInfo xmlns="http://www.force.com/2009/06/asyncapi/dataload"> <id>75080000002s5siAAA</id> <operation>query</operation> <object>User</object> <createdById>00580000008ReolAAC</createdById> <createdDate>2015-06-23T13:03:01.000Z</createdDate> <systemModstamp>2015-06-23T13:03:01.000Z</systemModstamp> <state>Open</state>...

PostgreSQL: trigger to call function with parameters


postgresql
In PostgreSQL, I'm trying to create a trigger that passes an argument to a function, but it appears that this operation looks for a function whose signature has zero arguments: ERROR: function create_rec_if_needed() does not exist: CREATE TRIGGER after_update_winks AFTER UPDATE ON winks FOR EACH ROW WHEN (NEW.counter > 3)...

Redirect if ActiveRecord::RecordNotUnique error exists


ruby-on-rails,postgresql,activerecord,error-handling
I have the next code to save: Transaction.create(:status => params[:st], :transaction_id => params[:tx], :purchased_at => Time.now).save! But how can I redirect to main root page if this ActiveRecord::RecordNotUnique error appears? Can I catch this error?...

How to install / use orafce package in postgresql 9.4?


postgresql,debian,orafce
I'm running the "official" docker container of postgresql in version 9.4. I went inside the running container and installed orafce docker exec -i -t my_postgres bash apt-get install postgresql-9.4-orafce afterwards I've tried to reload and restart the postgresql service, as well as just restarting the whole container, but when I...

Symfony/Twig radio style formbuilder


php,symfony2,twig,formbuilder
I need to style each of radio button using Symfony form builder. This is my part of my createFormBuilder: ->add('categoryId', 'entity', array( 'class' => 'MyBundle:Category', 'property' => 'name', 'required' => false, 'expanded' => true )) And in my twig template: {% for child in form.categoryId %} <div class="radio i-checks col-md-3">...

Connecting C++ and Postgresql


c++,postgresql,linker-error,libpqxx
I'm trying to connect C++ to Postgres. I recently installed libpqxx with Homebrew as follows: brew install libpqxx Then I made it with: make DatabaseTest on the file DatabaseTest.cpp. I ran the following simple program and got a strange error that I'm not sure what to do with... #include <iostream>...

With a OneToMany relation between entities, how create the right queryBuilder with Doctrine (on the Inversed Side entity)


php,symfony2,doctrine2,one-to-many,query-builder
I have these 3 entities in my symfony2.6 project: Compteurs.php class Compteurs { /** * @var \PointsComptage * * @ORM\ManyToOne(targetEntity="PointsComptage", inversedBy="compteurs") * @ORM\JoinColumns({ * @ORM\JoinColumn(name="pointscomptage_id", referencedColumnName="id") * }) */ private $pointsComptage; /** * @var \Doctrine\Common\Collections\Collection * * @ORM\OneToMany(targetEntity="ParametresMesure", mappedBy="compteurs") */ private $parametresMesure; ParametresMesure.php: class ParametresMesure { /** * @var Compteurs...

wkhtmltopdf on openSuSE: cannot connect to X server


php,symfony2,pdf-generation,twig,wkhtmltopdf
I need wkhtmltopdf on openSuSE. I have installed it via the repository. The version is 0.12.1. To render HTML with Symfony, I use the KnpSnappyBundle. By rendering a twig template, I got the following error message: The exit status code '1' says something went wrong: stderr: "wkhtmltopdf: cannot connect to...

How to order SQL query result on condition?


sql,postgresql,order,condition
i have the this SQL query: SELECT DISTINCT category FROM merchant ORDER BY category ASC that gives this output: accommodation education food general health money shopping sport transport How to put the row that contains "general" at the start (or the end) of the result?...

Avoid calling COUNT twice in CASE expression (PostgreSQL)


sql,postgresql
Inside a larger query, I have to COUNT a variable, then if it is larger than 1, have the count as a string otherwise an empty string: CASE COUNT(measurement.id) > 1 THEN to_char(COUNT(measurement.id),' 999') ELSE '' I'm afraid this is slow because I use COUNT() twice. Is there a better...

How to create a SELECT query FROM “TABLE1 AND TABLE2”


sql,postgresql,select,join
I have a PostgreSQL database, with only SELECT permissions. In this DB there are two tables with the same structure (the same columns). I need to write several query in each table and join the results. There is a way for writing a query like this one? SELECT field1, field2,...

Retrieve updated rows in AFTER UPDATE trigger Postgresql


postgresql,triggers,plpgsql
I'm writing a AFTER UPDATE trigger in postgresql. Actualy I need to get at least one row after update in STATEMENT LEVEL trigger, but there is no OLD or NEW variable there. In FOR EACH ROW trigger I didn't manage to find a sort of batch_last param. But to do...

postgresql complex group by in query


sql,postgresql
I have a query that gives: itemid deadlineneeded delievrydate quantity 200 15/07/15 14/07/15 5 200 15/07/15 14/07/15 10 200 15/07/15 13/07/15 25 201 15/07/15 14/07/15 30 200 14/07/15 10/07/15 3 201 15/07/15 15/07/15 100 It gives the information from multiple tables. Basically it means When items arrive to warehouse (delievrydate)...

How to write and use Monolog handlers and channels


php,symfony2,monolog,symfony-2.6
I have read some docs here but still not clear to me how to write and use a custom Monolog handler and channel. Let me explain a bit what I want to achieve. I have a custom function and I want that log to be logged into a file called...

Return integer value of age(date) function in Postgres


postgresql
I would like to determine the number of days that an account has been open. Ideally, I would like to compare the return value to an integer(days). i.e I would like to see if age(open_date) > 14 If anyone has any better ideas... Thanks...

PostgreSQL conditional statement


sql,postgresql,if-statement
I need to implement a conditional SELECT statement based on the result of another statement. I don't want to create a function for this purpose, but simply using conditional SELECT. My unsuccessful approach looks as below: DO $do$ BEGIN IF SELECT count(*) FROM table1 < 1 THEN SELECT * FROM...

postgres: using previous row value when current row value is null


postgresql
In my app I need to produce a data output (to be then converted to JSON) for the data in a postgres database (which is actually temperature data gathered by sensors). The data is stored in device_history_log as follows (for data between 11am and noon): row_id;deviceid;sensor_value_raw;last_update 104401;20865735;21.56;"2015-06-10 11:00:14" 104432;493417852;23.9;"2015-06-10 11:00:58"...

Validation of a form before submission


php,validation,symfony2,form-submit
Using Symfony, version 2.3 and more recent, I want the user to click on a link to go to the edition page of an already existing entity and that the form which is displayed to be already validated, with each error associated to its corresponding field, i.e. I want the...

Is there a better way to write this query involving a self select?


sql,postgresql,join,aggregate-functions
I am looking for a "better" way to perform a query in which I want to show a single player who he has played previously and the associated win-loss record for each such opponent. Here are the tables involved stripped down to essentials: create table player (player_id int, username text);...

Symfony2/Twig - iterate over select options


php,symfony2,twig
Usual way of displaying select field is to call {{ form_row(form.doctor_service_id, {'attr':{'class':'form-control'}}) }} I would like to perform two things: Check if this field is actually a select field Iterate over every option (value, name). I know how twig iterator works, I just don't know how to access select options...

How to check what constraint has been violated?


java,sql,postgresql,exception
I'm used to check if the user try to store a valid data with cathcing the ConsratinViolationException, like that: try { //persisitng to a db } catch (ConstraintViolationException e){ //Print message } I'm using PostgreSQL and now I'm under the isssue that the persisitng can violate more than one different...

How to delete replication slot in postgres 9.4


postgresql,replication,postgresql-9.4
I have replication slot which I want to delete but when I do delete I got an error that I can't delete from view. Any ideas? postgres=# SELECT * FROM pg_replication_slots ; slot_name | plugin | slot_type | datoid | database | active | xmin | catalog_xmin | restart_lsn --------------+--------------+-----------+--------+----------+--------+------+--------------+-------------...

Symfony 2 unable to pass entity repository to form


php,forms,symfony2,runtime-error
I have a form with a drop down and a set of checkboxes, i've used entity form field type to get the values via DB. it works with one of the entity but not with the other. i have this code seperately inside AddBadgesType there is NO AddBadges entity <?php...

Postgresql Update JDBC


java,postgresql,jdbc
I want to update table: id integer NOT NULL, "first" character varying(255), "last" character varying(255), age integer, CONSTRAINT registration_pkey PRIMARY KEY (id) using method: void updateTable(String tableName, String columnName, String value, String columnName2, String value2) { try { String sql = "UPDATE " + tableName + " SET " +...

Unable to configure Symfony (3rd party) bundle


php,symfony2,rss
I am faily new to Symfony and I am trying to setup a third party bundle that reads RSS feeds and then insert them into database. The third party bundle I am trying to use is called rss-atom-bundle After reading the instructions I can get the RSS feeds however I...

Set Auth Token manually for different firewall


symfony2
My application has 2 security firewalls "admin" - used by internal staff "account" - used by customers. Previoly I had one action under the account firewall, the action in the controller looks something like this. $user = //get user somehow $token = new UsernamePasswordToken($user, null, 'account', $user->getRoles()); $this->get('security.token_storage')->setToken($token); With this...

Symfony2 creating and persisting entity relationships


php,mysql,symfony2,doctrine2
I have two entities Skin and Email. I want Email to be a part of the Skin entity, however I can't use the console to update schema automatically right now, and that is probably why I can't get the relationship to work. So I want to store all the Emails...

Save a hex-string to PostgreSQL column character varying


postgresql,hex
I want to save hex-string ('A2-5A-47-00-10-00-00-00') into a PostgeSQL database. My column is character varying(30). How can I do this?...

name of value returned from PostgreSQL function


postgresql,plpgsql
I'm working on some code that talks to a PostgreSQL server for the first time (my experience is with SQL Server) and I need help figuring something out. I've always tried to avoid "SELECT * ..." from code, but I can't figure out what the alternative is when calling a...

Symfony/Twig how to render a Route set by anotation?


php,symfony2,routing,twig,url-routing
Let's say I have this code in a controller: <?php namespace Foo\BarBundle\Controller; use Symfony\Bundle\FrameworkBundle\Controller\Controller; use Sensio\Bundle\FrameworkExtraBundle\Configuration as Mvc; /** * @Mvc\Route("/foo/bar") */ class TestController extends Controller /** * @Mvc\Route("/test/{id}", requirements={"id" = "[0-9]{1,6}"}) * @Mvc\Template * * @return view */ public function testAction($id) { return array('test' => $id); } } How...

How adapt the path to the previous and next article


php,symfony2
I am looking for a solution to access in the current article to the previous(<<) or the next article(>>) at the end of the page of an article. I thought to get the current paramater of the url with this : {{ dump(app.request.attributes.get('_route_params')) }} and inject in the path with...