Re: SOLVED, INCLUDES A BUG FIX Get number of items/category from search

[marius-ciclistu]

Is there a build in way to query by something like

Code: [Select]

SELECT fk_i_category_id, COUNT(pk_i_id) FROM `oc_t_item` WHERE b_enabled = 1 and b_active = 1 and dt_expiration > now() GROUP BY fk_i_category_id
in order to get the category stats for that search?

That means taking the actual query and replacing the Selected columns with the above + adding GROUP BY + removing the limit and offset

Edit: Does anyone know what impact could have this solution?

[marius-ciclistu]

Code: [Select]

    $emptySearch = in_array($uri, array('/search/sOrder,dt_pub_date/iOrderType,desc', '/search'), true) || strpos($uri, ',') === false;
How can I extend this condition to include all scenarios where only a category is selected or NO category is selected, the rest of the search being empty?

[marius-ciclistu]

Found a way to solve the second post from above:

Code: [Select]

function mc_get_search_categories_stats()
{
    $categoryItems = array();
    $getcategoriesStats = false;
   
    foreach(Params::getParamsAsArray() as $param => $value) {
    if (in_array($param, array('page', 'sOrder', 'iOrderType', 'sCategory', 'bPic'), true) === false) {
    $getcategoriesStats = true;
    break;
    }
    }
   
    if ($getcategoriesStats === true) {
        $actualSearch = osc_search();
        $conditions = (array) json_decode($actualSearch->toJson());
        $conditions['limit_init']        = 0;
        $conditions['results_per_page']  = 500;
        $conditions['aCategories'] = array();
        $categoryStatsSearch = new Search();
        $categoryStatsSearch->setJsonAlert($conditions);
        $categoryStatsSearch->addGroupBy(DB_TABLE_PREFIX.'t_item.fk_i_category_id');
        $categoryStatsSearch->addField('COUNT('.DB_TABLE_PREFIX.'t_item.pk_i_id) as category_num_items_search');   
        $categoryStats = $categoryStatsSearch->doSearch(false, true);
        unset($categoryStatsSearch);
       
        foreach($categoryStats as $x){
            $categoryItems[$x['fk_i_category_id']] = $x['category_num_items_search'];
        }

        $categorySearch = new Category();
        foreach ($categorySearch->listAll() as $category){
            $categoryParent[$category['pk_i_id']] = $category['fk_i_parent_id'];
        }

        foreach($categoryItems as $catId => $nr){
            $id = $catId;
           
            while($id !== null) {
                $id = $categoryParent[$id];
               
                if($id !== null) {
                    if(isset($categoryItems[$id]) === false) {
                        $categoryItems[$id] = $nr;
                    } else {
                        $categoryItems[$id] += $nr;
                    }
                }
            }
        }
    }
   
    return $categoryItems;
}

But when the param bPic (seach only ads with picture is checked) => 2 group by statements and the results are wrong...

SOLUTION (that disregards the picture parameter for stats)
Core mod
THIS CAN BE CONSIDERED A BUG!
/oc-includes/osclass/model/Search.php

Code: [Select]

                if($this->withPicture && $this->groupBy === '') {
                    $this->dao->join(sprintf('%st_item_resource', DB_TABLE_PREFIX), sprintf('%st_item_resource.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX), 'LEFT');
                    $this->dao->where(sprintf("%st_item_resource.s_content_type LIKE '%%image%%' ", DB_TABLE_PREFIX, DB_TABLE_PREFIX, DB_TABLE_PREFIX));
                    $this->dao->groupBy(DB_TABLE_PREFIX.'t_item.pk_i_id');
                }
instead of

Code: [Select]

                if($this->withPicture) {
                    $this->dao->join(sprintf('%st_item_resource', DB_TABLE_PREFIX), sprintf('%st_item_resource.fk_i_item_id = %st_item.pk_i_id', DB_TABLE_PREFIX, DB_TABLE_PREFIX), 'LEFT');
                    $this->dao->where(sprintf("%st_item_resource.s_content_type LIKE '%%image%%' ", DB_TABLE_PREFIX, DB_TABLE_PREFIX, DB_TABLE_PREFIX));
                    $this->dao->groupBy(DB_TABLE_PREFIX.'t_item.pk_i_id');
                }