Symfony2 - how to search json_array field in findBy

Question

Assume i have an array of data contacts,

$cont = array("123-123-123","[email protected]");

Which i stored in symfony2 doctrine field as json_array type

$person->setContacts($cont); //Which automatically converts into json

Now my problem is, while searching the person by contact,

$cont['contacts'] = array("123-123-123","[email protected]");
or
$cont['contacts'] = json_encode(array("123-123-123","[email protected]"));
$person->findBy($cont);

Does not yield the correct result, is there any other method to retrieve the data by json_array field,sorry if the question is too basic.

It is auto generated by doctrine, while we creating the entity — senK
– senK, Commented May 26, 2014 at 7:25
Serializing data that you need to query is not a good option. Why don't you create a separate entity Contact? Or use Value Objects like in @Guilro's answer? — Jasper N. Brouwer
– Jasper N. Brouwer, Commented May 28, 2014 at 17:33

jillro · Accepted Answer · 2014-05-28 07:21:20Z

4

+50

You should not use JSON to store in database if you want to perform a search on it.

What you are trying to do is basically to persist a value object (you should create a real Contact value object instead of using array).

Then you cam find here several solution to persist value object. http://rosstuck.com/persisting-value-objects-in-doctrine/

The first one (map it yourself) is the same as ZhukV and is applicable even if you keep an array.

answered May 28, 2014 at 7:21

jillro

4,6092 gold badges23 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Robin Over a year ago

This is not correct in general. For example there is the jsonb in postgres. You can query on that efficiently.

Moisés Márquez · Accepted Answer · 2015-01-20 11:25:36Z

2

You can use the json (or jsonb) data type if you are using PostgreSQL > 9.3.

You can read the documentation here (Datatype JSON Documentation).

answered Jan 20, 2015 at 11:25

Moisés Márquez

3232 silver badges8 bronze badges

Comments

ZhukV · Accepted Answer · 2014-05-26 07:22:14Z

-2

Searching by JSON - bad idea for SQL databases. You can create a another fields (phone and email).

All values before bind to PDO, auto converting in DBAL\Types, and you must set the really data as parameter (object, array, etc...).

$cont['contacts'] = array("123-123-123","[email protected]");
$person->findBy($cont);

This is a working code. You can see to profiler for view full SQL query.

answered May 26, 2014 at 7:22

ZhukV

3,2186 gold badges30 silver badges40 bronze badges

Collectives™ on Stack Overflow

Symfony2 - how to search json_array field in findBy

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related