SQLite from subqueries: How to populate a boolean field on a pojo,… here is a solution to the problem.
SQLite from subqueries: How to populate a boolean field on a pojo,
I would like to return a list of posts, eg.
@Query("SELECT * FROM posts")
List<Post> getPosts()
I have a pojo.
post {
name:String
id :String
postUid:String
userHasNewContent:Boolean
}
Now, I want every post in the list to have userHasNewContent:Boolean
, populated by checking if the user who owns this post has new content (no more than a week).
So I tried.
@Query("SELECT *,
(SELECT content_uid FROM content WHERE content_uid = postUid AND
contentTime < :aWeekAgo)AS userHasNewContent
FROM posts")
List<Post> getPosts(String aWeekAgo)
The WHERE content is:
Content {
contentTime: Long//Unix timestamp
Number: String
and
public static Long aWeekAgo() {
long day = (1000 * 60) * 60 * 24;
return System.currentTimeMillis() - day * 7;
}
This doesn’t seem to work as expected, did I do it?
Edit
Well, when the question is written, it will be clear what I want to do. This is the short version.
//Get all posts
@Query("SELECT * FROM posts")
List<Post> getPosts()
Then loop through them.
@Query("SELECT count(*) FROM content WHERE contentId :contentUID AND soundTime < : aWeekAgo")
int checkIfUserHasNewContent(String uid, long aWeekAgo);
List<Post> postsWithNewContentIndicator = new ArrayList<>();
for (Post post : postsFromDb) {
post.userHasNewContent(checkIfUserHasNewContent(post.getUid()) > 0);
postsWithNewContentIndicator.add(post);
}
So, but I want to do this with a single query instead of using this loop.
Solution
You need to rewrite the query as follows
SELECT
*,
(
(
SELECT
COUNT(*)
FROM
(
SELECT
content_uid
FROM
content
WHERE
content_uid = postUid
AND contentTime > : aWeekAgo
)
)> 0
) AS userHasNewContent
FROM
posts