I'm trying to optimize my db queries a bit to see if I can gain some performance. One thing I thought would maybe help was to limit the fields that xPDO has to fetch for each query (because I don't need all the fields for certain functionalities). I researched a bit about the select() method for an xPDO-Query and also found the method getSelectColums() which can be used in conjunction with select(). To make this a bit more understandable, here a bit of the code (method getSongs() of a custom class/component):
public function getSongs(array $fieldquery = array(), $limit = 0, $offset = 0, $sortfield = 'songid', $sortdir = 'ASC') {
$query = $this->modx->newQuery('TimelineSong');
$query->select($this->modx->getSelectColumns('TimelineSong', 'TimelineSong', '', array('artistwww', 'artistfb', 'artisttw', 'songcovdata', 'songvidsrc', 'songlikes', 'songplays', 'editedon', 'editedby', 'extended', 'deleted', 'published'), true));
$query->where($fieldquery);
$query->sortby($sortfield, $sortdir);
$query->limit($limit, $offset);
if ( $limit === 1 ) {
if ( $song = $this->modx->getObject('TimelineSong', $query) ) {
return $song;
} else {
return; // return null
}
} else {
if ( $songs = $this->modx->getIterator('TimelineSong', $query) ) {
return $songs;
} else {
return; // return null
}
}
}
what I try here is to EXCLUDE all the fields listed in the array inside getSelectColumns(), so in my understanding these fields should not be fetched from the db and thus also not be available in the resulting object, right?
but when I try to check the output with toArray() like
$songobj = $timeline->getSongs(array(), 1)->toArray('', true);
then I still get ALL the fields from the object, also the ones I try to exclude.
when I just create an output from the getSelectColums() method I get that:
`TimelineSong`.`id`, `TimelineSong`.`songid`, `TimelineSong`.`artistname`, `TimelineSong`.`songtitle`, `TimelineSong`.`songsrc`, `TimelineSong`.`songdur`, `TimelineSong`.`songcovsrc`, `TimelineSong`.`songbuysrc`, `TimelineSong`.`songrelease`, `TimelineSong`.`songcharts`, `TimelineSong`.`songvoting`, `TimelineSong`.`songcvotes`, `TimelineSong`.`songvvotes`, `TimelineSong`.`songrank`, `TimelineSong`.`songchange`, `TimelineSong`.`songpub`, `TimelineSong`.`songunpub`, `TimelineSong`.`ignore`
which is actually what I want, just the fields I need, not the others...but why do they show up in the resulting object/array?
So the question is if I'm not understanding the inner workings right here or if there is something else I miss =)?
Thanks in advance for any insights!
MODX Staff
