Thorin Thorin - 5 months ago 9
MySQL Question

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

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

Answer

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 } # sort by clicks_count

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!