RE: [coldbox:16119] How to use output of a query inside another query

Communities ColdBox HMVC
1

Hello Ritu,

Welcome to ColdBox! So it sounds like what you’re asking how to make additional database calls inside of the output of a first database call, when then handler code has already run and now you’re in the view.

Here are three possible suggestions.

  1. If possible, rewrite your database logic in such a way that all the needed data is returned in the first result set (including the counts) so only a single trip to the database is needed. This should improve performance as well. This should be fairly easily achievable with a derived table, or grouping and using a count() aggregate in your SQL. This is my recommended approach.

  2. Move the GetData loop into the handler or a service method, and dynamically add the count into a new column in the table, or store up the data into an array or struct that can then be passed along to the view. That way all necessary data is collected and wrapped up before the view is rendered. MVC purists will like this because it “keeps business logic out of the views” but it also makes you write more code. This is probably my least favorite option due to the extra massaging of the data.

  3. Pass along a reference to a service to the view (in the request collection) to get the counts. Then, in your GetData loop in the view you can call something like #rc.CallQueueService.getQueueCount(GetData.CallBatchId)#. If option 1 wasn’t a possibility, this is what I would do.

Let us know if those options don’t make sense. We can help explain further once you decide which route to take.

Thanks!

~Brad

ColdBox Platform Evangelist
Ortus Solutions, Corp

E-mail: brad@coldbox.org
ColdBox Platform: http://www.coldbox.org
Blog: http://www.codersrevolution.com

2

Hi Brad ,

Thanks for the reply . First option i tried to implement and i am still trying to do that to merge query into one , in case if it not happen
can you please elaborate third option i.e. how to pass reference to service , service word i am listening fr fist time in coldbox.

Thanks