How to get relational fields data in one single query

I want to optimize the speed of my website and get all data including my relational (pick) fields data in one single query. How to do that?

Below my findings so far:

I noticed that when doing $pod->field('relational_field'); a additional query will run.

Assuming a pod with a name field and a categories field which is relational (pick):

// This code below will behave unexpected 
$pod = pods( 'pod', array(
   'where' => array(...),
   'select' => array('t.name', 'category.name AS category_name')
));

Assuming that you have created one single Pod item linked to two categories, you will get two results, because of the category.name AS category_name in the select option. If you had one category assigned to the pod, you will get one result.

The result is as follows:

array (size=2)
  0 => 
    object(stdClass)[2928]
      public 'name' => string 'My pod 1' (length=8)
      public 'category' => string 'Category 1' (length=10)
  1 => 
    object(stdClass)[2929]
      public 'name' => string 'My pod 1' (length=8)
      public 'category' => string 'Category 2' (length=10) // <-- The difference is only in category

What I really want is the data of the two categories in that 'category' key as an array. Is that possible?

(originally posted on Pods.io)

Solution

This is close. The 'select' parameter should be a string as you would supply to a SQL SELECT clause, not an array of fields. Same with the 'where' parameter, so:

$pod = pods( 'pod', array(
   'where' => 'where params as a string',
   'select' => 't.name,category.name AS category_name')
));

When you retrieve the data with field() or display(), use the aliased 'category_name' instead of the dot notation.