Welcome Guest, Not a member yet? Register   Sign In
A faster way to get an id-indexed result from database?
#1
Question 
(This post was last modified: 04-22-2020, 08:43 AM by Leo.)

Hello all!
Very often in my apps I need to get a result array (or object), from database, indexed by ids! I get everything and then index it with a loop like this:
PHP Code:
$unindexed_rows $db->table('records')->get()->getResultArray();
$indexed_rows = [];
foreach (
$unindexed_rows as $i) {
     $indexed_rows[$i['id']] = $i;

Does anyone know of a faster or more convenient way to do this? If there is not such a way, and one of you CodeIgniter core devs sees this, do you think this would make a good feature for CI? If it's impossible to avoid a loop then at least as a short-cut convenience function? I imagine I'm not the only one who needs indexed arrays/objects.
You can see things I made with codeigniter here: itart.pro its not overly impressive as I have very little time to learn.
Reply
#2

(This post was last modified: 04-22-2020, 09:13 AM by John_Betong.)

Try adding "orderby" to the SQL Query statement.

https://codeigniter.com/user_guide/datab...er#orderBy
Reply
#3

(This post was last modified: 04-22-2020, 10:08 AM by jreklund.)

I would use the function getUnbufferedRow('array') instead. As of right know you are getting an extra foreach. I'm not sure how to extend the Database class.

You can however create your own function that you can create in a library or BaseController / Base Model.

PHP Code:
// In a controller or model class
function getResultWithKey(\CodeIgniter\Database\ResultInterface $resultstring $key 'id')
{
    
$results = [];

    while (
$row $result->getUnbufferedRow('array'))
    {
        if (isset(
$row[$key]))
        {
            
$results[$row[$key]] = $row;
        }
    }

    return 
$results;
}

// Someplace else
$res $db->table('records')->get();
$result $this->getResultWithKey($res); 
Reply
#4

(This post was last modified: 04-22-2020, 10:54 AM by Leo.)

I spent some time looking, before posting this and some time after and so far this is the best working solution I found:
$indexed_by_id = array_column($unindexed, null, 'id');

PHP Code:
$rows_unindexed $db->table('things')->orderBy('thing_name''ASC')->get()->getResultArray();
$rows_indexed array_column($rows_unindexednull'id'); 


Should I put this in a feature request forum or nah?
So we would have a function like "getIndexedArray" something like this:
PHP Code:
$rows_indexed $db->table('things')->orderBy('thing_name''ASC')->get()->getIndexedArray('id'); 

Where getIndexedArray utilizes an array_coulmn function

or maybe even

$rows_indexed = $db->table('things')->orderBy('thing_name', 'ASC')->get()->indexColumn('id', 'object');
with 'object' doing this: return (object)array_column($result, null, $key);

p.s. yes, extending the database class would be nice, but I've seen somewhere on the forum its a pain

p.p.s. weeeell, also I figured I can do this:
PHP Code:
$valuesIndexed = (object)array_column(
                     $db->table('filter_values')
                         ->orderBy('value_name''ASC')
                         ->get()->getResultArray(),
                 
null'id'); 
You can see things I made with codeigniter here: itart.pro its not overly impressive as I have very little time to learn.
Reply
#5

Haven't tried array_coulmn myself, but can it return an array of objects? Naming convention are always the hardest part. The key can be more than just an index (number), I usually have mine built with UUIDv4, so it should have another name.

Maybe:
PHP Code:
getResultKey($type 'object'$key 'id')
getResultKeyArray($key 'id')
getResultKeyObject($key 'id'

You can always post thread(s) in feature request, or make this function yourself and make Pull Request for it yourself. But you need to make sure it can sure it can return both arrays and objects, and with different keys. And if they key aren't there, throw an error.
Reply
#6

(This post was last modified: 04-22-2020, 12:08 PM by Leo.)

(04-22-2020, 10:51 AM)jreklund Wrote: Haven't tried array_coulmn myself, but can it return an array of objects? Naming convention are always the hardest part. The key can be more than just an index (number), I usually have mine built with UUIDv4, so it should have another name.

Yes!! it can return an array of objects, or an array of arrays! It can even index by STRING or by INT!

I made this in Model.php for testing - so far its just a blunt copy of a findAll() function, but with an array_column modification at the end, like this:
PHP Code:
public function arrayColumn(string $keyint $limit 0int $offset 0)
    {
        $builder $this->builder();

        if ($this->tempUseSoftDeletes === true)
        {
            $builder->where($this->table '.' $this->deletedFieldnull);
        }

        $row $builder->limit($limit$offset)
            ->get();

        $row $row->getResult($this->tempReturnType);

        $eventData $this->trigger('afterFind', ['data' => $row'limit' => $limit'offset' => $offset]);

        $this->tempReturnType     $this->returnType;
        $this->tempUseSoftDeletes $this->useSoftDeletes;

        if($this->returnType === 'object') {
            return (object)array_column($eventData['data'], null'id');
        } else {
            return array_column($eventData['data'], null'id');
        }
    

It works as expected...returning an array of arrays if your model specified output as array. or an object of objects
It seems a bit stupid to just copy findAll() and modify it. It feels like I'm bloating the framework. I'm not sure how to make it smaller, or even work with what CI already has.

Until I figure out how to do that...not just a copy of findAll() with modification I won't put it in a Pull Request. I'll post it in a feature request forum though. Seems like a good idea.

p.s. getResultKey is good, I'll propose that.
p.p.s. posted here: https://forum.codeigniter.com/thread-76215.html
You can see things I made with codeigniter here: itart.pro its not overly impressive as I have very little time to learn.
Reply
#7

Im guessing one of the reasons this is not OK to be implemented in the framework is the possibility of omitting results.

If say you make a group by call, and you end up having non-unique values in the "key" field (the one that you are using at the end of the array_column function).

Thats why it might be better to just use this as a helper function or a make some Trait for it probably (if possible)? I know its not as convenient as having it built-in, but thats just what i think. Otherwise its a great idea to shorten that - it appears very very often in my code also!
Reply




Theme © iAndrew 2016 - Forum software by © MyBB