Android Room SQL Query - How can I run a query with a conditional WHERE clause

Question

So I would like to run an SQL query which obtains values from multiple tables. I wish to get the:

• exerciseName and exerciseID from the exercises_table
• junctionID from the exercise_junction_table
• setNumber, minReps, maxReps from the exercise_workout_goals table

I then also have a log_entries_table.

This table stores information about an exercise which the user has logged. From this table I wish to get the COUNT of all logs for a specific date if any are present.

If no logs are present on the date, I would like the return value to be 0.

ER diagram

From my research it seems as though I would need some sort of conditional WHERE clause, but only if a log_entry exists on the date which is passed to it.

If there is no log present, I would simply like the WHERE date = :date section to be ignored.

At the moment, my query is only returning results in which a logEntry is present because of my WHERE date = :date (the date is obtained from the log_Entries table, therefore if a log is not present, there will not be any date at all).

I cannot workout how to make the WHERE date = :date conditional and only run it on the exercises in which at least one logEntry is present.

Any help will be greatly appreciated.

My current query (This only returns values in which a logEntry on the date it is passed is present.)

 @Query("SELECT exercises_table.exercise_Name, exercises_table.exercises_id, 

exercise_workout_goals_table.set_number, 

exercise_workout_goals_table.junction_id, exercise_workout_goals_table.min_reps,  

exercise_workout_goals_table.max_reps, log_id, COUNT(log_id) AS logs_today  FROM 

exercises_table LEFT JOIN exercise_workout_junction_table ON 

exercises_table.exercises_id = exercise_workout_junction_table.exercise_id LEFT 

JOIN exercise_workout_goals_table ON 

exercise_workout_junction_table.exercise_workout_id = 

exercise_workout_goals_table.junction_id JOIN log_entries_table ON 

exercise_workout_junction_table.exercise_workout_id = 

log_entries_table.junction_id WHERE exercise_workout_junction_table.workout_id = 

:button_id AND log_entries_table.date = :date  ORDER BY 

exercise_workout_goals_table.junction_id; ")

  LiveData<List<ExerciseAndGoal>> getAllWorkoutExercises(int button_id, String date);

Exercise and Goal Entity

(This contains all of the fields in which I would like to obtain)

public class ExerciseAndGoal {

    private String exercise_name;
    private int set_number;
    private int min_reps;
    private int max_reps;
    private int exercises_id;
    private int junction_id;

    private int log_id;

    private int logs_today;


    public String getExercise_name() {
        return exercise_name;
    }

    public void setExercise_name(String exercise_name) {
        this.exercise_name = exercise_name;
    }

    public int getSet_number() {
        return set_number;
    }

    public void setSet_number(int set_number) {
        this.set_number = set_number;
    }

    public int getMin_reps() {
        return min_reps;
    }

    public void setMin_reps(int min_reps) {
        this.min_reps = min_reps;
    }

    public int getMax_reps() {
        return max_reps;
    }

    public void setMax_reps(int max_reps) {
        this.max_reps = max_reps;
    }

    public int getExercises_id() {
        return exercises_id;
    }

    public void setExercises_id(int exercises_id) {
        this.exercises_id = exercises_id;
    }

    public int getJunction_id() {
        return junction_id;
    }

    public void setJunction_id(int junction_id) {
        this.junction_id = junction_id;
    }



    public int getLog_id() {
        return log_id;
    }

    public void setLog_id(int log_id) {
        this.log_id = log_id;
    }

    public int getLogs_today() {
        return logs_today;
    }

    public void setLogs_today(int logs_today) {
        this.logs_today = logs_today;
    }
}

Answer 1

You use just "JOIN" in last clause, it's equal to "INNER JOIN".

This "INNER JOIN" throw out your rows from result where that condition is false:

exercise_workout_junction_table.exercise_workout_id = log_entries_table.junction_id

Replace your last "JOIN" with "LEFT JOIN" and with modification I've written in comment:

AND (log_entries_table.date = :date OR log_entries_table.date is NULL)