Help with SQLTemplate and SQLResult

[Ramiro Aparicio]

Hi,

I don't know if I am trying to do something really strange but I have 
been struggling a full day with this without any success, so maybe any 
of you can help me.

I have the following query:

(
    SELECT
    d.*,
    u.*
    FROM document d
    JOIN doc_grants_user dgu on dgu.Document_idDocument = d.idDocument
    JOIN user u on u.idUser = dgu.User_idUser
    WHERE d.ProtOnDomain_idProtOnDomain = 1
    AND dgu.User_idUser in (
        SELECT User_idUser
        FROM user_email
        WHERE email NOT LIKE "%prot-on.com"
    )
)
UNION
(
    SELECT
    d.*,
    u.*
    FROM document d
    JOIN doc_grants_group dgg on dgg.Document_idDocument = d.idDocument
    JOIN group_membership gm on dgg.GroupOfUsers_idGroup
    JOIN user u on u.idUser = gm.User_idUser
    WHERE d.ProtOnDomain_idProtOnDomain = 1
    AND gm.User_idUser in (
        SELECT User_idUser
        FROM user_email
        WHERE email NOT LIKE "%prot-on.com"
    )
)

This query is stored on the map and all I want is to be able to retrieve 
all de user and document data.

This is the current code I am running to retrieve the data

        SQLTemplate query = (SQLTemplate) 
objContext.getEntityResolver().lookupQuery("ExternUsersDocumentReport");
        EntityResult documentResult = new EntityResult(Document.class);
        documentResult.addDbField(Document.ID_DOCUMENT_PK_COLUMN, 
"idDocument");
        EntityResult userResult = new EntityResult(User.class);
        userResult.addDbField(User.ID_USER_PK_COLUMN, "idUser");
        userResult.addDbField(User.USERNAME_PROPERTY, "username");
        SQLResult resultDescriptor = new SQLResult();
        resultDescriptor.addEntityResult(documentResult);
        resultDescriptor.addEntityResult(userResult);
        query.setResult(resultDescriptor);
        List<Object[]> objects = objContext.performQuery(query);

Here I have some questions:
 1.- Why NamedQueries can not use SQLResults? (Not a big deal but 
lookupQuery seems hackish)
 2.- What is the difference between addDbField and addObjectField?  I 
can not understand the difference reading the JavaDoc but using 
addObjectField causes NullPointerException
 3.- This SQLResult use case with 2 different classes is supported? The 
examples does not show anything like this and I get the 2 separate 
DataRows but with no column name and I just get null objects if I use 
objectFromDataRow.

I have tried using #result in the query resulting only with User fields 
and I get

'ResultSetMetadata' has less elements then 'columns'.


The main problems seems to be that DataRow results have no column name, 
but I don't know what I am doing wrong.

Maybe if someone can point me to some example code it can help, but I 
couldn't find anything :(

Thanks in advance.

Ramiro Aparicio