This topic contains a post which is marked as Best Answer. Press here if you would like to see it.
*

rooman

  • ****
  • 219 posts
Can I get the most viewed ads?
« 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?
Code: [Select]
<?php osc_query_item(array("**"**", 'results_per_page' => 500));
if( osc_count_custom_items() == 0) { ?>

<p class="empty"><?php _e('No Listings''sigma') ; ?></p>

*

MB Themes

Re: Can I get the most viewed ads?
« Reply #1 on: September 28, 2022, 03:37:33 PM »
You would need custom query and to use t_item_stats table
  To get fast support, we need following details: Detail description, URL to reproduce problem, Screenshots

*

rooman

  • ****
  • 219 posts
Re: Can I get the most viewed ads?
« Reply #2 on: September 28, 2022, 04:13:59 PM »
I tried this code

Code: [Select]
$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
Code: [Select]
$mySearch->addField("SUM(".DB_TABLE_PREFIX."t_item_stats.i_num_views) as total_views");
I replaced this line
Code: [Select]
$mySearch->order("i_num_views", "DESC", DB_TABLE_PREFIX."t_item_stats");by this
Code: [Select]
$mySearch->order("total_views", "DESC");It's sorted, but it shows less than 500 views
« Last Edit: September 28, 2022, 04:59:25 PM by rooman »

*

MB Themes

Re: Can I get the most viewed ads?
« Reply #3 on: September 28, 2022, 05:10:12 PM »
You do not have limit on views here
  To get fast support, we need following details: Detail description, URL to reproduce problem, Screenshots

*

rooman

  • ****
  • 219 posts
Re: Can I get the most viewed ads?
« Reply #4 on: September 28, 2022, 05:14:08 PM »
Where to put limit
What is the correct way?

*

MB Themes

Re: Can I get the most viewed ads?
« Reply #5 on: September 29, 2022, 09:10:41 AM »
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");

  To get fast support, we need following details: Detail description, URL to reproduce problem, Screenshots

*

rooman

  • ****
  • 219 posts
Re: Can I get the most viewed ads?
« Reply #6 on: September 29, 2022, 10:31:32 AM »
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

Code: [Select]
$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());

*

MB Themes

Re: Can I get the most viewed ads?
« Reply #7 on: September 29, 2022, 02:52:49 PM »
You probably must use "having" instead of addCondition in this case.
  To get fast support, we need following details: Detail description, URL to reproduce problem, Screenshots

*

rooman

  • ****
  • 219 posts
Re: Can I get the most viewed ads?
« Reply #8 on: September 29, 2022, 03:27:23 PM »
I used having
Instead of addCondition
Code: [Select]
$mySearch->having("i_num_views => 500");But it shows an error in the function There is no having . function

I tried
Code: [Select]
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
« Last Edit: September 30, 2022, 12:58:16 PM by rooman »

*

MB Themes

Re: Can I get the most viewed ads?
« Reply #9 on: September 30, 2022, 03:14:15 PM »
Try this:
Code: [Select]
$mySearch->addHaving("sum(i_num_views) > 500");


(or ->dao->having)
  To get fast support, we need following details: Detail description, URL to reproduce problem, Screenshots

*

rooman

  • ****
  • 219 posts
Re: Can I get the most viewed ads?
« Reply #10 on: September 30, 2022, 04:05:12 PM »
Try this:
Code: [Select]
$mySearch->addHaving("sum(i_num_views) > 500");


(or ->dao->having)


Thank you it works ???

*

MB Themes

Re: Can I get the most viewed ads?
« Reply #11 on: September 30, 2022, 04:12:35 PM »
Cool, can you share complete solutoin?
  To get fast support, we need following details: Detail description, URL to reproduce problem, Screenshots

Marked as best answer by frosticek on October 01, 2022, 10:04:38 PM
*

rooman

  • ****
  • 219 posts
Re: Can I get the most viewed ads?
« Reply #12 on: October 01, 2022, 12:08:26 PM »
Code: [Select]
$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)
« Last Edit: October 01, 2022, 12:11:33 PM by rooman »

*

MB Themes

Re: Can I get the most viewed ads?
« Reply #13 on: October 01, 2022, 10:04:49 PM »
Cool, thanks ;)
  To get fast support, we need following details: Detail description, URL to reproduce problem, Screenshots