[SOLVED] PS 1.6 Additional Column Page Views in Backoffice - Catalog - Product

[Jolsvi]

Hi,

I want to add additional column Page Views in the Product table (backoffice). I have tried to search in the forum but mostly shows the product page views for frontoffice purpose.

I know there is a stats function Best-selling Products which displays page views data from each product, but there is no filter to sort like catalog products has. 

Please help, if someone knows how to make it or has already applied it.

Thank you in advance

Edited March 28, 2021 by Jolsvi (see edit history)

[Guest]

.

Edited July 26, 2021 by Guest (see edit history)

[Jolsvi]

Hi @Guest thank you for your answer.

I tried it to add this on AdminProductController.php but it shows with multiple product id

$this->_select .= ', pv.`counter` as pageview_counter';

 

$this->_join .= ' LEFT JOIN `'._DB_PREFIX_.'page` pa ON (pa.`id_object` = a.`id_product` AND pa.`id_page_type`=5)';
$this->_join .= ' LEFT JOIN `'._DB_PREFIX_.'page_viewed` pv ON (pv.`id_page` = pa.`id_page`)';

 

$this->fields_list['pageview_counter'] = array(
    'title' => $this->l('Page View'),
    'align' => 'center',
    'havingFilter' => true,
);

 

Maybe you have additional code to view total counter in one product?

[Guest]

.

Edited July 26, 2021 by Guest
update function (see edit history)

[Guest]

.

Edited July 26, 2021 by Guest (see edit history)

[Jolsvi]

Thank you @Guest you are really helpful.

I have tried your codes. The first one shows no more multiple records, but column page views returns 0 data.

The second one shows page views with data but I think it is not the total page views (and the filter displays random sorted data). Probably because of the id_date_range in table ps_date_range connection.

 

I compare the results by adding the query that I got from forum and put that into the SQL manager:

 

SELECT SQL_CALC_FOUND_ROWS p.id_product, p.reference, pl.name, (
   SELECT IFNULL(SUM(pv.counter), 0)
   FROM ps_page pa
   LEFT JOIN ps_page_viewed pv ON pa.id_page = pv.id_page
   LEFT JOIN ps_date_range dr ON pv.id_date_range = dr.id_date_range
   WHERE pa.id_object = p.id_product AND pa.id_page_type = 5
   AND dr.time_start BETWEEN '2021-01-01 00:00:00' AND '2021-12-31 23:59:59'
   AND dr.time_end BETWEEN '2021-01-02 00:00:00' AND '2021-12-31 23:59:59') AS totalPageViewed
FROM ps_product p
INNER JOIN ps_product_shop product_shop ON (product_shop.id_product = p.id_product AND product_shop.id_shop = 1)
LEFT JOIN ps_product_lang pl ON (p.id_product = pl.id_product AND pl.id_lang = 1 AND pl.id_shop = 1)
GROUP BY p.id_product
ORDER BY p.id_product ASC

 

and I don't know how to apply that in the function

 

[Guest]

.

Edited July 26, 2021 by Guest (see edit history)

[Jolsvi]

Hi @Guest it works!!!

The total page views shows the same number in database.

Thank you very much for your help

[Guest]

.

Edited July 26, 2021 by Guest (see edit history)