XF 2.2 fetch() not returning full results

[mjda]

I've hit an issue here that really has me scratching my head.

I'm setting a variable like this: $myList = $myRepo->getMyListForIndex()->fetch();

Here's what that function looks like:

public function getMyListForIndex()
    {
        $finder = $this->finder('Vendor\Addon:Class');
        $finder
            ->where('attach_count', '>', 0)
            ->with(['Attachment'])
            ->order('my_id', 'desc')
            ->limit(2);
            
        return $finder;
    }

Everything seems to be working fine, as in I get no errors. The issue, though, is that $myList only has 1 result, instead of 2. However, if I copy/paste the query from the debug page into phpMyAdmin, I get 2 results, as expected.

Anyone have any ideas as to what could be happening with that other result when I run $myList = $myRepo->getMyListForIndex()->fetch();?

It's probably also worth noting that if I use ->limit(10) then I only get 8 results, unless I copy/paste into phpMyAdmin where I get all 10 results.

 

[Kirby]

I"d assume that your Attachment relation is the problem as it joins xf_attachment multiple times for an entity - this won't work.
When hydrating the raw query results into entities, the entity manager would create just one entity.

 

[mjda]

I"d assume that your Attachment relation is the problem as it joins xf_attachment multiple times for an entity - this won't work.
When hydrating the raw query results into entities, the entity manager would create just one entity.

You may be on to something with that, but in this case it doesn't make sense to me. The lone result that does show up with ->limit(2) is one that has multiple attachments. Why would it work for that one, and not any of the others?

Here's what my relation looks like, if that helps at all:

'Attachment' => [
                'entity' => 'XF:Attachment',
                'type' => self::TO_ONE,
                'conditions' => [
                    ['content_type', '=', 'my_type'],
                    ['content_id', '=', '$my_id'],
                ],
                'with' => 'Data'
            ]

 

[Kirby]

The relation is exactly as I had expected and does cause your problem.

If your entity has two attachments, the resulting query would generate two result rows:
The first part of those two rows would be identical (=your entity), the second part would be from xf_attachment and the 3rd part from xf_attachment_data.

When hydrating the results, only one of those rows (remember, they have identical my_id) would be added to the collection.

The Finder does accept your code because you've defined the relation as TO_ONE.

This is incorrect, it is TO_MANY - there might be multiple records in xf_attachment for one my_id and content type my_type.

If you correct that you'll get an exception telling you that TO_MANY relations can't be used for joins

 

[mjda]

You are right. If I remove the ->with('Attachment') from the repo everything works perfectly. That only confuses me even further, though, because now it's working fine, but has to call an extra query for each entity to get the Attachment relation.

 

[Kirby]

You need to get familiar with SQL joins

What you want to achieve simply won't work with a JOIN.

Doing an individual query for every entity attachments is inefficient; take a look at how this is being done for forum post attachments.

 

[mjda]

You need to get familiar with SQL joins

What you want to achieve simply won't work with a JOIN.

Doing an individual query for every entity attachments is inefficient; take a look at how this is being done for forum post attachments.

Ha! You're probably right about that. There is plenty of stuff I should probably better familiarize myself with where coding is concerned.

I know how to pull all the attachments in. For that I just use the $attachmentRepo->addAttachmentsToContent() function. However, for what I'm trying to do I need to only display a single attachment for each entity, which is why I thought I'd try this.

What I'm still confused about, though, is how one of the results that is being returned was one of the results that has 2 attachments. One of the ones that is missing is one that only has 1 attachment. So that's just the opposite of how you're saying it should be. Also, it's not consistently holding back the same entities. If I change it to ->limit(3) then the results that are showing up are both of the ones that should show up when the limit is 2.

That said, it's obviously a problem with that relation, however strange it is, so I'll try to find a better solution. Thanks for your help!

 

[mjda]

Well, I'm not sure why I didn't think of this sooner but I was able to figure out how to do what I needed to do. I only wasted a full day just to figure out that this won't work.

I'm now grabbing all the attachments, with $attachmentRepo->addAttachmentsToContent(), and using the following code to show only a single one for each entity within my loop.

<xf:set var="$attachment" value="{{ $my.Attachments|first }}" />
<xf:macro template="attachment_macros" name="attachment_list_item" arg-attachment="{$attachment}" arg-canView="{{ true }}" />

 

[briansol]

What kirby tried to explain above is the answer to your question.

you can't use TO_MANY in the relations. it just doesn't work.


so, in phpmyadmin, you will get two rows like

id name leftjoin1
id name leftjoin2

but in xf, you will only get
id name leftjoin

 

[Kirby]

However, for what I'm trying to do I need to only display a single attachment for each entity, which is why I thought I'd try this.

If this is what you want to do:
Add a field main_attachment_id to your entity and use that to join that single attachment entity with your finder.

Loading and looping over all attachments to filter out one is doable, but kinda inefficient.

What I'm still confused about, though, is how one of the results that is being returned was one of the results that has 2 attachments.
[...]
That said, it's obviously a problem with that relation, however strange it is

The behaviour you are experiencing isn't strage, nor inconsistent or the opposite of what I had written before - it is the direct consequence of the data you were querying.

As I've already pointed out before (and was repeated by @briansol): TO_MANY simply does not work as a finder relation.

To understand this you need to understand how JOINs do work in SQL.

Vastly simplified (I've left out all fields that are not relevant to illustrate the issue) you are dealing with 3 tables in your finder query:

xf_my_entity​

my_id	title	attach_count
1	Title 1	2
2	Title 2	1
3	Title 3	2
4	Title 4	1
5	Title 5	1
6	Title 6	1
7	Title 7	1
8	Title 8	1
9	Title 9	1
10	Title 10	1

xf_attachment​

attachment_id	data_id	content_type	content_id
1	1	my_type	1
2	2	my_type	1
3	3	my_type	2
4	4	my_type	3
5	5	my_type	3
6	6	my_type	4
7	7	my_type	5
8	8	my_type	6
9	9	my_type	7
10	10	my_type	8
11	11	my_type	9
12	12	my_type	10

xf_attachment_data​

data_id	filename
1	content_1_image_1.jpg
2	content_2_image_1.jpg
3	content_2_image_2.jpg
4	content_3_image_1.jpg
5	content_3_image_2.jpg
6	content_4_image_1.jpg
7	content_5_image_1.jpg
8	content_6_image_1.jpg
9	content_7_image_1.jpg
10	content_8_image_1.jpg
11	content_9_image_1.jpg
12	content_10_image_1.jpg

Without a LIMIT, the raw result set when querying by my_id ASC would be

xf_my_entity:my_id​	xf_my_entity:title​	xf_my_entity:attach_count​	xf_attachment:attachment_id​	xf_attachment:data_id​	xf_attachment:content_type​	xf_attachment:content_id​	xf_attachment_data:data_id​	xf_attachment_data:filename​
1​	Title 1​	2​	1​	1​	my_type​	1​	1​	content_1_image_1.jpg​
1​	Title 1​	2​	2​	2​	my_type​	1​	2​	content_1_image_2.jpg​
2​	Title 2​	1​	3​	3​	my_type​	2​	3​	content_2_image_1.jpg​
3​	Title 3​	2​	4​	4​	my_type​	3​	4​	content_3_image_1.jpg​
3​	Title 3​	2​	5​	5​	my_type​	3​	5​	content_3_image_2.jpg​
4​	Title 4​	1​	6​	6​	my_type​	4​	6​	content_4_image_1.jpg​
5​	Title 5​	1​	7​	7​	my_type​	5​	7​	content_5_image_1.jpg​
6​	Title 6​	1​	8​	8​	my_type​	6​	8​	content_6_image_1.jpg​
7​	Title 7​	1​	9​	9​	my_type​	7​	9​	content_7_image_1.jpg​
8​	Title 8​	1​	10​	10​	my_type​	8​	10​	content_8_image_1.jpg​
9​	Title 9​	1​	11​	11​	my_type​	9​	11​	content_9_image_1.jpg​
10​	Title 10​	1​	12​	12​	my_type​	10​	12​	content_10_image_1.jpg​

With LIMIT 2 you would get the first two result rows (eg. two times your entity ID 1).
So even if the entity manager was able to correctly hydrate this into one MyEntity with two attachments, it would just be one MyEntity.

With LIMIT 3 your would get entity IDs 1 and 2 and with LIMIT 10 you would get entity IDs 1 to 8 accordingly.

But this is not what you want - you do not want to limit the number of rows, but the number of entities returned.
This can't be controlled through a a query; LIMIT can only affect the number of rows being returned, in case of TO_MANY relations (like the one you are using) the records from the first table do get "duplicated" with an unknown multiplier (that is determined by the product of the number of rows for that row from the first table in all joined tables).

I hope this does make it clear, if not please read up on JOINs

 

[mjda]

If this is what you want to do:
Add a field main_attachment_id to your entity and use that to join that single attachment entity with your finder.

Loading and looping over all attachments to filter out one is doable, but kinda inefficient.

This was the first thing I considered doing, but I (lazily) didn't want to write the code involved in that. Then I have to write code for adding that, changing it upon attachment add/edit/delete. I think the way I'm doing it now, with the $entity.Attachments|first will work just fine for what I need until I decide later that I want to take the time to do it a better way. Most of the time there will only be a single attachment anyways.

Thanks, @Kirby, for your explanation on how this is working. I really do appreciate you taking the time to write that explanation out.