How to get value using join table with different values?

Question

I have a product, user and product_click model. In product_click, I have column count where I'm keeping a record, which user click which product how many times.

I have also tried

Product.sort_by{ |r|  r.product_clicks.where(user_id: user.id).first.try(:count).to_i

which giving desire result but creating lots of query on running.

product_click.rb

belongs_to :product
  belongs_to :user

product.rb

has_many :product_clicks, dependent: :destroy

user.rb

has_many :product_clicks, dependent: :destroy

Schema

create_table "product_clicks", force: :cascade do |t|
    t.bigint "product_id"
    t.bigint "user_id"
    t.bigint "count", default: 0
    t.datetime "created_at", null: false
    t.datetime "updated_at", null: false
  end

In controller

Product.left_outer_joins(:product_clicks).order("count DESC NULLS LAST")

Now i am looking something like this to work.

But here i can't able to check whether the count was of which particular user. If i'm adding

Product.left_outer_joins(:product_clicks).where(resource_clicks: { user_id: user.id }).order("count DESC NULLS LAST")

Then it showing a product which user clicked but not the 'nil' ones(which i never clicked).

I also tried this

Product.left_outer_joins(:product_clicks).where(product_clicks: { user_id: [user.id, nil] }).order("count DESC NULLS LAST")

but giving me all i clicked and all which no one else clicked. If someone click which i didn't then it not displaying to me.

Eason

What is your expected output? I mean, it's unclear what you wanna to show. Can you add the columns of product_clicks? If it's a join table, consider to add has_many through relation. What's resource_clicks? – Excitor

@Excitor hi, updated the question. Thanks for the feedback. I want sorted product with maximum clicked product on top by a particular user, who is accessing the app currently. – Eason

@T J any references? – Eason

@philipxy Long story short, i need product in sorted where most clicked on top. and count of click by user storing in Product_click table. i want to show sorted products for that particular user and rest products below. Soon i will update the question. Thanks. – Eason

Answer 1

While I am thinking of other ways, one straight way would be a 2 step solution.

product_ids = ProductClick.where(user_id: user.id).order('count desc').pluck(:product_id)

products = Product.where(id: product_ids)

It'll definitely improve your time and is better than your query (which is N+1).

Using preloading

Product.includes(:product_clicks).where('product_clicks.user_id' => user.id).order('product_clicks.count desc')

The above query should also result in the same way, however, it may internally hit 2 queries anyway.

Using having clause - Recommended

Product.includes(:product_clicks).having('product_clicks.user_id' => user.id).order('product_clicks.count desc')

Update - Using OR

Product.includes(:product_clicks).where('product_clicks.user_id' => user.id)
       .order('product_clicks.count desc')
       .or(
        Product.includes(:product_clicks)
               .where('product_clicks.user_id' => user.id)
               .where(product_clicks: { id: nil })
       )

Nilsson

@Farhan Thanks for taking time out, i am able you archive what you are trying to explain but here problem is, I'm not getting the product which user never clicked. – Eason

same thing happening with this, Product.includes(:product_clicks).where('product_clicks.user_id' => user.id).order('product_clicks.count desc') it not showing those product which another user clicked but i didn't. – Eason

@AnshulRiyal I have updated the answer with new query. Unfortunately I don't have a similar to schema to test it, try and let me know if there's an error or something. – Nilsson