[SOLVED] How to create query sql-manager for viewed pages-anyone?

[unlimitedgrain]

Hi,

 

Can someone help me creating a query?

I would like to see the number of page views per product per day or week.

I know I can see it in the stats, but there I have to click on a product to see it and I want to see it as a list, so I can compare the views per product and see the most viewed products etc.

 

I have no sql knowledge, so combining all tables is to difficult for me.

 

Using 1.6.0.11

Edited March 18, 2015 by unlimitedgrain (see edit history)

[unlimitedgrain]

Does anyone know how to make this SQL query?

[rocky]

Try something like the following:

SELECT SQL_CALC_FOUND_ROWS p.reference, p.id_product, 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 '2015-03-15 00:00:00' AND '2015-03-15 23:59:59'
   AND dr.time_end BETWEEN '2015-03-15 00:00:00' AND '2015-03-15 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 

This should calculate the page views of products on 15 March 2015 and display it along with the products' reference codes, IDs and names. Adjust the dates as necessary.

[unlimitedgrain]

Try something like the following:

SELECT SQL_CALC_FOUND_ROWS p.reference, p.id_product, 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 '2015-03-15 00:00:00' AND '2015-03-15 23:59:59'
   AND dr.time_end BETWEEN '2015-03-15 00:00:00' AND '2015-03-15 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 

This should calculate the page views of products on 15 March 2015 and display it along with the products' reference codes, IDs and names. Adjust the dates as necessary.

 Thanks Rocky, I think it as almost there. It is grouped the right way, but the count is for all 0 ;-)

I also would like see the date range in the output if that is possible.

I had a query made that did show the viewed pages but it was not with a total count of one product. So I think it showed all records in the DB.

My query:

SELECT  pl.name, pr.id_product, pr.reference, pv.counter AS total FROM ps_page_viewed pv 

  Left JOIN ps_page p ON pv.id_page = p.id_page 

 LEFT JOIN ps_page_type pt ON p.id_page_type = pt.id_page_type 

LEFT JOIN ps_product pr ON p.id_object = pr.id_product

LEFT JOIN ps_product_lang pl on pr.id_product = pl.id_product

  WHERE pt.name = 'product' AND p.id_object = pr.id_product

 

Maybe you know how to make the counter as a total and showing the date range I put in the query?

[rocky]

Can you send me those database tables from your website? For some reason, the ps_page_viewed table is empty on all my websites. Makes it hard for me to test queries without data.

[unlimitedgrain]

We are one step further, the id_page_type should be 15, but for some reason it doesn't show the number of views for all products. So somewhere there is still something wrong with the count.

Below the query that gives these results (not for all products). If someone knows what has to be changed, please let me know.

Thanks to rocky who spend time on helping me!!!!

 

SELECT SQL_CALC_FOUND_ROWS p.reference, p.id_product, 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 = 15

   AND dr.time_start BETWEEN '2015-02-21 00:00:00' AND '2015-03-16 23:59:59'

   AND dr.time_end BETWEEN '2015-02-21 00:00:00' AND '2015-03-16 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

[unlimitedgrain]

OK SOLVED!! Here is the query that works, the id_page_type has to be 3. I added a sort.

 

SELECT SQL_CALC_FOUND_ROWS p.reference, p.id_product, 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 = 3

   AND dr.time_start BETWEEN '2015-02-21 00:00:00' AND '2015-03-18 23:59:59'

   AND dr.time_end BETWEEN '2015-02-21 00:00:00' AND '2015-03-18 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 totalPageViewed DESC

 

couldn't have done this without the help of Rocky!!

[rocky]

I'm happy you finally figured it out.

[ABANGWEB]

Then how to display in homepage? we looking most viewed product by all visitor.

Edited April 1, 2015 by ABANGWEB (see edit history)

[unlimitedgrain]

I don't understand your question. This is a query that shows views forms all visitors.

[rocky]

Do you mean the Back Office dashboard? I see there's already an option to list the most viewed products on my PrestaShop v1.6.0.14 test site. Scroll down to the "Products and sales" section and then click the "Most Viewed" tab.

[Guest locen]

Hi, I made this query:

 SELECT o.reference,  pl.name as product_name, pc.name AS city, od.product_quantity, cs.firstname, cs.lastname, DATE(o.date_add) AS data_acq, ROUND (od.total_price_tax_incl, 3) AS price_with_tax,  cu.name AS curr, o.total_discounts_tax_excl, pt.rate AS tax_value_percent, od.product_reference

FROM ps_product p

LEFT JOIN ps_product_lang pl ON p.id_product = pl.id_product

LEFT JOIN ps_order_detail od ON p.id_product = od.product_id

LEFT JOIN ps_orders o ON o.id_order = od.id_order

LEFT JOIN ps_currency cu ON cu.id_currency = o.id_currency

JOIN ps_order_detail_tax dt ON od.id_order_detail = dt.id_order_detail

JOIN ps_tax pt ON pt.id_tax = dt.id_tax

JOIN ps_customer cs on o.id_customer=cs.id_customer

JOIN ps_address ad on o.id_address_delivery=ad.id_address

JOIN ps_country_lang pc on ad.id_country=pc.id_country

 JOIN ps_country_lang ps on pl.id_lang=pc.id_lang

WHERE  o.current_state = 2 OR o.current_state =  3 OR o.current_state = 4 OR o.current_state = 5 OR o.current_state = 9 OR o.current_state = 12 OR o.current_state = 13 OR o.current_state = 16 

 

 

GROUP BY od.id_order_detail

ORDER BY o.date_add;

 

 

But I have problem with prices. when I export file i have price number with a lot of decimals.

can someone help me?

[newcomer]

I can confirm unlimitedgrain solution works. However in my case the id_page_type is 2. You need to find what is the correct id page type for "product" in ps_page_type.