Can I get the most viewed ads?
For example, ads with more than 500 views and higher
Can this code be used, but what are the modifications to it?
<?php osc_query_item(array("**"**", 'results_per_page' => 500));
if( osc_count_custom_items() == 0) { ?>
<p class="empty"><?php _e('No Listings', 'sigma') ; ?></p>

You would need custom query and to use t_item_stats table

I tried this code

$mySearch = new Search();
$mySearch->addField("SUM(".DB_TABLE_PREFIX."t_item_stats.i_num_views) as total_views");
$mySearch->addCategory('dvd'); //category
$mySearch->set_rpp(500); //results per page
$mySearch->addTable(DB_TABLE_PREFIX."t_item_stats");
$mySearch->addConditions(DB_TABLE_PREFIX."t_item_stats.fk_i_item_id = ".DB_TABLE_PREFIX."t_item.pk_i_id");
$mySearch->order("i_num_views", "DESC", DB_TABLE_PREFIX."t_item_stats");
$mySearch->addGroupBy(DB_TABLE_PREFIX."t_item_stats.fk_i_item_id");
View::newInstance()->_exportVariableToView("customItems", $mySearch->doSearch());

if( osc_count_custom_items() == 0) { ?>
<p class="empty"><?php _e('No Listings', 'sigma') ; ?></p>
But it didn't work

I think this line may need to be modified
$mySearch->addField("SUM(".DB_TABLE_PREFIX."t_item_stats.i_num_views) as total_views");
I replaced this line
$mySearch->order("i_num_views", "DESC", DB_TABLE_PREFIX."t_item_stats");by this
$mySearch->order("total_views", "DESC");It's sorted, but it shows less than 500 views

You do not have limit on views here

Where to put limit
What is the correct way?

but you should aggregate table first using SELECT fk_i_item_id, sum(i_num_views) as i_num_views from DB_TABLE_PREFIX.t_item_stats GROUP BY fk_i_item_id.

Then join it, then add filter on stats


$mySearch->addConditions("i_num_views > 500");


but you should aggregate table first using SELECT fk_i_item_id, sum(i_num_views) as i_num_views from DB_TABLE_PREFIX.t_item_stats GROUP BY fk_i_item_id.

Then join it, then add filter on stats

Code: [Select]

$mySearch->addConditions("i_num_views > 500");


tested
But there are no results

$mySearch = new Search();
$mySearch->addField("SUM(".DB_TABLE_PREFIX."t_item_stats.i_num_views) as total_views");
$mySearch->addCategory('dvd'); //category
$mySearch->set_rpp(500); //results per page
$mySearch->addTable(DB_TABLE_PREFIX."t_item_stats");
$mySearch->addConditions(DB_TABLE_PREFIX."t_item_stats.fk_i_item_id = ".DB_TABLE_PREFIX."t_item.pk_i_id");
$mySearch->addConditions("i_num_views > 500");
$mySearch->order("total_views", "DESC");
$mySearch->addGroupBy(DB_TABLE_PREFIX."t_item_stats.fk_i_item_id");
View::newInstance()->_exportVariableToView("customItems", $mySearch->doSearch());

You probably must use "having" instead of addCondition in this case.

I used having
Instead of addCondition
$mySearch->having("i_num_views => 500");But it shows an error in the function There is no having . function

I tried
LIMIT 500, %d", DB_TABLE_PREFIX, DB_TABLE_PREFIX, DB_TABLE_PREFIX, DB_TABLE_PREFIX, osc_current_user_locale(), $catsToInclude, $num_ads);
But it shows an error

Try this:
$mySearch->addHaving("sum(i_num_views) > 500");


(or ->dao->having)

Try this:

Code: [Select]

$mySearch->addHaving("sum(i_num_views) > 500");


(or ->dao->having)

Thank you it works

Cool, can you share complete solutoin?

$mySearch = new Search();
$mySearch->addField("SUM(".DB_TABLE_PREFIX."t_item_stats.i_num_views) as total_views");
$mySearch->addCategory('dvd'); //category
$mySearch->set_rpp(500); //results per page
$mySearch->addTable(DB_TABLE_PREFIX."t_item_stats");
$mySearch->addConditions(DB_TABLE_PREFIX."t_item_stats.fk_i_item_id = ".DB_TABLE_PREFIX."t_item.pk_i_id");
$mySearch->addHaving("sum(i_num_views) > 500");
$mySearch->order("total_views", "DESC");
$mySearch->addGroupBy(DB_TABLE_PREFIX."t_item_stats.fk_i_item_id");
View::newInstance()->_exportVariableToView("customItems", $mySearch->doSearch());
if( osc_count_custom_items() == 0) { ?>
<p class="empty"><?php _e('No Listings', 'sigma') ; ?></p>

Cool, thanks