Liferay custom query cast issue

Question

I'm using Liferay service builder with a custom query that uses an inner join across two tables.

I also have a blank service that defines the Model that this will return.

AuditExportFinderImpl.java

public List<AuditExport> getAuditExport(String auditIds) {

        SQLQuery q = buildQuery(auditIds);
        List<AuditExport> audits = (List<AuditExport>)q.list();

        return audits;
    }

    private SQLQuery buildQuery(String auditIds) {

        Session session = null;
        SQLQuery q = null;

        try {

            session = openSession();
            String sql = CustomSQLUtil.get(FIND_EXPORT_AUDITS);
            sql = StringUtil.replace(sql, "[$AUDIT_IDS$]", auditIds);
            q = session.createSQLQuery(sql);
            q.setCacheable(false);
            q.addEntity("AuditExport", AuditExportImpl.class);


        } finally {
            closeSession(session);
        }

        return q;
    }

default.xml (Custom Query):

<custom-sql>
    <sql id="findExportAudits">
        <![CDATA[
        SELECT
        audititem.auditid,
        audititem.orgid,
        audititem.userid,
        audititem.username,
        audititem.firstname,
        audititem.lastname,
        audititem.createdate,
        audititem.auditaction,
        auditdetail.auditdetailkey,
        auditdetail.auditdetailvalue
        FROM audititem
        INNER JOIN auditdetail ON audititem.auditid = auditdetail.auditid
        WHERE audititem.auditid IN ([$AUDIT_IDS$])
 ]]>
    </sql>
</custom-sql>

This returns the resultset list (cast correctly to an AuditExport) but the joined data AuditDetail table is the same for every record, see below:

When not using a cast the service returns the correct data, see below (some data masked for privacy):

Why is it replicating the data and how do I get the resultset to cast correctly?

I can provide more information if needed, thanks.

Answer 1

Ok, so my issue was in my service.xml.

In the model I was casting too (defined in the service.xml) had only the AuditId defined as the primary key, but I was retrieving a dataset that contains many of these.

To fix this I defined a composite key of AudiId and AudiDetailKey in service.xml, this then cast the dataset correctly.