I need id as key of result set from find()

1

Hi!
First, find(‘list’) doesn’t do job for me, it doesn’t return entities.
Let’s say I’ve got table:

id vakue1 … others columns, that I need in my result
50 a
51 b
63 c

standard result is
key of array
0 => [id=>50,value=>a, others columns…]
1 => [id=>51,value=>b, others columns…]
2 => [id=>63,value=>c, others columns…]

what I want is:
50 => [id=>50,value=>a, others columns…]
51 => [id=>51,value=>b, others columns…]
63 => [id=>63,value=>c, others columns…]

For now, i rewrite whole result to new array creating desired keys, but maybe there is a better way? Creating them when shifting data from DB? I try with “custom finders” but I cant.
Can you help? (I’m using Cakephp 3.0)
Jarek

2

I think the best option to use Hash after calling find()
https://book.cakephp.org/3.0/en/core-libraries/hash.html

3

I made my AppTable and a listEntites custom finder based on the findList source code, I think it can be tweaked but for now works for me

function findListEntities(Query $query, array $options)
{
    $options += [
        'keyField' => $this->getPrimaryKey(),
        'groupField' => null
    ];
    $options = $this->_setFieldMatchers(
        $options,
        ['keyField', 'groupField']
    );

    return $query
        ->find('list', $options)
        ->select($this)
        ->formatResults(
            function ($results) use ($options) {
                return $results->combine(
                    $options['keyField'],
                    function ($entity) { return $entity; },
                    $options['groupField']
                );
            },
            true // overwrite formatResults
        );
}
4

Using Hash after a find works well for me too. Here is an example for you.

use Cake\Utility\Hash;

$supplier_result = $this->loadModel(‘Users’)->find()
->select([‘Users.id’,‘Suppliers.provider_name’])
->contain([‘Suppliers’])
->toArray();

$supplier_list = Hash::combine($supplier_result, ‘{n}.id’,’{n}.supplier.provider_name’);

You could add more fields to the array as you wish and combine function will work magic for you.

5

Thanks for replies. But if I use Hash::combine() after find()…->toArray() will it not be the same as using foreach? I mean take row X, change, save to another array?
@raul338 I see combine in your function also, but it so complex, that I’m not sure how it works :slight_smile:
I will give it a try, when I return to that part of my project.
Does your method return array of entities (objects)?

6

Yes.
I took most the code of the original findList function.

And the combine function is from Collections class, look at the very last example.

Yes. It does return an array of Entities with the keyField (identical like the findList works), and optionally grouped by groupField (thats the combine call).
Like any other finder, you can mix/chain it with others options/finders

1 Like
7

Whoever googles this like I just did, take a look at combine():

$results = $articlesTable
    ->find('all')
    ->contain(['Authors'])
    ->combine('article_id', 'author')
    ->toArray();

The above gets you an array of author entities keyed by article_id. This Stackoverflow answer has helped me a lot: https://stackoverflow.com/questions/29058549/how-to-use-hash-on-the-query-builder-results-in-cakephp-3-x/29066321#29066321

8

I will try this, because ever before I just run foreach and make new array from normal find results.
Edit:
so I checked few variants for 10k rows on my old C2D “server” and it seems that placing foreach is not a big deal. combine() works, but results are like from find('list') so I can obtain only one value field. When I passed array as second argument ['log','IP'] - got error. So this is not what I want. I see that most time is consumed for fetch data, I see high %iowait on server. When I reduce amount of data by select and hydrate(false) it is close to combine()
@raul338 I try implement your function, it’s complained about Query kind or namespace, after fixing that it can’t find getPrimaryKey() I tried add load Tree behavior to table initialize() but it didn’t helped. So thanks for help I will stay with foreach because I understand it :slight_smile:

ps. how cake debug timer works? In this tests it show about 2 seconds for all, about 1s for controller action, couple tens ms for SQL, but page was reloaded after about 15s (according to network debug in FF browser)

9

If you just want your array to have keys that match the ID of the entity, use indexBy.

1 Like
10

If you just want your array to have keys that match the ID of the entity, use indexBy

Thanks, didn’t know about that one. This ndm’s Stackoverflow answer has helped me understand a bit:

indexBy() doesn’t belong to the query, but to the result set, so being able to call it requires the query to be executed first. It’s not possible to use this for an association query builder, as it must return a query, not a result set.

11

Thanks @Zuluru indexBy() is what I was looking for. I run quick test and it works :slight_smile: For others who looking for info, if you enter “indexBy” into book search you find nothing. It’s described in Collections chapter and you can search it in API docs, in 3.1v search works.

and an example:
$faktury = $this->Naleznoscis->find()->limit(30)->order(["id" => "DESC"])->indexBy('id');
debug($faktury->toArray() );

1 Like