How to get parents by IDs when using polymorphic associations

Question

I have a many-to-many relation table site_sections with the following columns:

id
site_id
section_id

which is used a join table between sections and sites tables. So one site has many sections and a section is available in many sites. Sites table has the following columns:

id
store_number

The sites_sections table is used in a polymorphic association with parameters table.

I'd like to find all the parameters corresponding to the site sections for a specific site by its store_number. Is it possible to pass in an array of site_settings.id to SQL using the IN clause, something like that:

Parameter.where("parent_id IN (" + [1, 2, 3, 4] + ") and parent_type ='com.models.SiteSection'");

where [1, 2, 3, 4] should be an array of IDs from sites_sections table or there is a better solution ?

Answer 1

Your solution is correct:

Site aSite = Site.findFirst("store_number=?", STORE_NUMBER); 
List<SiteSection> siteSections= SiteSection.where("site_id=?", aSite.getId()).include(Parameter.class); 
for (SiteSection siteSection : siteSections) { 
  List<Parameter> siteParams = siteSection.getAll(Parameter.class);
  for (Parameter siteParam : siteParams) { ... }
}

In addition, by using the include(), you are also avoiding an N+1 problem: http://javalite.io/lazy_and_eager#improve-efficiency-with-eager-loading

However there can be a catch. If you have a very large number of parameters, you will be using a lot of memory, since include() loads all results into heap at once. If your result sets are relatively small, you are saving resources by running a single query. If your result sets are large, you are wasting heap space.

See docs: LazyList#include()

Side note: use aSite.getId() or aSite.getLongId() instead of aSite.get("id")