Documentation request -- how to orderBy on an embeddable object

[jackdpeterson]

How would one perform a sort operation on a collection where the entity has an embeddable value object?

e.g., let's assume we have a currency object in there with a field, "amount" that's an integer.

&order-by[0][field]=priceObj_amount&order-by[0][direction]=desc

returns a semantical error, like:

"detail": "[Semantical Error] line 0, col 138 near 'priceObj_amount': Error: Class Example\Entity\Barcode\Item has no field or association named priceObj_amount",

[TomHAnderson]

Hi Jack,

First let me point you to a fork of this project for ORM only: https://github.com/API-Skeletons/zf-doctrine-orm-querybuilder I did this yesterday but all the tests are passing. Still another set of eyes would be welcome.

You can specify an alias e.g. https://github.com/API-Skeletons/zf-doctrine-orm-querybuilder/blob/master/src/OrderBy/FieldOrderBy.php#L16 but I don't think that's what you're going for. I think you have a function on your entity called priceObj_amount().

The Doctrine QueryBuilder is used to create DQL which is transpiled to SQL. If you cannot get the value through SQL then you can't get it through DQL.

Notice your error says no field or association. This is correct.

[jackdpeterson]

Okay, so I've narrowed this down a little bit and I think the issue is just with the orderBy tidbits.

Example that works in query string (provides results w/ an amount > 2500):
&filter[0][type]=eq&filter[0][field]=isNew&filter[0][value]=0&filter[1][type]=gte&filter[1][field]=priceObj.amount&filter[1][value]=2500

What doesn't work is sorting on the field's order-by.

That yields the semantical error.

Below is the DQL that is generated when using priceObj.amount as the 'field'.
An exception occurred while executing 'SELECT DISTINCT id_0 FROM (SELECT b0_.id AS id_0, b0_.feedProvider AS feedProvider_1, b0_.isNew AS isNew_2, b0_.title AS title_3, b0_.description AS description_4, b0_.GSIN AS GSIN_5, b0_.createdAt AS createdAt_6, b0_.url AS url_7, b0_.imageUrl AS imageUrl_8, b0_.fpExtendedAttributes AS fpExtendedAttributes_9, b0_.priceObj_amount AS priceObj_amount_10, b0_.priceObj_currencyCode AS priceObj_currencyCode_11, b0_.location_latitude AS location_latitude_12, b0_.location_longitude AS location_longitude_13 FROM barcode_product_result b0_ WHERE b0_.GSIN = ? AND b0_.isNew = ? AND b0_.priceObj_amount >= ?) dctrn_result ORDER BY priceObj_amount_10 DESC' with params ["09780321712943", 0, "2500"]:\n\nSQLSTATE[HY000]: General error: 3065 Expression #1 of ORDER BY clause is not in SELECT list, references column 'dctrn_result.priceObj_amount_10' which is not in SELECT list; this is incompatible with DISTINCT",

[TomHAnderson]

You can't use ANYTHING.anyfield. You have to use 'alias':
https://github.com/API-Skeletons/zf-doctrine-orm-querybuilder/blob/master/src/OrderBy/FieldOrderBy.php#L16

On Wed, Aug 3, 2016 at 11:25 AM Jack Peterson [email protected]
wrote:

Okay, so I've narrowed this down a little bit and I think the issue is
just with the orderBy tidbits.

Example that works in query string (provides results w/ an amount > 2500):

&filter[0][type]=eq&filter[0][field]=isNew&filter[0][value]=0&filter[1][type]=gte&filter[1][field]=priceObj.amount&filter[1][value]=2500

What doesn't work is sorting on the field's order-by.

That yields the semantical error.

Below is the DQL that is generated when using priceObj.amount as the
'field'.
An exception occurred while executing 'SELECT DISTINCT id_0 FROM (SELECT
b0_.id AS id_0, b0_.feedProvider AS feedProvider_1, b0_.isNew AS isNew_2,
b0_.title AS title_3, b0_.description AS description_4, b0_.GSIN AS GSIN_5,
b0_.createdAt AS createdAt_6, b0_.url AS url_7, b0_.imageUrl AS imageUrl_8,
b0_.fpExtendedAttributes AS fpExtendedAttributes_9, b0_.priceObj_amount AS
priceObj_amount_10, b0_.priceObj_currencyCode AS priceObj_currencyCode_11,
b0_.location_latitude AS location_latitude_12, b0_.location_longitude AS
location_longitude_13 FROM barcode_product_result b0_ WHERE b0_.GSIN = ?
AND b0_.isNew = ? AND b0_.priceObj_amount >= ?) dctrn_result ORDER BY
priceObj_amount_10 DESC' with params ["09780321712943", 0,
"2500"]:\n\nSQLSTATE[HY000]: General error: 3065 Expression #1
#1 of
ORDER BY clause is not in SELECT list, references column
'dctrn_result.priceObj_amount_10' which is not in SELECT list; this is
incompatible with DISTINCT",

—
You are receiving this because you commented.

Reply to this email directly, view it on GitHub
#33 (comment),
or mute the thread
https://github.com/notifications/unsubscribe-auth/AAeJYMVR4o6SIDgHBpTXgk4vgKuvJzTbks5qcNwigaJpZM4JbT8b
.

[jackdpeterson]

After trying various forms of aliasing (specifying in the filter options, setting the setting the object I'm pulling in as 'row') as well as attempting directly creating the DQL through query Builder ... I think I'm coming to the conclusion that one cannot perform an order-by operation on an embeddable's field value. If I create a query directly using DQL ... it works ... but using querybuilder to do so ... not so much.

Any thoughts would be much appreciated.

http://pastie.org/pastes/10929903/text?key=nqyfrbl53ot75hg0fqvg is the pastie where I have the entity and the value object defined as well as some additional info related to this.

[jackdpeterson]

This is not a problem with this module, rather it's an issue with doctrine and MySQL 5.7:

doctrine/orm#5622