CODEWITHSUNDEEP
grailshqlgrails-orm
Universitas
Universitas

Reputation: 493

Need help creating Grails executeQuery based on domain and lookup table

I have two classes: sample, and parameter. I also have a sample_sample_parameter lookup table which exists to hold the sample id and the parameter id. This is mapped in my grails app.

I was able to write an sql query that works in squirrel:

select s.* from sample s, sample_sample_parameters sp where s.id = sp.sample_id and sp.sample_parameter_id = 41

where 41 would be replaced with the parameter.id variable passed from the gsp page to the action. I have also tried to make it work with executeQuery but it tells me that sample is not mapped.

How do I turn this query into a gorm recognizable form? 

class Sample {


Date collectionDate // date the sample was collected
Date sampleReceivedDate // date the sample arrived on site
Date dateCreated
String sampleComments // details about the sample
String labAccessionID // internal reference
String sampleGender // is it from a male or female?
String sampleAge // the age of the animal the sample was taken from
String sampleBreed // the breed of animal
String sampleNameID // patient name
String filepath

String enteredby


String sg
String mosm

static searchable = true


static hasMany =[sampleParameters:SampleParameter, ficsruns:Ficsrun]//[tags:Tag]// a Sample can have many parameters
/* mappedBy allows two tables share a common table. It creates two join tables, one for each. 
 * SampleParameter is the table being shared by Sample and SampleType tables */
static mappedBy=[sampleParameters:"samples"]//[tags:"domainClass1s"]/*http://www.van-porten.de/2010/09/multiple-many-to-many-in-grails/*/
static belongsTo = [sampleType:SampleType, labFinding:LabFinding, sampleSource:SampleSource, species:SpeciesList] // creates dependencies


static constraints = {

    sampleType(blank:false)
    sampleNameID(blank:false)
    collectionDate(blank:false)
    sampleReceivedDate(blank:false)
    sampleComments(nullable:true, maxSize:1000)
    labAccessionID(nullable:true)
    sampleGender(blank:false, inList:["M","F","NM","SF", "UNK"])
    sampleAge(nullable: true)
    sampleBreed(nullable:true)
    sampleSource(blank:false)
    species(blank:false)
    labFinding(nullable:true)
    filepath(nullable:true)
    enteredby(nullable:true)
    sg(nullable:true)
    mosm(nullable:true)
    dateCreated()
}

/* This section is for static mapping to the hematology database*/
   static mapping = {
    version false
    id generator:'sequence', params:[sequence:'SHARED_SEQ']   
   }

   String toString(){
    "${sampleNameID}"
  }
}


class SampleParameter implements Comparable{


String name
String value

static hasMany = [
samples:Sample,         //domainClass1s: DomainClass1,
sampleTypes:SampleType  //domainClass2s: DomainClass2
]
static mapping = {
    version false
    id generator:'sequence', params:[sequence:'SHARED_SEQ']   
}

  static mappedBy =        [samples:"sampleParameters",sampleTypes:"sampleParameters"]//[domainClass1s: "tags", domainClass2s: "tags"]
  static belongsTo =[Sample,SampleType] //[DomainClass1, DomainClass2]

  static constraints = {
     name(blank:false)
     //value()
     //value(unique:true)
     value (unique: 'name')
 }

@Override public String toString() {
return name + " " + value
}

@Override
public int compareTo(Object o) {
    if (o == null || this == null) {
        return 0;
    } else {
        return value.compareTo(o.value)
    }
}
}

Upvotes: 1

Views: 2360

Answers (3)

James Kleeh
James Kleeh

Reputation: 12228

List<Sample> samples = Sample.findAllBySampleParameter(SampleParameter.get(variable))

Give it a try?

Upvotes: 1

Tiago Farias
Tiago Farias

Reputation: 3407

The problem is that you're not using a HQL query in your executeQuery method. Instead you're using native sql. From the manual:

The executeQuery method allows the execution of arbitrary HQL queries.

Take a look at the specification to see ways of doing that. Which, by the way, are way easier than native sql.

Upvotes: 1

fluxon
fluxon

Reputation: 548

As a first suggestion, when you have the paramter's id, do the following.

Parameter p = Parameter.get(params.id) // or wherever your id is stored
List<Sample> samples = Sample.findAllByParameter(p) // this assumes, the parameter property is actually named 'parameter'

Of course there is no error handling in place right now, but you'll get the idea.

Welcome to GORM, welcome to Grails.

Upvotes: 2

Related Questions