Thanks.
I have tried the new DQL and results are as follows:
- I now have a one-dimensional array with 4 indices, each holding the
"Item" Entity
- One "Product" entity is loaded as part of the above entity (one for
each index location), under "product" member variable.
- The "Product" is no longer a proxy
It now looks more properly OO-loaded. That is good.
But I do note that I come across the same issue in that the format changes
- it is now a single-dimensional array rather than double.
That is... if I use "SELECT i, p", I get a one-dimensional array of Item
Objects (which have Product objects loaded).
If I use "SELECT i as item, p as product", I get a one-dimensional array,
where each index contains a one-dimensional array with key of "item", which
points to Item object, and Item object has a property "product" that points
to Product object.
The format changes are generally not a big deal for me thankfully, but I do
need to go into the code and fix up any and all references to how things
are called. This can be a pain. That is to say, *different queries are not
interchangeable* when it comes to using results.
Here is retrieval of same "product.model" data (using suggested DQL but
varying SELECT params):
//3 different ways to retrieve the same data by varying SELECT params
$ret[0]->getProduct()->getModel(); //SELECT i, p
$ret[0]['item']->getProduct()->getModel(); //SELECT i as item, p as product
$ret[0]['model']; //SELECT i as item, p.model as
model
Looking this I actually prefer the plain "SELECT i, p" better -- it's more
OO, just an array of Objects with no arrays in the way other then the
top-level one
I should also note that due to the association here, using "SELECT i, p as
product" and "SELECT i, p" and "SELECT i, p as fnord_bottle" all give
identical results. That is, "as " is ignored for p.
*The "May be a bug":*
As far as the "bug", yep, still there when using original query. Generated
SQL looks something like this:
SELECT
i0_.id AS id_0,
p1_.id AS id_32,
p1_.model AS model_33,
i0_.product_id AS product_id_52
FROM item i0_
INNER JOIN product p1_ ON (i0_.product_id = p1_.id)
WHERE ...;
And Result Set from SQL looks like so:
+------++-------+----------++---------------+
| id_0 || id_32 | model_33 || product_id_52 |
+------++-------+----------++---------------+
| 421 || 159 | AA || 159 |
| 422 || 159 | AA || 159 |
| 423 || 158 | BB || 158 |
| 424 || 157 | CC || 157 |
+------++-------+----------++---------------+
4 rows in set (0.00 sec)
Also, since original post, for the "bug" query, I have changed it to use
"WITH", with same messy results as before "(all mixed results in a
one-dimension array)"
INNER JOIN ' . Product::class . ' p
WITH i.product = p.id
For now I liked the suggested DQL the best, as so:
SELECT i,p
FROM ' . Item::class . ' i
JOIN i.product p
WHERE ...
Dennis
On Monday, May 9, 2016 at 3:10:03 PM UTC-4, Marco Pivetta wrote:
>
> Hi Dennis,
>
> On 6 May 2016 at 21:11, Dennis Fedco <[email protected] <javascript:>>
> wrote:
>
>> *Peculiarity #1*
>>
>> I have some code, like so:
>>
>>
>> $query = $this->getEntityManager()->createQuery('
>> SELECT i, p
>> FROM ' . Item::class . ' i
>> INNER JOIN ' . Product::class . ' p
>> WHERE
>> i.productId = p.id
>> ');
>> $result = $query->getResult();
>>
>> foreach ($result as $row)
>> print get_class($row) . ' - ' . $row->getId() . "\n"
>>
>>
> The "WHERE i.productId = p.id" seems to be incorrect here. Is there an
> association between Product and Item? If so, then you can simply rewrite
> the query as following:
>
> 'SELECT i, p
> FROM ' . Item::class . ' i
> JOIN i.product p'
>
> This already hydrates the Product instance into the Item objects being
> retrieved.
>
>
>> I get this output, which is a one-dimensional array (I've added array
>> indices for clarity):
>>
>> [0] => Application\Entity\Item - 284
>> [1] => DoctrineProxies\__CG__\Application\Entity\Product - 59
>> [2] => Application\Entity\Item - 302
>> [3] => Application\Entity\Item - 288
>> [4] => DoctrineProxies\__CG__\Application\Entity\Product - 58
>> [5] => Application\Entity\Item - 292
>> [6] => DoctrineProxies\__CG__\Application\Entity\Product - 57
>>
>> I found this to be peculiar because I am requesting both "i", and "p" in
>> my select statement but I get count of four of "Item" returned and count of
>> 3 of "Product".
>> Namely, I would typically expect 4 and 4. That is, I'd expect this line
>> to be present:
>> [2.5] => DoctrineProxies\__CG__\Application\Entity\Product - 59 --- That
>> line is *missing* in my output.
>>
>> (Index of 2.5 to be interpreted as a made up index to signify that the
>> line should have been the one between indices 2 and 3)
>>
>> I suspect it is because the index of the Product happens to be the same
>> in the join, 59 in this case)
>> But then I have to craft my code to accommodate for this, because
>> normally I'd expect the Item and Product to alternate.
>>
>> Is this behavior "by design"?
>>
>
> Can you please check the SQL results and compare them to the hydrated
> result? As it stands, from this perspective (reader) I can't say what the
> expectations would be.
> The output is indeed messy due to how you decided to hydrate it (fetch
> join, but on an arbitrary join)
>
>
>> *Peculiarity #2*
>>
>> If I change the above DQL to say "p.model" instead, keeping everything
>> else the same. Like so:
>> SELECT i, p.model
>>
>> Instead of an array of N Objects or Proxies, I now get an array of
>> arrays. See below (with indices for clarity):
>> [0][0] => Application\Entity\Item - 284
>> [0][1] => AA (model for id==59)
>> [1][0] => Application\Entity\Item - 302
>> [1][1] => AA (model for id==59)
>> [2][0] => Application\Entity\Item - 288
>> [2][1] => BB (model for id==58)
>> [3][0] => Application\Entity\Item - 292
>> [3][1] => CC (model for id==57)
>>
>> The *inconsistency* between the structure of result sets gotten from
>> similar DQL statements is confusing and unexpected to me.
>>
>
> This seems to be the correct result instead. Try `i as item, p.model as
> model`, should produce better results.
>
>
>>
>> Is this behavior by design?
>>
>
> The first result (all mixed results in a one-dimension array) is most
> likely NOT what we want as a result, and may be a bug. I do not think this
> will be fixed soon though, as somebody may rely on it at this time.
> Instead, we might get rid of this kind of behavior in the next major
> iteration.
>
>
>> Am I using ORM correctly in this such cases?
>>
>
> I think the lack of a fetch-join over an association is what is the most
> confusing bit about the initial query. Please try applying the DQL I
> suggested above.
>
>
>> My goal is to get the contents of Item, but also pull model from Product
>>
>
> Yes, that would be a fetch-join operation over an association.
>
> Cheers,
>
> Marco Pivetta
>
> http://twitter.com/Ocramius
>
> http://ocramius.github.com/
>
>
--
You received this message because you are subscribed to the Google Groups
"doctrine-user" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at https://groups.google.com/group/doctrine-user.
For more options, visit https://groups.google.com/d/optout.
