0

Here I want to sort product list as per current user.
 The user clicked the product most comes first in list and sort accordingly.


Here I have table product_counts which store user_id product_id and counts.

I tried this, but it taking a lot of queries to run which isn't good.

Product.all.sort_by{ |r|  r.product_clicks.where(user_id: user.id).first.try(:count).to_i }.reverse!
Anshul Riyal
  • 123
  • 1
  • 12
  • Share your code which you have tried. – NN796 Jul 23 '19 at 12:43
  • Table name is `product_clicks` or `product_counts`? – NN796 Jul 23 '19 at 12:50
  • Would something like this work? `Product.joins(:product_clicks).select('products.*, COALESCE(COUNT(product_clicks),0) AS click_count').where(product_clicks: {user_id: current_user.id }).order('click_count DESC')`. I had an instructor that told us to start on the model we want returned. So this way you could start on the product model in order to have products returned without having to use `#map`? – Int'l Man Of Coding Mystery Jul 23 '19 at 15:18

2 Answers2

1

Here is a working query which will be faster:

ProductCount.joins(:product).where(user_id: current_user.id).order(count: :desc).map(&:product)

or

ProductCount.includes(:product).where(user_id: current_user.id).order(count: :desc).map(&:product)

The query with includes will be faster. Here is a guide for difference between joins & includes.

Let me know if it worked.

NN796
  • 1,247
  • 11
  • 31
  • I recall that a `where` query does not necessarily maintain the id order. Do you have docs that suggest it does? I could be wrong or out of date. – jvillian Jul 23 '19 at 13:34
  • @jvillian `order` clause needs to be mentioned separately. https://apidock.com/rails/ActiveRecord/QueryMethods/order – NN796 Jul 23 '19 at 13:49
  • So, this: `Product.where(id: p_ids)` may not maintain the order of `p_ids` and `products`, therefore, may not be ordered as intended. Yes? No? – jvillian Jul 23 '19 at 14:00
  • @jvillian No. Thanks for the correction. I will update my answer shortly. – NN796 Jul 23 '19 at 14:03
  • @NN796 It giving an only product which current user clicked not those who was never clicked. ex: if I have 100 product and I clicked only 10 of them then list only shows 10 product and not rest of them. – Anshul Riyal Jul 24 '19 at 08:03
  • @AnshulRiyal You are saying: `if I have 100 product and I clicked only 10 of them then list only shows 10 product and not rest of them` Please clear your requirement and paste the query you are using? – NN796 Jul 24 '19 at 08:11
  • I'm using this `ProductCount.joins(:product).where(user_id: current_user.id).order(count: :desc).map(&:product)` and yes its not showing rest of them thou its sorting what it giving. – Anshul Riyal Jul 24 '19 at 09:15
  • @AnshulRiyal your requirement does not make sense to me. Your requirement was to show products which have click on them. But now you are saying that you want to show the products which have clicks plus the products which do not have clicks. Right? – NN796 Jul 24 '19 at 12:16
  • @NN796 It's mention in the question.. i need all the products but sorted in such a manner that most clicked but user comes. What exactly you didn't get? – Anshul Riyal Jul 24 '19 at 12:26
  • @AnshulRiyal you mentioned `Sort product on the basis of current user most clicks?`. If you want to show clicked and un-clicked products then can you please tell me if there is a relationship of the user(in your case `current_user`) with the product table? – NN796 Jul 24 '19 at 12:52
  • @NN796 exactly, i want to sort product. This might help you https://stackoverflow.com/q/56669484/8662236 Thanks for your time thou :) Glad to see any optimized query for this. – Anshul Riyal Jul 24 '19 at 13:43
1

If you have ProductCount model then the below query should work.

ProductCount.includes(:product).where(user_id: user.id).order(counts: :desc).map(&:product)
Dyaniyal Wilson
  • 1,012
  • 10
  • 14
  • This query didn't give all the products. suppose if i have 10 products and i and some other user clicked any 6 then it reject rest 4 products and provide only 6 products – Anshul Riyal Aug 07 '19 at 06:25