1

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 ?

belgoros
  • 3,590
  • 7
  • 38
  • 76
  • I Think I found the solution: – belgoros Dec 18 '17 at 10:38
  • 1. Find the Site by store_number: ` Site aSite = Site.findFirst("store_number=?", STORE_NUMBER); 2. Find `SitesSettings` list for the found site and include the `Parameters`: `List siteSections= SiteSection.where("site_id=?", aSite.get("id")).include(Parameter.class);` 3. Then loop on `site_settings` and get the corresponding `parameters`: `for (SiteSection siteSection : siteSections) { List siteParams = siteSection.getAll(Parameter.class);` 4. Loop on Parameters: `for (Parameter siteParam : siteParams) {` and find the needed values. – belgoros Dec 18 '17 at 12:59

1 Answers1

0

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")

ipolevoy
  • 5,432
  • 2
  • 31
  • 46
  • Thanks a lot, Igor, - a cool feature `getId`; `getLongId`, didn't know that :). Is it possible to convert to JSON an error when a record not found ? – belgoros Dec 18 '17 at 14:25
  • Actually I return from the controller `respond(notFound(param("storeNumber")));` where `notFound` method just returns `return "No site found for the provided store number: " + storeNumber;` – belgoros Dec 18 '17 at 14:39
  • sure, do not forget to set the status code: `respond(message).status(404);`, and do not forget to test that you even have a parameter: `if(!blank("storeNumber"))` ... – ipolevoy Dec 18 '17 at 21:31
  • But when I add `status(404)` to the response: `respond(notFound(param("storeNumber"))).status(404);`I get the template not found error: `Failed to render template: '/system/error.ftl', with layout: '/layouts/default_layout'; Template "/system/error.ftl" not found.`. Should I really have it in case of JSON response only ? – belgoros Dec 19 '17 at 10:37
  • Is there a more elegant way to send an error message as JSON instead of this way: `JsonHelper.toJsonString(new String("No site found for the specified store number"));` ? Should I use a Error class as a wrapper to be able to populate its attributes and convert it to JSON the same way ? – belgoros Dec 19 '17 at 10:53
  • I've found this way with `map` method: `return JsonHelper.toJsonString(map("error", "No site found for the specified store number: " + storeNumber));` – belgoros Dec 19 '17 at 13:06
  • you should not delete `/system/error.ftl`. Framework needs it to display errors. Also, look in the log file to see what error it is trying to render – ipolevoy Dec 19 '17 at 14:11
  • Igor, there are no views in the current application, it will be an API. I have defined abstract APIController that returns JSON and has no layout: `public abstract class APIController extends AppController {@Override protected String getContentType() { return "application/json"; } @Override protected String getLayout() { return null; }` – belgoros Dec 19 '17 at 14:48
  • you still need that file so that the framework could display internal errors not caught in your code. You can edit that file to display JSON. In fact, I use Freemarker all the time to build JSON web services. Here is a pefrect example of a collection displayed in JSON using ActiveWeb https://github.com/javalite/activeweb-rest/tree/master/src/main/webapp/WEB-INF/views/people – ipolevoy Dec 19 '17 at 14:51
  • Thank you very much, I'll take a look. Nevertheless, it seems a little bit unusual to have views to display JSON. I'll take a try. – belgoros Dec 19 '17 at 15:38
  • trust me, views will provide a LOT of flexibility you do not have now – ipolevoy Dec 21 '17 at 21:34