XF 2.1 Finder -> with less fields

[Robert9]

I use finder to get resources and a second table;
now the result has around 50 cols and i need only 10.
How can i reduce the query to the needed fields only, please?

 

[Robert9]

Next problem:

I use this:

$resources = $app->finder('XFRM:ResourceItem')
->where('resource_category_id', 1)
->with('Plan')
->order('title','ASC')
->limit('60')
->fetch();

when i debug i get:

SELECT xf_rm_resource., Plan_Plan_1.
FROM xf_rm_resource
LEFT JOIN Plan AS Plan_Plan_1 ON (Plan_Plan_1.resource_id = xf_rm_resource.resource_id)
WHERE (xf_rm_resource.resource_category_id = 1)
ORDER BY xf_rm_resource.title ASC

In short: fetch some resources and left join some data from a second table Plan.

The result in phpMyAdmin looks like it should:

all cols from xf_rm_resource and all fields from table Plan.


But $resources has only the content from ResourceItem.

How can i tell the finder
a) to put all fields to the $result and
b) to fetch only the fields i need

 

[Robert9]

Finally i sit here for hours to find a way to use relations and getters to get a query that doesnt fill my $results.
Why i schould not use just a normal query and be finished in ten minutes?

 

[Liam W]

Relations are their own entity, and the data can be modified directly on it. To get your Plan entity from the ResourceItem you'd use $resources->first()->Plan (you can also use fetchOne on the Finder instance to fetch a single Entity).

XenForo 2 generally always uses full entities when dealing with data.

 

[Robert9]

Thank. Unfortunately i dont understand your help.
I have

$resources = $app->finder('XFRM:ResourceItem')
->where('resource_category_id', 1)
->with('Plan')
->order('title','ASC')
->limit('60')
->fetch();


I dont want to have query for Plan. I need ResourceItems left joined a row from Plan.
And the mysql query is correct

SELECT xf_rm_resource., Plan_Plan_1.
FROM xf_rm_resource
LEFT JOIN Plan AS Plan_Plan_1 ON (Plan_Plan_1.resource_id = xf_rm_resource.resource_id)
WHERE (xf_rm_resource.resource_category_id = 1)
ORDER BY xf_rm_resource.title ASC

but there is nothing from Plan in $resources and alos there is nothing in Plan

                    [_relations:protected] => Array
                        (
                            [Plan] =>
                        )

 

[Liam W]

That would be because the Plan row corresponding to the relation key couldn't be found. How is the Plan relation defined in the ResourceItem structure? Are you using the correct key?

 

[Robert9]

        $structure->relations['Plan'] =
        [
            'entity' => 'Robert9\Example:Plan',
            'type' => self::TO_ONE,
            'conditions' => 'resource_id',
            'primary' => true
        ];

Plan is used in the query, so the relation is ok, i hope.

 

[Liam W]

Do you have a row in the table for your Plan entity with a resource_id equal to 1? Is the primary key of that table resource_id?

 

[Robert9]

Table Plan is like => resource_id, value1, value2

I want to fetch the resource and add the fields from table Plan

 

[Robert9]

Yes. If i use the query from above in phpMyAdmin i get everything i want:
all fields from resource and all fields from plan (and resource_id twice times)

resource_id is the primary in both tables.

The query is correct, the result in mysql is correct, but results->Plan is empty and no extra data in results->resourceitem

 

[Liam W]

Are you sure the Plan relation is missing? Have you tried using XF::dump() on $resource->Plan directly?

 

[Robert9]

I tried to make everything like in the wordcount add-on; there is post and a new table with post_id, wordcount.
I have resource and a new table with resource_id and something.

 

[Robert9]

I use print_r($results), it is the same.
_relation is empty
_values has 35 fields = resource-table

I will try xdebig now to follow the script, but i am without hope.

 

[Robert9]

Puuuuuuhhhhh! Typo in structure of Plan.
Thank you very much.

 

[Robert9]

Next problem:

I want to fetch resources leftjoin my new table Plan. This is solved.
But now i want to add where Plan.field = 1.
or where Plan.field = 0.

but how?