Search code examples
hibernatejoingrailsgroovyhql

Grails how to show HQL JOIN value in g:each

i want to ask how can i show this code result on view page

Controller

query =" select subs.firstName, trx.currentBalance from Transaction trx, Subscriber subs where 
trx.subscriberID = subs.msisdn and trx.subscriberID = '0' and trx.date ='2020-09-04'"
query= Transaction.executeQuery(query);

with this code :

View Page

<g:each in="${listQueryTable}" status="i" var="userInstance">
<tr>
<td>${userInstance.firstName}</td>      <-- this code does not work for me
<td>${userInstance.currentBalance}</td> <-- this code does not work for me
</tr>
</g:each>
Solution

  • Using the HQL query like yours in the controller you can display the values of it in the GSP using indicies like so:

    <g:each in="${listQueryTable}" status="i" var="userInstance">
<tr>
<td>${userInstance[0]}</td> 
<td>${userInstance[1]}</td>
</tr>
</g:each>

    because each element of listQueryTable is an Object[].

    As a variation, you can turn each list's element into a map like so:

    query =" select subs.firstName, trx.currentBalance, ..."
listQueryTable = Transaction.executeQuery(query).collect{ [ firstName:it[0], currentBalance:it[1], ... ] }

    then you can use your original GSP for display.

    The proper solution would be to get rid of raw HQL query and use Hibernate/GORM means to build the model for View. You have to add a reference between you domain classes and use dynamic finders or criteria query.