Quoting Strategy for MySQL reserved keywords column names

Using Neos & Flow Flow App Development
1

Hi there

I encountered a problem with autogenerated column names which equal to reserved mysql keywords. Imagine: I have two models, one called Group and the other called GroupMembership. There’s a bidirectional 1:n relation between Group and GroupMembership.

The reference for every GroupMembership to its Group is stored in a property called group (in the GroupMembership domain model).

When I try to create and persist a new domain object, doctrine orm generates an INSERT statement, which leads to an exception similar to this:

An exception occurred while executing ‘INSERT INTO vendor_package_domain_model_groupmembership (persistence_object_identifier, person, group) VALUES (?, ?, ?)’ with params [“4600992e-d795-4bc3-9e97-2ed653d791bc”, “d3e72049-1c7e-45d6-8cfe-8fe39938f9fc”, “5c6680ff-8fc6-44fe-b412-1360a6ceefe3”]

The error in this generated sql statement originates from the column name group which is not enclosed in backticks! When I copy this statement into my sql client and add the backticks around group, everything works.

I already found (and tried to follow) this part of the flow documentation: http://flowframework.readthedocs.io/en/stable/TheDefinitiveGuide/PartIII/Persistence.html#customizing-doctrine-entitymanager
Unfortunately, for me this does not help. I tried to wire both mentioned signal to my corresponding slots. In these slots I did:
$config->setQuoteStrategy(new DefaultQuoteStrategy());
or
$config->setQuoteStrategy(new AnsiQuoteStrategy());

This calls didn’t have any effects on my code, e.g. the generation of the sql statement in question.

I also found this topic in the forum regarding reserved keywords as column names: @ORM\Column(name="xyz") not working

My questions are:

  1. Is it possible to use property names such as group or name in domain models?
  2. What is required to do for using such property names?
  3. Is there a special quoting strategy required?
  4. Could you provide a working example?

Thanks and kind regards,
Peter

1 Like
2

Since this post just got published in todays summary email, I feel oblige to contribute my latest results on this topic:

  1. Don’t try to use QuotingStrategy - even the doctrine people recommend to use this feature very sparely.

  2. If you try to assign a custom/different column name to any property which holds a reference on another entity, use @ORM\JoinColumn(name=“xyz”) instead of just @ORM\Column(name=“xyz”).

  3. If you’d like to give any property a custom/different column name --> you need to do that before generating the first migration. Otherwise, this annotation seems to be ignored when calculating differences between your domain model and the database table. Workaround: Delete property => generate migration => execute migration => add property again (with different name or @ORM\Column annotation) => generate migration => execute migration => be happy

  4. Using “quoted column names” in annotations as for example @ORM\ManyToOne(mappedBy="'xyz'") (the apostrophes being backticks!) does not work - and seems no to be required.

Kind regards,
Peter