Thursday, June 23, 2016

How to get most clicked records and at-least one child levels resource will be include

By Hường Hana 11:30 PM , , , Leave a Comment

I have models like this with polymorphic relationship

class Level1  has_and_belongs_to_many :level2s  has_many :resources ,:as => :mediable end  class Level2     has_and_belongs_to_many :level1s     has_many :level3s     has_many :resources ,:as => :mediable end  class Level3     belongs_to :level2     has_many :resources ,:as => :mediable end  class Resource     belongs_to :mediable , polymorphic: true     has_many :resources ,:as => :mediable     has_many :clicks ,:as => :mediable end  class click     belongs_to :clickable , polymorphic: true end

When user add a resource in level1/level2/level3(image or media) I show these media somewhere where user can click on this and each click I save an entry in clicks table

Now I need to when user on level1's show page I need to show top 50 resources of the level1s and level2s combined based on the click counts and at least one resource will be fetched from database

I am going to try like this:

Resource.select("resources.*, count(clicks.id) as click_counts")             .joins( "INNER JOIN clicks ON clicks.clickable_id = resources.id AND clicks.clickable_type='Resource'" )             .where("(resources.mediable_id IN(1) AND resources.mediable_type='Level1') OR (resources.mediable_id IN(1, 2, 3, 4, 5) AND resources.mediable_type='Level2')")             .group("resources.id")             .order("click_counts").limit(50)

It will return top 50 resources related to level 1 and its related level2s but not not guarantee to I have at least one resources related to level2.

can you help me how can I do that

There are possibilities a resource never clicked but I have to get that resources as well as I need at least one resource for each level So I think inner join should change to left outer

1 Answers

Answers 1

If is not a problem for you do a little extra computation, you can achieve it easily with: 

resources = Resource.select("resources.*, count(clicks.id) as click_counts")             .joins( "INNER JOIN clicks ON clicks.clickable_id = resources.id AND clicks.clickable_type='Resource'" )             .where("(resources.mediable_id IN(1) AND resources.mediable_type='Level1') OR (resources.mediable_id IN(1, 2, 3, 4, 5) AND resources.mediable_type='Level2')")             .group("resources.id")             .order("click_counts").limit(50)  unless resources.pluck(:mediable_type).include? 'Level2'    resources = resources.limit(49) + Resource.select("resources.*, count(clicks.id) as click_counts")             .joins( "INNER JOIN clicks ON clicks.clickable_id = resources.id AND clicks.clickable_type='Resource'" )             .where("resources.mediable_id IN(1, 2, 3, 4, 5) AND resources.mediable_type='Level2'")             .group("resources.id")             .order("click_counts").limit(1) end

Otherwise, you could try with something like this (not tested):

sub_select = "(    SELECT resources.*, count(clicks.id) as click_counts, 1 as SortKey    FROM resources     INNER JOIN clicks ON clicks.clickable_id = resources.id AND clicks.clickable_type='Resource'    WHERE resources.mediable_id IN(1, 2, 3, 4, 5) AND resources.mediable_type='Level2'    GROUP BY resources.id    ORDER BY mediable_type, click_counts     LIMIT 1     UNION ALL     SELECT resources.*, count(clicks.id) as click_counts, 2 as SortKey    FROM resources     INNER JOIN clicks ON clicks.clickable_id = resources.id AND clicks.clickable_type='Resource'    WHERE (resources.mediable_id IN(1) AND resources.mediable_type='Level1') OR (resources.mediable_id IN(1, 2, 3, 4, 5) AND resources.mediable_type='Level2')    GROUP BY resources.id    ORDER BY SortKey, mediable_type, click_counts) as t"  select_sql = "SELECT DISTINCT resources.*, click_counts FROM #{sub_select} LIMIT 50"  results = ActiveRecord::Base.connection.select_all(select_sql).rows # array results_ordered = results.sort { |a, b| a.last <=> b.last }

Notes:

  • The SortKey extra attribute guarantee the queries order
  • UNION ALL does not eliminate any duplicate record. So we need to add a DISTINCT on resources.*, click_counts columns to remove a possibile duplicate record (the first of 'Level2')

Hope it helps!

If You Enjoyed This, Take 5 Seconds To Share It

0 comments:

Post a Comment