grails - get column in query with joint table

Question

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 1

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