DataBase results with column data using Grails Domain Model

[mwilliamson]

Hi,

Hope I am not writing this in wrong place.

I am currently porting some of my php API features into my Grails app. The php app was doing data access to a mysql database. And had a command called get_records_sql which return data based on key:value pairs. The key being column name, and value being row result. Example
"[{"id":"1","name":"joe"},{"id":"2","name":"Bob"}]" which was very handy when transforming into JSON as the key value pairs were maintained. And could be passed to views allowing data easily being retrieved via its key.

Now in grails, I have a GORM Domain model which corresponds to my database table. Now with the domain model I execute a query using the same mysql command as I previously used in php. However, this returns the result like follows "[[1,"joe"],[1,"Bob"]]" with no column data attached.

Anyone know if I can use Grails to return data from a database with column data linked to the row result. Hope that makes sense. Appreciated any help

best,
Mark

 

[dave]

Hey Mark, Grails lets you take a list of objects and spit it back out immediately in JSON for... say you have a list derived from

def myList = Customer.findAll()

try rendering the output with - render myList as JSON

 

[mwilliamson]

Hey Mark, Grails lets you take a list of objects and spit it back out immediately in JSON for... say you have a list derived from

def myList = Customer.findAll()

try rendering the output with - render myList as JSON

Hi Dave,

Thanks for the advice. I gave it a go but I'm afraid it resulted in a similar string to what I already had. Example [[1,"Joe"],[1,"Bob]]. Surprising how little information I can find on this little problem online.

Best,
Mark

 

[mwilliamson]

Hey Mark, Grails lets you take a list of objects and spit it back out immediately in JSON for... say you have a list derived from

def myList = Customer.findAll()

try rendering the output with - render myList as JSON

Apologies, you were correct.

I was doing

 def list = Domain.executeQuery(" SQL ....")

which did not return the column data with the results. However, when I tried

 def list = Domain.findAll(" Exclude the Select part then add SQL ....")

it did return the results with column data intact.

Now to figure out how to return just the columns needed opposed to all columns which I currently get back as the findAll with sql does not seem to work will a full sql statement. Example If I use "Select table.id, table.name from package.domain as table where table.id = 1" it does not work but if i use "Domain.findAll("from package.domain as table where table.id = 1")" it does.

Best,
Mark

 

[mwilliamson]

Came across another way of doing what I wanted using HQL with the GORM.executeQuery command. Good example after the link: http://grails.1312388.n4.nabble.com/Domain-executeQuery-how-to-get-field-names-in-result-td1357781.html