问题描述
我有一个产品，用户和product_click模型.在product_click中，我有一个count列，我在其中记录一个记录，哪个用户单击哪个产品多少次.
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.
我也尝试过
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
product_click.rb
belongs_to :product
belongs_to :user
product.rb
product.rb
has_many :product_clicks, dependent: :destroy
user.rb
has_many :product_clicks, dependent: :destroy
模式
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
在控制器中
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).
我也尝试过
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.
推荐答案
虽然我在考虑其他方法，但一种直接方法是两步解决方案.
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)
它肯定会改善您的时间，并且比您的查询(N + 1)要好.
It'll definitely improve your time and is better than your query (which is N+1).
使用预加载
Product.includes(:product_clicks).where('product_clicks.user_id' => user.id).order('product_clicks.count desc')
上面的查询也应该以相同的方式产生结果，但是无论如何它可能在内部命中2个查询.
The above query should also result in the same way, however, it may internally hit 2 queries anyway.
使用Haveing子句-推荐
Product.includes(:product_clicks).having('product_clicks.user_id' => user.id).order('product_clicks.count desc')
更新-使用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 })
)
这篇关于如何使用具有不同值的联接表获取值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！