EntitySerializedFieldsBundle: How to query a serialized field?

Hi Oro-team,

is there a sustainable solution to query for a custom field of an entity, which was created using Oro’s EntitySerializedFieldsBundle?

I want to query for buOwner-property using the ShoppingListQueryBuilder():

PHP

/** * Usage: Customer Users * * @param AclHelper $aclHelper * @param bool $selectRelations * @param null|int $websiteId * æparam $buOwner * @return null|ShoppingList * * @throws \Exception */ public function findAvailableForCustomerUser( AclHelper $aclHelper, $selectRelations = false, $websiteId = null, $buOwner = null) { /** @var ShoppingList $shoppingList */ $qb = $this->getShoppingListQueryBuilder($selectRelations); if ($websiteId) { $qb->andWhere($qb->expr()->eq('list.website', ':website')) ->setParameter('website', $websiteId); } // This could not work!!! if($buOwner) { $qb->andWhere($qb->expr()->eq('serializedData', ':buOwner')) ->setParameter('buOwner', $buOwner); } $qb->addOrderBy('list.id', 'DESC')->setMaxResults(1); return $aclHelper->apply($qb)->getOneOrNullResult(); } #

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36

/**      * Usage: Customer Users      *      * @param AclHelper $aclHelper      * @param bool $selectRelations      * @param null|int $websiteId      * æparam $buOwner      * @return null|ShoppingList      *      * @throws \Exception      */     public function findAvailableForCustomerUser(                                     AclHelper $aclHelper,                                     $selectRelations = false,                                     $websiteId = null,                                     $buOwner = null)     {         /** @var ShoppingList $shoppingList */         $qb = $this->getShoppingListQueryBuilder($selectRelations);           if ($websiteId) {             $qb->andWhere($qb->expr()->eq('list.website', ':website'))                 ->setParameter('website', $websiteId);         }           // This could not work!!!         if($buOwner) {             $qb->andWhere($qb->expr()->eq('serializedData', ':buOwner'))                 ->setParameter('buOwner', $buOwner);         }           $qb->addOrderBy('list.id', 'DESC')->setMaxResults(1);           return $aclHelper->apply($qb)->getOneOrNullResult();     } #

This could not work, of course.
– buOwner is a custom ShoppingList-entity-property, but I cannot query for it directly
– buOwner is a attribute of database field serialized_data

I can retrieve the serialized_data-string using myql-decoding like
– SELECT convert(FROM_BASE64(serialized_data) using UTF8)
and build a custom hardcoded query with it …

However, I want to use on board tools.
Is there an easy way to adapt function findAvailableForCustomerUser() in such a manner,
that it is queriing buOwner-property correctly?

Thanks a lot for help!

Kind regrads
Frank

Topic

[Andrey YatsenkoModerator]

This is the main limitations of the serialized fields. No real columns at the database, and as a result no sorting and filtering per this field.

[FrankParticipant]

Hi Andrey,

thanks for your urgent reply!!!

Hm. Makes me sad, a little bit. ;-( So, I have to build a custom query. Hope, this will not cost so much performance.

Kind regards
Frank.

[Author]

Replies