Malformed object returned by query from a one-to-many Grails domain

Question

Consider these two Grails domain classes:

class Agreement implements Serializable {
    String code
    String branchCode
    ...

    static belongsTo = [agency: CollectingAgency]

    static mapping = {
        ...
    }
}

class CollectingAgency implements Serializable {
    String type
    String agencyClass
    ...

    static hasMany = [agreement: Agreement]

    static mapping = {
        ...
    }
}

Now when I perform Agreement.findAll() it creates a sql similar to this (using loggingSql = true):

select agreement0_.agency_id as agency4_67_1_, agreement0_.AGREH_CD as
    AGREH1_1_, agreement0_.AGREH_BRCHCD as AGREH2_1_,
    ...
    agreement0_.agency_id as agency4_66_0_,
                ^^^^^^^^^
    ...
from RVAGREHDROTB agreement0_
where agreement0_.agency_id=?

The statement will not execute because of the unknown column(agency_id). And I wonder where do it gets the agency_id column? I haven't mapped any column with such name.

And when I try to query from the other side using CollectingAgency.findAll(), it returns a malformed Object:

[
    {
        type: "0400300",
        agencyClass: "12",
        ...
        agreement: [

Yes, like that, with the agreement key having an opening square bracket [ but no closing bracket ]. Plus, the other properties of the table are not retrieved.

How can I achieve a query with a resulting object similar to these:

Agreement.findAll():

[
    {
        code: "1212",
        branchCode: "a014s",
        ...
        agency: {
            type: "0400300",
            agencyClass: "12",
            ...
        },
        ...
    },
    {
        code: "1213",
        branchCode: "a014z",
        ...
        agency: {
            type: "0400300",
            agencyClass: "12",
            ...
        },
        ...
    },
    ...
]

and CollectingAgency.findAll():

[
    {
        type: "0400300",
        agencyClass: "12",
        ...
        agreement: [
            {
                code: "1212",
                branchCode: "a014s",
                ...
            },
            {
                code: "1213",
                branchCode: "a014z",
                ...
            },
            ...
        ]
    },
    ...
]

Answer 1

In your Agreement class you have

static belongsTo = [agency: CollectingAgency]

which will create an 'agency' field in your class. the agency_id you see just maps your 'agency' field to the 'agency' column in the database.