Room: error: Not sure how to convert a Cursor to this method’s return type (void)

I have this query set up to return all the records from these tables and display the information on a recyclerview in android. the DB is set up using the Room persistence library aka SQLITE.

@Query
("SELECT moodBeforetable.userId, 
moodBeforetable.moodBefore, 
moodBeforetable.cbtId, 
cbtTable.automaticThought, 
cbtTable.twistedThinkingPK, 
cbtTable.challengeThought, 
cbtTable.rationalThought,  
cbtTable.date, 
moodAfterTable.moodAfter, 
twistedThinkingTable.twistedThinkingPK,
twistedThinkingTable.allOrNothing,  
twistedThinkingTable.blamingOthers,
twistedThinkingTable.catastrophizing, 
twistedThinkingTable.emotionalReasoning, 
twistedThinkingTable.fortuneTelling, 
twistedThinkingTable.labelling, 
twistedThinkingTable.magnifyingTheNegative, 
twistedThinkingTable.mindReading, 
twistedThinkingTable.minimisingThePositive,
twistedThinkingTable.overGeneralisation, 
twistedThinkingTable.selfBlaming, 
twistedThinkingTable.shouldStatement 

FROM moodBeforetable

JOIN cbtTable ON moodBeforetable.cbtId = cbtTable.cbtId
JOIN twistedThinkingTable ON cbtTable.cbtId = twistedThinkingTable.cbtId
JOIN moodAfterTable ON moodAfterTable.cbtId = cbtTable.cbtId

WHERE moodBeforetable.date >= datetime('now', '-1 year')
AND moodBeforetable.userId = :userId 
ORDER BY :date DESC")

LiveData<List<MoodBeforeTable>> moodLogsAll (int userId, String date);

When I try to compile the app I get the following error:

The query returns some columns which are not used by com.example.feelingfit.persistence.tables.MoodBeforeTable. You can use @ColumnInfo annotation on the fields to specify the mapping.

Could anyone help me debug this and find out why the app wont compile?

Answer

Problem is Room cannot map the result from your custom query to existing MoodBeforeTable. It is because your return type is List<MoodBeforeTable> but you have used joins using TwistedThinkingTable and MoodAfterTable and so on.

What you should do is create a new POJO like below:

public class MoodLogPojo() {
    private int userId;
    private String moodBefore;
    zprivate int cbtId;
    private String automaticThought;
    private int twistedThinkingPK;
    private String challengeThought;
    private String rationalThought;
    private String date;
    private String moodAfter;
    private int twistedThinkingPK;
    private String allOrNothing;
    private String blamingOthers;
    private String catastrophizing;
    private String emotionalReasoning;
    private String fortuneTelling;
    private String labelling;
    private String magnifyingTheNegative;
    private String mindReading;
    private String minimisingThePositive;
    private String overGeneralisation;
    private String selfBlaming;
    private String shouldStatement;

    // generate getters and setters too
    public void setSelfBlaming(Stirng selfBlming) {
        this.selfBlming = selfBlming
    }

    public String getSelfBlaming() { return selfBlaming; }

    // and so on ...

}

Then use this class as the return type like :

LiveData<List<MoodLogPojo>> moodLogsAll (int userId, String date);.

NOTE: Mind the MoodLogPojo class. Modify it accoring to the corresponding data types from each Entity.