How to sort getrecords result when the same table is joined twice

3 posts by 2 authors in: Forums > CMS Builder
Last Post: June 17, 2017   (RSS)

By kitsguru - June 15, 2017

I have a table that uses a category table for two different fields.

Master Table (links) has two fields - `category` and `sub_category` both using a links_category table for their respective values using a list -> database lookup. 

I wished to sort the results using either field. Originally I tried to use a left join and use MySQL order by clause to handle the sorting of the array. However the getRecords() function did not allow me to join the same table twice. However since the label of both the category and sub_category are part of the returned result I was able to use built in PHP function to do this. I can then use jquery to sort the results on the fly without returning to the server to re-query the database. However if I did, I could still sort the results in php. The trick was to use array_multisort();

See php manual : http://php.net/manual/en/function.array-multisort.php

list($linksRecords, $linksMetaData) = getRecords(array(
    'tableName'     => "links",
    'useSeoUrls'    => false,
    'debugSql'      => true,
    'perPage'       => '50',
));

// build variables to sort the array
foreach ($linksRecords as $key => $row) {
    $cat[$key]  = $row['category:label'];
    $subcat[$key]  = $row['sub_category:label'];
}

// sort the array by subcategory;label
echo "sort the array by subcategory:label";
array_multisort($subcat, SORT_ASC, $linksRecords);

foreach ($linksRecords as $record) {
    showme($record);
}

// sort the array by category:label
echo "sort the array by category:label";
array_multisort($cat, SORT_ASC, $linksRecords);

foreach ($linksRecords as $record) {
    showme($record);
}

// sort the array by subcat:label category:label
echo "sort the array by subcat:label category:label";
array_multisort($subcat, SORT_ASC, $cat, SORT_ASC, $linksRecords);

foreach ($linksRecords as $record) {
    showme($record);
}

Jeff Shields

By Dave - June 17, 2017 - edited: June 17, 2017

Nice!  Thanks for sharing!  I always found array_multisort particularly confusing. ;-)

Dave Edis - Senior Developer
interactivetools.com