Query in view?

Communities ColdBox HMVC
#1

HI,

I have this code in my handler:

    <cfset prc.qData = instance.SomeService.getData() />

In my view, I need to loop over this query and run another query.
What is the best way to do this? I'm trying to avoid having any
<cfquery> or <cfstoredproc> calls in my view as it just seems old
school, but I need to loop over this query and use a value from one of
the columns in this inner query. Is there a more elegant way to do
this?

<cfloop query="prc.qData">

   <!--- inner query here --->

</cfloop>

Thanks,

West

#2

You could set a reference in the prc to the service method that runs
the query in your handler and then pass in the arguments you need from
prc.qData loop by calling the function from the prc scope.

.brett

#3

That seems to work interestingly enough. I like it better than having
my queries in my view for sure.

I'd be interested to hear how any others may handle this situation,
but what you've suggested seems to work fine.

#4

I did notice that any properties that were injected into my Service
like the DSN don't seem to be available when doing it this way
though. At the top of my service I have this:

<cfproperty name="dsn" inject="coldbox:datasource:dsn_myapp" />

For some reason, this property is no longer available now. Any idea
why that might be and is there something I can do differently to fix
it?

#5

I would prepare the data before-hand. So, have a separate service call which gets your original data, loops over it, calls another service within each loop and manually populate a new query with queryAddColumn etc. Then pass this new query down to your view.

#6

Interesting, I like that approach too.

The downside being that you'd be looping over the resultset twice to
render the view?

#7

Try referencing the service in the prc instead of the method.

#8

It depends if the nested query is bringing back multiple records or not.
If it’s always going to be 1 row of data, you could just manually add the new fields to the original query.
If it’s going to be multiple rows, then you could add the nested query into a new field of the original query, but yes, this means you would have to loop over the nested data in the view.