Does this SQL statement need a WHERE clause?

Question

I have two tables in my app database, a Gamebooks table and a Collections table. I'm modelling a many-to-many relationship, where a Collection can contain many Gamebooks, by way of a third class called CollectionsAndGamebooks. When the user selects a Collection in the app, they see all the Gamebooks that belong to that Collection (when the user is adding a Gamebook, they specify which Collection to which they want to add it).

I'm using the following statement to try to obtain this functionality, but when I select a Collection, all Gameboooks are displayed, regardless of which Collection they've been added to. Do I need a WHERE clause?

    @Query("SELECT * FROM CollectionWithGamebooks " +
        "INNER JOIN collections_table ON CollectionWithGamebooks.collectionId = collections_table.collectionId " +
        "INNER JOIN gamebooks_table ON CollectionWithGamebooks.gamebookId = gamebooks_table.gamebookId "
         )
    LiveData<List<Gamebook>> getGamebooksInSelectedCollection();

Here's the POJO representing the associative table.

public class CollectionWithGamebooks {

@Embedded public Collection collection;
@Relation(
        entity = Gamebook.class,
        parentColumn = "collectionId",
        entityColumn = "gamebookId",
        associateBy = @Junction(CollectionWithGamebooksCrossRef.class)
)
public LiveData<List<Gamebook>> gamebooks;

}

Answer 1

I don't believe that a WHERE clause will necessarily resolve your issue. However, the @Query is only part of the story, as this is only used to feed the function/method that it precedes. The function/method and particularly the return type has a large play upon the results.

Edit see below, as with the additional information it can be seen that yes a WHERE clause is all you need

I believe that your issue likely stems from your use of select * from CollectionWithGameBooks .... (the associative table). If you want Collections with the respective Gamebooks (children of the collection) then you should use collections_table as the first table, then JOIN the CollectionWithGamebooks table to the collections_table and then JOIN the gamebooks_table to the CollectionWithGamebooks table.

Another important factor is the how the result is obtained, that is the class into which the results are placed. Which you don't show.

Typically you would have a POJO with the Collection embedded ('@Embedded' annotation) and the GameBook with @Relation and as you have an associative table with the associateBy field specifying the CollectionWithGamesbooks table and the appropriate columns via a Junction.

It should be noted that @Relation is a convenience annotation and that the children (GameBooks) are not obtained according to any filtering or ordering, ALL children of each parent are obtained.

Thanks Mike. I've added the POJO representing the associative entity. The query returns a LiveData List of Gamebook objects.

As the newly added result is a list of gamebooks then that's what you get; every gamebook that is related to a collection (it would hardly matter which table were first as you get every relationship).

So if you want all the gamebooks per collection then you want something like (untested)

@Query("SELECT * from collection_table")
LiveData<List<CollectionWithGameBooks>> getCollectionsWithGameBooks();

This will return all collections each with the respective gamebooks.

If you just want the gamebooks for a given collection then something like (which is hardly different from your original query)

@Query("SELECT game_book_table.* FROM game_book_table JOIN CollectionWithGamebooks ON CollectionWithGamebooks.gamebookId = gamebooks_table.gamebookId JOIN collections_table ON CollectionWithGamebooks.collectionId = collections_table.collectionId WHERE collections_table.collectionId =:collectionId")
LiveData<List<Gamebook>> getGamebooksInSelectedCollection(long collectionId);

• note that with the above you don't need to JOIN the collections_table if you are using the collectionId as the argument as the collectionId is a value in the CollectionWithGamebooks table. If you wanted another value to be used to determine the Collection, then you would need to JOIN the CollectionWithGamebooks table.

So actually, yes all you originally need is a WHERE clause that selects the collection. The query above is arguably better as it only retrieves the required data (i.e. only the columns needed to build the Gamebook objects).