Android Room Subquery unmatched field error

Question

I can't figure out how to bind a subquery select within an attribute of the entity, I simply need to bind the "main_game" subquery into the mainGame String attribute of my entity:

My DAO:

@Dao
public interface GamesDao {

        @Query("SELECT *, (SELECT g_nested._id FROM games as g_nested, game_groups as gg WHERE g_nested._id = gg._game_group AND g_nested.is_main = 1 AND gg._game = g._id) as main_game FROM games as g")
    List<GameEntity> getAll();
}

My GameEntity:

@Entity(tableName = "games")
public class GameEntity extends GenericIndexEntity {

    @NonNull
    @ColumnInfo(name = "gen")
    private Integer gen;

    @NonNull
    @ColumnInfo(name = "is_main")
    private Integer isMain;

    @Ignore
    @ColumnInfo(name = "main_game")
    private String mainGame;

    public GameEntity(@NonNull String indexNumber, String name, String nameEs, String nameIt, String nameFr, String nameDe, String namePt, String nameRu, @NonNull Integer gen, @NonNull Integer isMain, String mainGame) {
        super(indexNumber, name, nameEs, nameIt, nameFr, nameDe, namePt, nameRu);
        this.gen = gen;
        this.isMain = isMain;
        this.mainGame = mainGame;
    }

    @NonNull
    public Integer getGen() {
        return gen;
    }

    public void setGen(@NonNull Integer gen) {
        this.gen = gen;
    }

    @NonNull
    public Integer getIsMain() {
        return isMain;
    }

    public void setIsMain(@NonNull Integer isMain) {
        this.isMain = isMain;
    }

    public String getMainGame() {
        return mainGame;
    }

    public void setMainGame(String mainGame) {
        this.mainGame = mainGame;
    }
}

I've tried using multiple combinations of @Ignore, @ColumnInfom removing them but it simply doesn't bind the value:

 @Ignore
@ColumnInfo(name = "main_game")
private String mainGame;

This way is throwing Tried the following constructors but they failed to match: ... param:mainGame -> matched field:unmatched]

If I remove all the tags from the attribute the query works, but doesn't populate the value.

Am I missing something?

Answer 1

If you @Ignore a member variable then Room discounts considering that member variable as a column. If you do not @Ignore the member variable it WILL be a column in the table.

Assuming that you do not want the member variable to be a column in the table BUT at some time you want to retrieve and assign a value then you will need to use a POJO that either has a member variable that is not @Ignored perhaps in your case an extra member variable (due to the complexity of extended classes).

• The alternative is to hard code all the member variables e.g. as per this answer to a similar issue Android Room additional constructor parameter

Due to the apparent complexity of the table i.e. GameEntity extends GenericIndexEntity and possibly other classes are extended, it would be possibly be a nightmare to hard code all the involved columns.

Thus you may well want to use the @Embedded annotation in the POJO to make life easy. However, doing so will include the @Ignored main_game column. So you would need to use another member variable.

So something like:-

public class GameWithMainGamePOJO {
    @Embedded
    GameEntity gameEntity;
    private String mg;

    public void setGameEntity(GameEntity gameEntity) {
        this.gameEntity = gameEntity;
    }

    public String getMg() {
        return mg;
    }

    public void setMg(String mainGame) {
        this.mg = mainGame;
    }

    /* Perhaps what you want */
    public GameEntity getGameEntity() {
        gameEntity.setMainGame(this.mg);
        return gameEntity;
    }
}

In which case you would want to output a column named mg and then perhaps use the getGameEntity method which should return a GameEntity with the main_game set with the extracted value.

Demo

Here's a demo based upon the available code, with other code made up and importantly a query that a) gets the GameEntity columns (including the columns from the extended GenericIndexEntity *(my cobbled together version)).

To demo this a simplified query was used, The @Dao annotated interface used being:-

@Dao
public interface GamesDao {

    @Insert(onConflict = OnConflictStrategy.IGNORE)
    long insert(GameEntity gameEntity);
    @Query("SELECT *, (SELECT 'MG1'||g.gen) as mg FROM games as g")
    List<GameWithMainGamePOJO> getAll();
}

The full, often cobbled together, code used for the demo (less the GamesDao interface as above):-

GenericIndexEntity (cobbled together)

class GenericIndexEntity {
   private String indexNumber;
   private String name;
   private String nameEs;
   private String nameIt;
   private String nameFr;
   private String nameDe;
   private String namePt;
   private String nameRu;

   GenericIndexEntity(
           String indexNumber, String name, String nameEs, String nameIt, String nameFr, String nameDe, String namePt, String nameRu
   ) {
      this.indexNumber = indexNumber;
      this.name = name;
      this.nameEs = nameEs;
      this.nameIt = nameIt;
      this.nameFr = nameFr;
      this.nameDe = nameDe;
      this.namePt = namePt;
      this.nameRu = nameRu;
   }

   public String getIndexNumber() {
      return indexNumber;
   }

   public String getName() {
      return name;
   }

   public String getNameDe() {
      return nameDe;
   }

   public String getNameEs() {
      return nameEs;
   }

   public String getNameFr() {
      return nameFr;
   }

   public String getNameIt() {
      return nameIt;
   }

   public String getNamePt() {
      return namePt;
   }

   public String getNameRu() {
      return nameRu;
   }

   public void setIndexNumber(String indexNumber) {
      this.indexNumber = indexNumber;
   }

   public void setName(String name) {
      this.name = name;
   }

   public void setNameDe(String nameDe) {
      this.nameDe = nameDe;
   }

   public void setNameEs(String nameEs) {
      this.nameEs = nameEs;
   }

   public void setNameFr(String nameFr) {
      this.nameFr = nameFr;
   }

   public void setNameIt(String nameIt) {
      this.nameIt = nameIt;
   }

   public void setNamePt(String namePt) {
      this.namePt = namePt;
   }

   public void setNameRu(String nameRu) {
      this.nameRu = nameRu;
   }
}

GameEntity (adjusted to overcome some unknowns/guesses):-

@Entity(tableName = "games")
public class GameEntity extends GenericIndexEntity {

   @NonNull
   @PrimaryKey
   @ColumnInfo(name = "gen")
   private Integer gen;

   @NonNull
   @ColumnInfo(name = "is_main")
   private Integer isMain;

   @Ignore
   @ColumnInfo(name = "main_game")
   private String mainGame;

   public GameEntity(@NonNull String indexNumber, String name, String nameEs, String nameIt, String nameFr, String nameDe, String namePt, String nameRu){
      super(indexNumber,name,nameEs,nameIt,nameFr,nameDe,namePt,nameRu);
   }

   public GameEntity(@NonNull String indexNumber, String name, String nameEs, String nameIt, String nameFr, String nameDe, String namePt, String nameRu, @NonNull Integer gen, @NonNull Integer isMain, String mainGame) {
      super(indexNumber, name, nameEs, nameIt, nameFr, nameDe, namePt, nameRu);
      this.gen = gen;
      this.isMain = isMain;
      this.mainGame = mainGame;
   }

   @NonNull
   public Integer getGen() {
      return gen;
   }

   public void setGen(@NonNull Integer gen) {
      this.gen = gen;
   }

   @NonNull
   public Integer getIsMain() {
      return isMain;
   }

   public void setIsMain(@NonNull Integer isMain) {
      this.isMain = isMain;
   }

   public String getMainGame() {
      return mainGame;
   }

   public void setMainGame(String mainGame) {
      this.mainGame = mainGame;
   }
}

GameWithMainGamePOJO (as above)

TheDatabase the @Database annotated abstract class:-

@Database(entities = {GameEntity.class},exportSchema = false,version = 1)
abstract class TheDatabase extends RoomDatabase {
    abstract GamesDao getGamesDao();

    private static volatile TheDatabase instance;

    public static TheDatabase getInstance(Context context) {
        if (instance==null) {
            instance= Room.databaseBuilder(context,TheDatabase.class,"the_games_database.db")
                    .allowMainThreadQueries() /* for brevity of the demo */
                    .build();
        }
        return instance;
    }
}

MainActivity to demonstrate the principle:-

public class MainActivity extends AppCompatActivity {

    TheDatabase db;
    GamesDao dao;

    @Override
    protected void onCreate(Bundle savedInstanceState) {
        super.onCreate(savedInstanceState);
        setContentView(R.layout.activity_main);

        db = TheDatabase.getInstance(this);
        dao = db.getGamesDao();

        long g1Id = dao.insert(
                new GameEntity("ix1","G1","G1ES","G1IT","G1FR","G1DE","G1PT","G1RU",1,2,"G1MG"));
        GameEntity ge2 = new GameEntity("ix2","G2","G2ES","G2IT","G2FR","G2DE","G2PT","G2RU");
        ge2.setMainGame("GM2MG");
        ge2.setIsMain(0);
        ge2.setGen(2);
        long g2id = dao.insert(ge2);
        List<GameWithMainGamePOJO> debug = dao.getAll();
        for (GameWithMainGamePOJO gwmgpojo: dao.getAll()) {
            Log.d(
                    "DBINFO","IXN = " + gwmgpojo.gameEntity.getIndexNumber()
                    + "NAME = " + gwmgpojo.gameEntity.getName()
                    + "NAMEDE = " + gwmgpojo.gameEntity.getNameDe()
                    + "NAMEES = " + gwmgpojo.gameEntity.getNameEs()
                    + "NAMEFR   = " + gwmgpojo.gameEntity.getNameFr()
                    + "NAMEIT = " + gwmgpojo.gameEntity.getNameIt()
                    + " ETC .... "
                    + "MGFROMGE = " + (gwmgpojo.getGameEntity()).getMainGame()
                    + "MGFROMPOJO =  " + gwmgpojo.getMg()
            );
        }
    }
}

The RESULT output to the log:-

D/DBINFO: IXN = ix1NAME = G1NAMEDE = G1DENAMEES = G1ESNAMEFR   = G1FRNAMEIT = G1IT ETC .... MGFROMGE = MG11MGFROMPOJO =  MG11
D/DBINFO: IXN = ix2NAME = G2NAMEDE = G2DENAMEES = G2ESNAMEFR   = G2FRNAMEIT = G2IT ETC .... MGFROMGE = MG12MGFROMPOJO =  MG12

As can be seen MG11/MG12 has been dynamically generated and applied and also that the getGameEntity method returned the expected HameEntity with mainGame set correctly.