Android Room Subquery unmatched field error

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?

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 https://stackoverflow.com/questions/75860588/android-room-additional-constructor-parameter/75861355#comment133938832_75861355

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.