android - Room not returning duplicates

Question

So I have a room database all set up, everything is fine, so I can make queries and inserts, delete etc no problems, however i've just run into a situation where id like to return entries by their Ids and duplicates should be allowed, however room is removing the duplicates, so for instance I send it a list of ids say <1,2,3,2,3> and it returns items by their ids but only sends me <1,2,3> removing the duplicate entries. The query I'm making is below (btw complete noob at sql)

@Query("SELECT * FROM card WHERE cardId IN(:cardId)")
LiveData<List<Card>> getCardsByIds(List<Integer> cardId);

Im using it via a repository I created (just a level of abstraction) and calling this repo from a ViewModel, this ViewModel has a mutable live data integer list containing the ids and using a SwitchMap I get the latest live data. ill include the relevant pieces below

CARD REPO calls my Daos method like this

public LiveData<List<Card>> getCardsByIds(List<Integer> cardIds){
    return cardDao.getCardsByIds(cardIds);
}

ViewModel calls for them

private MutableLiveData<List<Integer>> cardIds;
//** constructor etc
cards = Transformations.switchMap(cardIds, id -> cardRepository.getCardsByIds(id));

and through the magic of SwitchMap when the cardIds list updates a new query is made and I observe the ViewModel from my fragment. I've debugged it so I know the list of Ids is correct and has the duplicates Ids, but the returned LiveData list is missing the duplicate Ids. any help?

Answer 1

Edit:

The SQLiteDatabase always presents the results as a Cursor in a table format that resembles that of a SQL database.

Source : Google Developer Training

Before the results are returned by the query, the results are stored in a table, and if there are rows with duplicate primary keys, they would be omitted by default.

To achieve what you intend, you can execute a query to find single element by id in loop and append the result to a list.

Updated DAO method:

@Query("SELECT * FROM card WHERE cardId=:cardId")
LiveData<Card> getCardById(Integer cardId);

Update Repository method:

public LiveData<List<Card>> getCardsByIds(List<Integer> cardIds){
    List list = new ArrayList();
    for(Integer cardId: cardIds){
      list.append(cardDao.getCardById(cardId));
    }
    return list;
}

Hope this helps.

Original Answer:

If id is the primary key of your model, It doesn't allow duplicate data to be entered. Hence while retrieving you might find duplicates missing.

If you have id with duplicate, create another attribute for primary key. (use autogenerate if you don't have any primary key attribute)

A primary key is by default UNIQUE and NOT NULL.