Pods: Traversal performance issue

[rpall]

I'm working on optimizing the performance of a site and i noticed that the following line adds a whopping 95 queries for some reason:

$request['customer_id'] = $requests_pods->field( 'customer.ID' );

Any suggestions on how to optimize this? I understand that it has a separate relationships table that it needs to query. Is there a way perhaps to add the relationship id in the same table? Or any other suggested practices?

Customer is a relationship field to Users WP object on the Requests pod.
Requests pods type is ACT

[sc0ttkclark]

The best way to handle this by far is selecting this data in your find() call itself.

$params = array(
    'select' => 't.*, customer.ID AS customer_id',
    // other args here
);

$requests_pods->find( $params );

[sc0ttkclark]

We get this question a lot, we should definitely add a big section on optimization or a big CTA on find() and field() docs pages that goes to an in depth optimization walkthrough doc. cc @jimtrue

[pglewis]

We get this question a lot, we should definitely add a big section on optimization or a big CTA on find() and field() docs pages that goes to an in depth optimization walkthrough doc.

I also like the idea of a new "prefetch" array of fields that Pods could use to automatically do deeper nested joins. People that need this optimization could easily opt-in without any more fiddling than creating an array of related field names.

[sc0ttkclark]

I'm game for having a 'prefetch' or 'fetch_related' parameter with configs like:

'prefetch' => array(
    'customer' => array(
        'ID',
    ),
    // Or alternatively
    'customer' => 'ID',
),

And we would just alias the resulting fields as {relationship_field}_{field} (customer_ID) or namespace it like %customer_ID or whatever we want when calling it from field()

[pglewis]

'select' => 't.*, customer.ID AS customer_id',

Also, I'm not 100% sure this will work like this... doesn't Pods use underscore internally to replace dot notation fields? In the off chance my memory is correct there you may need to alias it AS the_customer_id or whatever

[sc0ttkclark]

it does use _ for dot notation but only as it traverses on relationship fields, so customer.another.ID would get aliased as customer_another for the post table there.

[pglewis]

I should know better than to second-guess The Scott :D. I just remembered I had run into issues in the past trying to alias with a similar format.

[sc0ttkclark]

If you have a field called customer_id and you have a relationship to customer and you want to prefetch customer.id then customer_id2 will be returned by MySQL for the alias even though it is told to be customer.id AS customer_id. That's probably the main problematic case you're remembering.

[lougreenwood]

Forgive my ignorance with the 'select' feature, but doesn't it presuppose that the use of a query is known before the query is made?

Is it possible to optimise for a scenario where the use of a query is unknown (so the selects can't be specified)?

[sc0ttkclark]

By default, as long as you select t.* (any pod) and d.* (if it's a table-based post type), it will let you use everything normally. You can optimize specific selects as needed, such as that customer.ID as customer_id case. In that case, you can utilize $pod->field( 'customer_id' ) but still call $pod->field( 'customer.display_name' ) (assuming customer is a user relationship).

So you can call anything as you would normally, this custom select stuff just makes it easy for you to optimize any/all that you'd like at any given point, with the flexibility to still call things the normal way.