Osclass Support Forums
General osclass questions => General discussion => Topic started by: rooman on September 28, 2022, 03:28:38 PM
-
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
$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:
$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>
??? 8)
-
Cool, thanks ;)