MorkPork MorkPork - 2 years ago 129
SQL Question

Grails displaying values from two tables using primary and foreign keys

This is, I hope, probably quite obvious but I can't find an example that I think answers my issue.

I have an SQL database that I cant modify and within it are two tables, linked with primary/foreign keys (test_scenario and test_exec_queue respectively, so the PK value from test_scenario can show up many times within test_exec_queue) and when I display the data on screen I want it to, instead of displaying the FK value from test_exec_queue I want it to use that to get testScenarioName from the test_scenario table and display that instead.

So far my class looks like this but I've no idea what to put in to do the above logic, or do I do this somewhere else? In the controller? Any help appreciated

class TestExecQueue {

static constraints = {
testscenarioid(blank:false, editable:false)
myPriority(inList:[0,1,2,3,4], blank:false)
myState(inList:["READY"], blank:false)
}

static mapping = {
table "test_exec_queue"
version false
columns{

id column:"test_exec_queue_id"
testscenarioid column:"test_scenario_id"
myPriority column:"Priority"
myState column:"State"
}
}

Integer testscenarioid
Integer myPriority
String myState
}

Answer Source

You need to create a class which maps the test_scenario table in addition to the TestExecQueue class you've already implemented.

In your TestExecQueue class, you would link to the scenario by class, rather than by an integer field:

class TestExecQueue {
    static mapping = {
        scenario column:'test_scenario_id'
    }

    TestScenario scenario
}

Note: This is one example of mapping the relationship, you should review the Domain Modeling section of the Grails Documentation for other options.

The display of the classes is entirely dependent on your controllers and views, and that would need more elaboration on your part to answer clearly. One option is to set the public toString() methods on the classes that will be printed.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download