Scoped Item Navigation

I’ve got a few custom pages on my site where I’ve appended a query param on every link that goes to an item, specifically when the user is viewing items by type or collection. So if they’re browsing items with the type Still Image, the items/show links are appended with ?type=6, etc. The idea is that they’re mainly interested in viewing items that match that criteria, so the next/prev links on items/show should keep within that scope, rather than linking to the next/previous by item id.

The function below outputs the modified next/prev links when the query param is present, though I’m only testing with a very small dataset at this point. I’m wondering specifically if it’s a bad idea to use get_records() to query when all I really need is each item id. I unset() the results right after I get the ids, but I have no idea if that’s even helpful.

How many items can I query with get_records() before it starts dragging the server? The project this is for will likely be returning maybe 2-3K records.

Any thoughts?

function scopedNavigation($item=null, $collectionid=null, $typeid=null){
    $link_previous=null;
    $link_next=null;
    $itemid=$item->id;
    $appendParam=null;
    if($itemid){
        $itemsArray=array();
        $params=array();
        // limit by collection OR type
        if(strlen($collectionid) > 0){
            $params['collection']=$collectionid;
            $appendParam = '?collection='.$collectionid;
        }elseif(strlen($typeid) > 0){
            $params['type']=$typeid;
            $appendParam = '?type='.$typeid;
        }
        $itemsQuery = get_records('Item',$params); // @todo: is this performant?
        foreach($itemsQuery as $i){
            $itemsArray[]=$i->id; // an array of item ids matching the param
        }
        unset($itemsQuery);
        if(count($itemsArray)){
            // find the ids on either side of the current item id
            $index = array_search($itemid, $itemsArray);
            if($index !== false && $index > 0 ){
                $prev = $itemsArray[$index-1];
                $link_previous = $prev ? '<a href="'.url('items/show/'.$prev.$appendParam).'">'.__('Previous Item').'</a>' : null;
            }else{
                $link_previous=null;
            }
            if($index !== false && $index < count($itemsArray)-1){
               $next = $itemsArray[$index+1]; 
               $link_next = $next ? '<a href="'.url('items/show/'.$next.$appendParam).'">'.__('Next Item').'</a>' : null;
            }else{
               $link_next=null; 
            }         
        }else{
            return null;
        }
    }
    $html .= '<nav>';
        $html .= '<ul class="item-pagination-custom">';
            $html .= '<li id="previous" class="previous">'.$link_previous ? $link_previous : link_to_previous_item_show().'</li>';
            $html .= '<li id="next" class="next">'.$link_next ? $link_next : link_to_next_item_show().'</li>';
        $html .= '</ul>';
    $html .= '</nav>';
    
    return $html;
}

Usage:

<?php echo scopedNavigation($item, html_escape($_GET['collection']), html_escape($_GET['type']));?>

EDIT: there’s a bug in the code above that causes some waste, but we’ll ignore that for the moment.

I ended up just using MYSQL instead of get_records().

Here’s my current version in case anyone finds this later when trying to solve a similar problem.

function scopedNavigation($item=null, $collectionid=null, $typeid=null){
    $html = null;
    $current_item_id = $item->id;
    $appendParam = null;
    $column = null;
    $value = null;
    $itemsArray=array();
    if($current_item_id){
        // limit by collection OR type (not both)
        if(strlen($collectionid) > 0){
            $appendParam = '?collection='.$collectionid;
            $column = 'collection_id';
            $value = $collectionid;
        }elseif(strlen($typeid) > 0){
            $appendParam = '?type='.$typeid;
            $column = 'item_type_id';
            $value = $typeid;
        }
        // db query
        $db = get_db();
        $prefix=$db->prefix;
        if($column && $value){
            $select = "SELECT id FROM `".$prefix."items` WHERE `".$column."` = ".$value." AND `public` = 1";
        }else{
            $select = "SELECT id FROM `".$prefix."items` WHERE `public` = 1";
        }
        $sql = $select;
        $q = $db->query($sql);
        $results = $q->fetchAll();
        
        // build an array of item ids
        foreach($results as $result){
            $itemsArray[] = $result['id'];  
        }
        // find the ids on either side of the current item id
        if(count($itemsArray)){
            $index = array_search($current_item_id, $itemsArray);
            if($index !== false && $index > 0 ){
                $prev = $itemsArray[$index-1];
                $link_previous = $prev ? '<a class="scoped" href="'.url('items/show/'.$prev.$appendParam).'">'.__('Previous Item').'</a>' : null;
            }
            if($index !== false && $index < count($itemsArray)-1){
               $next = $itemsArray[$index+1]; 
               $link_next = $next ? '<a class="scoped" href="'.url('items/show/'.$next.$appendParam).'">'.__('Next Item').'</a>' : null;
            }
        }   
    }
    $html .= '<nav>';
        $html .= '<ul class="item-pagination">';
            $html .= '<li id="previous" class="previous">'.$link_previous.'</li>';
            $html .= '<li id="next" class="next">'.$link_next.'</li>';
        $html .= '</ul>';
    $html .= '</nav>';
    
    return $html;
}

Note that it may or may not be more performant to just get the next and previous ids by doing two separate database queries (example), but I found this current version to be good enough for my needs for now.