Java – SQLite from subqueries: How to populate a boolean field on a pojo,

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

Related Problems and Solutions