Android ROOM SQL Query- How can I return all logged exercises on Current Date?

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

At the moment, my query successfully returns all results in which a log is either present or null on the date which it is passed.

However, if a log has been saved on a previous date then the exercise which has been previously logged is not returned, as the exercise has a log which date is both not null and not equal to the current date.

I could say WHERE log_entries_table.date != :date however logs_today would hold the count of all logs regardless of date and this will not return the count of all log entries with the current date

How could I modify this query to only return the count of logs_today where the log_entries.date = date?

My current query

@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 
LEFT 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 OR log_entries_table.date is NULL) 
GROUP BY exercises_table.exercises_id 
ORDER BY exercise_workout_junction_table.exercise_workout_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

Then I think you have to remove condition WHERE:

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

And transfer part of it:

log_entries_table.date = :date

to your tables' condition connection ON:

ON exercise_workout_junction_table.exercise_workout_id = log_entries_table.junction_id AND log_entries_table.date = :date