Content Providers and Database Views

Question

In our App we have 5 tables, that are updated from a REST Service, using a ContentProvider.

-photos
-statuses
-likes
-comments
-users

We use a ContentProvider for all of our DB related calls, and we want to implement a feed like Activity with a ListView that has an Adapter that extends a CursorAdapter.

Our feed should show either photos or statuses, where as for every photos/status item it has to show:

• Item's creator User Name
• Item's Number of likes
• Item's Number of comments

We were thinking about implementing it using a View

CREATE VIEW event_feed AS 
select * from (
  select "Photo" as type, p.*, u.*, 
    (select count(*) from comments where subjectId = p.id) comment_count, 
    (select count(*) from likes where subjectId = p.id) like_count
  from photos p inner join users u on p.creatorId = u.id
  union
  select "Status" as type, s.*, u.*, 
    (select count(*) from comments where subjectId = s.id) comment_count, 
    (select count(*) from likes where subjectId = s.id) like_count
  from statuses s inner join users u on s.creatorId = u.id
  )
order by createdAt desc

After creating it we will be querying the view using the ContentProvider for a Cursor

Questions

• Performance wise, Is there a better way?
• Could CursorJoiner be any better?

Answer 1

Performance wise should also take into consideration the frquency of times that you are going to
Generate (and then regenarate that view) i.e. does the user can use a 'pull to refresh' in order to
See new items on the 'newsfeed'?
Assuming not an indexed view (by the query you post) and since a view is basically a virtual table
Than generating a view will be expanded like a macro and the same plan should be generated.
Which basically means the created view adds no value unless it's reused.
BUT in terms of performance you can end up with view joining to view joining to view which, when
Expanded, gives a far more complex plan than expected.