SqlWalker and repository->find() is not connected #4983

New Issue

2026-01-22T14:55:11+01:00

admin commented

2026-01-22 14:55:11 +01:00

Originally created by @viniychuk on GitHub (Jan 20, 2016).

Originally assigned to: @Ocramius on GitHub.

Hi everybody,

I've created a custom SqlWalker and was really disappointed that it works only for DQL, but not for the repository queries.
Let's say this walker changes
SELECT s0_.title as title FROM story s0_ WHERE id=1
to
SELECT UPPER(s0_.title) as title FROM story s0_ WHERE id=1

Imagine that you have a Story entity with only id and title. And you have a database record with a title "A Little Story" and id = 1 in your database.
So when I do request like:

$objects = $doctrine->getEntityManager()->createQueryBuilder()->from('AppBundle:Story', 's')
                ->select('t')->where('s.id = 1')->getQuery()->execute;
dump($objects[0]->getTitle());

I got "A LITTLE STORY".

But when I do:

$object = $doctrine->getRepository('AppBundle:TimeEntry')->find('23');
dump($object->getTitle());

I see "A Little Story", because my worker aren't executed (because of the architecture of the BasicEntityPersister).

Is there a way to achieve "overriding" of part of the SQL for select/insert without overriding the Doctrine core classes?

Thank you!

Originally created by @viniychuk on GitHub (Jan 20, 2016). Originally assigned to: @Ocramius on GitHub. Hi everybody, I've created a custom SqlWalker and was really disappointed that it works only for DQL, but not for the repository queries. Let's say this walker changes `SELECT s0_.title as title FROM story s0_ WHERE id=1` to `SELECT UPPER(s0_.title) as title FROM story s0_ WHERE id=1` Imagine that you have a Story entity with only id and title. And you have a database record with a title "A Little Story" and id = 1 in your database. So when I do request like: ``` $objects = $doctrine->getEntityManager()->createQueryBuilder()->from('AppBundle:Story', 's') ->select('t')->where('s.id = 1')->getQuery()->execute; dump($objects[0]->getTitle()); ``` I got `"A LITTLE STORY"`. But when I do: ``` $object = $doctrine->getRepository('AppBundle:TimeEntry')->find('23'); dump($object->getTitle()); ``` I see `"A Little Story"`, because my worker aren't executed (because of the architecture of the BasicEntityPersister). Is there a way to achieve "overriding" of part of the SQL for select/insert without overriding the Doctrine core classes? Thank you!

admin added the Invalid label 2026-01-22 14:55:11 +01:00

admin closed this issue

2026-01-22 14:55:11 +01:00

admin commented

2026-01-22 14:55:12 +01:00

@Ocramius commented on GitHub (Jan 20, 2016):

Is there a way to achieve "overriding" of part of the SQL for select/insert without overriding the Doctrine core classes?

The correct way to deal with this is to write a custom repository class for your 'AppBundle:TimeEntry', then make the find() use DQL for that particular API endpoint.

Please note that this will not change the behavior of Doctrine\ORM\EntityManagerInterface#find().

Also, the example above seems to be a very weak candidate for DQL walker usage: please do consider using entity private API before applying transformations inside SQL/DQL, if feasible.

@Ocramius commented on GitHub (Jan 20, 2016): > Is there a way to achieve "overriding" of part of the SQL for select/insert without overriding the Doctrine core classes? The correct way to deal with this is to write a custom repository class for your `'AppBundle:TimeEntry'`, then make the `find()` use DQL for that particular API endpoint. Please note that this will not change the behavior of `Doctrine\ORM\EntityManagerInterface#find()`. Also, the example above seems to be a very weak candidate for DQL walker usage: please do consider using entity private API before applying transformations inside SQL/DQL, if feasible.

admin commented

2026-01-22 14:55:17 +01:00

@Ocramius commented on GitHub (Jan 20, 2016):

Closing as Invalid, since this is not an issue, and it is documented at http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/working-with-objects.html#custom-repositories

@Ocramius commented on GitHub (Jan 20, 2016): Closing as `Invalid`, since this is not an issue, and it is documented at http://doctrine-orm.readthedocs.org/projects/doctrine-orm/en/latest/reference/working-with-objects.html#custom-repositories

admin commented

2026-01-22 14:55:19 +01:00

@viniychuk commented on GitHub (Jan 20, 2016):

It was just an example obviously. I need to use a provider specific function (mysql) and that's why I need to modify sql before insert, update and select.

@viniychuk commented on GitHub (Jan 20, 2016): It was just an example obviously. I need to use a provider specific function (mysql) and that's why I need to modify sql before insert, update and select.

admin commented

2026-01-22 14:55:23 +01:00

@Ocramius commented on GitHub (Jan 20, 2016):

@viniychuk a custom repository should do then :-)

@Ocramius commented on GitHub (Jan 20, 2016): @viniychuk a custom repository should do then :-)

admin commented

2026-01-22 14:55:24 +01:00

@viniychuk commented on GitHub (Jan 20, 2016):

Thanks for your reply, but repository won't fix relations-related queries :)
Even though I understand all the downsides of what I’m trying to do I need to implement AES_ENCRYPT/DECRYPT for my project.
In order to do that and keep using doctrine I need to find a way to change almost all queries.
I managed to create a walker that works fine with pure DQL queries and is using annotation on entities to set config for it (e.g. with fields to be encrypted) but I can't find a way to "listen" all queries I need.

Any suggestions?

@viniychuk commented on GitHub (Jan 20, 2016): Thanks for your reply, but repository won't fix relations-related queries :) Even though I understand all the downsides of what I’m trying to do I need to implement AES_ENCRYPT/DECRYPT for my project. In order to do that and keep using doctrine I need to find a way to change almost all queries. I managed to create a walker that works fine with pure DQL queries and is using annotation on entities to set config for it (e.g. with fields to be encrypted) but I can't find a way to "listen" all queries I need. Any suggestions?

admin commented

2026-01-22 14:55:26 +01:00

@Ocramius commented on GitHub (Jan 20, 2016):

If I understand it correctly, you have a lot of scenarios where something like following:

$em->find(Foo::class, 123);

may result in following SQL (pseudo):

SELECT
    AES_DECRPYT(a.col1) AS col1,
    AES_DECRPYT(a.col2) AS col2,
    AES_DECRPYT(a.col3) AS col3
FROM
    tablename a
WHERE
    AES_DECRPYT(a.id) = :id

There is no real way to hook into all queries done by the ORM, plus you will need to mirror the entire encryption/decryption also in INSERT statements (also not supported)

One solution (that may help) is to use custom DBAL types. Be aware that those only work if you have a constant symmetric key for the entire DB/connection, but then you'd simply map columns via @Column(type="my_encrypted_aes_string"), and the conversions would be done by the ORM directly.

I had a database that was using AES_ENCRYPT and AES_DECRYPT at mysql level (due to customer requirements), and ended up changing the requirements (and moved encryption to the storage layer/filesystem instead).

Hope this gives you enough space to go down the rabbit hole :-)

@Ocramius commented on GitHub (Jan 20, 2016): If I understand it correctly, you have a lot of scenarios where something like following: ``` php $em->find(Foo::class, 123); ``` may result in following SQL (pseudo): ``` sql SELECT AES_DECRPYT(a.col1) AS col1, AES_DECRPYT(a.col2) AS col2, AES_DECRPYT(a.col3) AS col3 FROM tablename a WHERE AES_DECRPYT(a.id) = :id ``` There is no real way to hook into all queries done by the ORM, plus you will need to mirror the entire encryption/decryption also in `INSERT` statements (also not supported) One solution (that may help) is to use custom DBAL types. Be aware that those only work if you have a constant symmetric key for the entire DB/connection, but then you'd simply map columns via `@Column(type="my_encrypted_aes_string")`, and the conversions would be done by the ORM directly. I had a database that was using `AES_ENCRYPT` and `AES_DECRYPT` at mysql level (due to customer requirements), and ended up changing the requirements (and moved encryption to the storage layer/filesystem instead). Hope this gives you enough space to go down the rabbit hole :-)

admin commented

2026-01-22 14:55:26 +01:00

@viniychuk commented on GitHub (Jan 20, 2016):

Thanks again, I'll try custom types (I don't remember why but I thought they wouldn't do the trick). I'll let keep you posted from the rabbit hole :)

@viniychuk commented on GitHub (Jan 20, 2016): Thanks again, I'll try custom types (I don't remember why but I thought they wouldn't do the trick). I'll let keep you posted from the rabbit hole :)

admin commented

2026-01-22 14:55:26 +01:00

@viniychuk commented on GitHub (Jan 20, 2016):

Reporting from the rabbit hole =)
So I've tried custom type and here's the deal, it works perfectly for INSERT/SELECT, but I didn't find any ways to change it in the ORDER BY clause.
Here's what I've got:

SELECT t0.id AS id1, UPPER(t0.title) as title1
FROM time_entry t0 
WHERE t0.id = 22 
ORDER BY t0.title ASC

Any new suggestions ?

@viniychuk commented on GitHub (Jan 20, 2016): Reporting from the rabbit hole =) So I've tried custom type and here's the deal, it works perfectly for INSERT/SELECT, but I didn't find any ways to change it in the ORDER BY clause. Here's what I've got: ``` SELECT t0.id AS id1, UPPER(t0.title) as title1 FROM time_entry t0 WHERE t0.id = 22 ORDER BY t0.title ASC ``` Any new suggestions ?

admin commented

2026-01-22 14:55:28 +01:00

@Ocramius commented on GitHub (Jan 20, 2016):

@viniychuk well, ORDER BY doesn't need conversion to DBAL/ORM values, so it's not converted at all.

Since you want the conversion, then in your case it's SELECT f.bar AS baz FROM Foo f ORDER BY baz. Still, I wouldn't rely on this unclear implication.

@Ocramius commented on GitHub (Jan 20, 2016): @viniychuk well, `ORDER BY` doesn't need conversion to DBAL/ORM values, so it's not converted at all. Since you want the conversion, then in your case it's `SELECT f.bar AS baz FROM Foo f ORDER BY baz`. Still, I wouldn't rely on this unclear implication.

admin commented

2026-01-22 14:55:28 +01:00

@viniychuk commented on GitHub (Jan 20, 2016):

@OskarStark yes, I'm just wondering why don't we have ORDER BY title1 instead of ORDER BY t0.title ? That would solve all my issues :)

@viniychuk commented on GitHub (Jan 20, 2016): @OskarStark yes, I'm just wondering why don't we have `ORDER BY title1` instead of `ORDER BY t0.title` ? That would solve all my issues :)

admin commented

2026-01-22 14:55:29 +01:00

@Ocramius commented on GitHub (Jan 20, 2016):

@viniychuk referencing something in the selection or in the root table makes a big difference

@Ocramius commented on GitHub (Jan 20, 2016): @viniychuk referencing something in the selection or in the root table makes a big difference

Sign in to join this conversation.

Branches Tags

1 Participants

Notifications

Due Date

No due date set.

Dependencies

No dependencies set.

Reference: doctrine/archived-orm#4983