CODEWITHSUNDEEP
mysqlsql
Peter Fox
Peter Fox

Reputation: 1315

MySQL others viewed

We have a custom framework for our webshop and we want to add some new features, like Others also viewed. This should display products that have been viewed by others as well. So it should display books that have been viewed by others that have viewed this book as well.

We have a database that keeps a log running on which users has viewed which products. 

CREATE TABLE `log_products_viewed` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `productId` bigint(11) DEFAULT NULL,
  `userId` bigint(11) DEFAULT NULL,
  `createdAt` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=84 DEFAULT CHARSET=latin1;

Then we would like to make a query on products that have been viewed by others as well. We have tried something, but is this the correct Query. 

SELECT productId, 
       Count(productId) AS viewAmount 
FROM   log_products_viewed 
WHERE  userid IN (SELECT DISTINCT( userid ) AS members 
                  FROM   log_products_viewed 
                  WHERE  productId = '396') 
       AND productId <> '396' 
GROUP  BY productId 
ORDER  BY viewAmount DESC 
LIMIT  0, 2;

We get a return with two results and the corresponding amounts. But if 1 user has viewed an item multiple times, he is able to manipulate the query.

Therefore the query has to return the products which has the most users viewed and not the most views. I'm stuck in the query, can you help me out?

Upvotes: 0

Views: 72

Answers (1)

Peter van der Wal
Peter van der Wal

Reputation: 11856

If this table is used for only this purpose you can add a

UNIQUE KEY (`productId`, `userId`)

to it, so a user "can't view a product twice". Change your insert-statement into

INSERT INTO log_products_viewed (productId, userId) VALUES (?, ?) ON DUPLICATE KEY IGNORE

-- or if you want to update the date

INSERT INTO log_products_viewed (productId, userId) VALUES (?, ?) ON DUPLICATE KEY UPDATE lastViewedAt = CURRENT_TIMESTAMP()

to avoid warnings.

This also helps to keep your table small and thus your SELECT-query fast.

Otherwise:

SELECT productId, 
   Count(DISTINCT userid) AS viewAmount 
FROM   log_products_viewed 
WHERE etc. etc.

Upvotes: 1

Related Questions