newbie newbie - 7 months ago 26
Groovy Question

grails - get column in query with joint table

I have a criteria set up like this:

def e = Equipment.createCriteria()
def equipmentInstanceList = e.list {
createAlias "rentals", "r", org.hibernate.sql.JoinType.LEFT_OUTER_JOIN
projections {
property('name')
property('r.status')
property('r.dateRented')
property('r.dateReturned')
}
}


I get the idea that it will return 2 dimensional list. as mentioned here. From what I've understand the 1st list is the list of the selected items, and the 2nd list is the list of the columns of the items. My question is

How can I get all the selected items? Example:

def list = equipmentInstanceList.[all] //supposed to get all the selected items
def a = equipmentInstanceList[1] //will only get the 1st item in 1st list


How can I access the specific column in 2nd list? Example:

def a = equipmentInstanceList[1].status //supposed to get the status column in 2nd list


EDIT:

This query has the same result as the criteria above.

def equipmentForRent = Equipment.executeQuery("SELECT e.name, r.status, r.dateRented, r.dateReturned FROM ers.Equipment e LEFT JOIN e.rentals r")


I'm still trying to get the columns in
equipmentForRent
. What I'm trying so far.

each.equipmentForRent { e -> println e.dateReturned }


Still get this error:

Exception evaluating property 'dateRented' for java.util.Arrays$ArrayList, Reason: groovy.lang.MissingPropertyException: No such property: dateRented for class: java.lang.String


Why is it different from this query:

def rentalTest = Rental.executeQuery("FROM Rental")
println rentalTest.status //no error, returns the status column from rental


Anyone?

Answer Source

You have raised two questions in one: quickly:

How can I get all the selected items? Example:

How can I access the specific column in 2nd list? Example:

def a = equipmentInstanceList[1].status //supposed to get the status column in 2nd list

To get 2nd:

def a = equipmentInstanceList[2].status // this now points to 2nd element status

But none of this is null safe and could lead to issues. To iterate through an element with an index:

equipmentInstanceList?.eachWithIndex{  e,i->
  println "-- $i is index $e is element"
 }

Your query you have tried to do sql query in HQL

def equipmentForRent = Equipment.executeQuery("SELECT e.name, r.status, r.dateRented, r.dateReturned FROM ers.Equipment e LEFT JOIN e.rentals r")

Try wrapping select new map(items) from table like shown

def equipmentForRent = Equipment.executeQuery("SELECT new map(e.name as name, r.status as status, r.dateRented as dateRented, r.dateReturned as dateReturned) FROM Equipment e LEFT JOIN e.rentals r")