(I really struggle with how to ask this question correctly with few words, so sorry for the title. I tried my best.)

I have a database like this:

(The table is a simplified table from a larger project in Android Studio with java.)

I want to query this datebase so that:

• I get the animals on a certain day
• If an animal is not given for å certain day, I want the row with that animal and a blank date

Examples:

• If date is 08/12 2021, I want rows with id 4 and 5.
• If date is 07/12 2021, I want rows with id 3 and 2 (no cat that day)
• If date is 06/12 2021 (or any other date), I want rows with id 1 and 2 (no cat nor dog that day)

I know I can get all from a certain date pluss those with blank dates, by:

@Query("SELECT * 
        FROM db 
        WHERE (db.date LIKE :date OR db.date LIKE :blank) 
        ORDER BY no ASC")

But, how can I get what I want instead?

CodePudding user response：

According to your sample data, I think that your datamodel has none right setup.

You will get a problem with this data:

→ Therefore you must calculate the time-difference between last valid date and previous valid date and have to make differences, when your last date is less then your request date.

e.g. request at 11/11 2021, where your last valid date would be 09/12 2021

It would be better if you set to every INSERT a system-timestamp. Something like this:

With this setup you have the advantage that you do not have to implicitly assign the ID to possible dates.

With this the result can easily be queried:

SELECT
  *
FROM
  db
WHERE
  db.change_date = '11/12 2021'

UPDATE

If you only want to show a result is available for a certain date:

@Query("SELECT * 
        FROM db 
        WHERE (db.date LIKE :date OR db.date LIKE :blank) AND db.animal = 'cat'
        ORDER BY nr ASC limit 1
        UNION ALL
        SELECT * 
        FROM db 
        WHERE (db.date LIKE :date OR db.date LIKE :blank) AND db.animal = 'dog'
        ORDER BY nr ASC limit 1")

CodePudding user response：

The very quick answer is to just take the first result:

@Query("SELECT * 
        FROM db 
        WHERE (db.date LIKE :date OR db.date LIKE :blank) 
        ORDER BY nr ASC limit 1")