Improving the design of a JPA polymorphic association

I have the following JPA entity hierarchy:

Upon logging in to the application a user has an instance of UserAccount in session; then depending on the concrete type of the organization associated to that UserAccount (Admin, Business or Family), a different dashboard/screen is displayed to the user e.g. if the user is a Business, then the dashboard for that business is displayed.

My concern with that design is that I have to do a instanceof check each time a user logs in so that I know which type of dashboard to display. I could also avoid that instanceof check by having a property in UserAccount such as organizationType (which would take one of three values) but then there would be redundant information.

Is there a way to improve my design? If so how?

1 Answers

Answers 1

Be greedy and get both, without redundancy.

Depending on the inheritance strategy, you may already have the organizationType info and you can expose it for free.

@Entity @Inheritance(strategy = InheritanceType.SINGLE_TABLE) @DiscriminatorColumn(name = "DTYPE") public abstract class AbstractOrganization implements Serializable {     @Id     protected Long id;      @Column(name = "DTYPE", insertable = false, updatable = false)     protected String organizationType;      ...  } 

the same applies also to the JOINED strategy.

Do not implement the setOrganizationType method.

Since a discriminator is required for simulating hierarchies with tables (except TABLE_PER_CLASS strategy), there's no redundancy and JPA provider will handle this attribute for you.

Read More

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

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!

Read More