Using values that are not constant in our Provider

[ferrannp]

I wonder how to do this using schematic. I've been avoiding complicated queries and I tried to avoid this as much as I could but I have some use cases where I need it.

I do need some queries that have sub-queries that need non constant values (like executing a function that will return the current date).

If I try to do that with schematic, I'll get:

Attribute value must be constant.

This is easy to fix if that value should be in the outer query, so we can create an @InexactContentUriand pass anything we want from our code, example:

MyProvider.MyTable.withTime(util.getCurrentTime());

That would work fine, but if I do need util.getCurrentTime()inside a subquery, I can't do it. I am assuming this is a limitation of the Annotation model. So... Is there a way to do this using schematic? Is it possible to write the call in a fully raw mode way and generate the provider too?

[SimonVT]

For select statements there's the @Where annotation.

@Where(path = "mypath")
public static String[] dynamicWhere() {
  return new String[] {
      MyColumns.COLUMN + ">" + util.getCurrentTime(),
  };
}

This is called every time the "mypath" uri is queried.

Does that solve your problem?

[ferrannp]

Hello @SimonVT and thanks for your promptly response. Isn't the @Whereyou suggested the same as using the wherefrom @InexactContentUri? (Since it accepts an array).

Look, here are some queries I can't replicate with schematic right now:

SELECT shows.id, episodes.season, episodes.number, min(episodes.sortKey) as minKey, innerJoin.airedCount
FROM shows, episodes
  LEFT JOIN (SELECT episodes.showId as showId, episodes.season as season, COUNT(*) as airedCount 
  FROM episodes 
  WHERE episodes.first_aired IS NOT NULL AND episodes.first_aired <= util.getCurrentTime()
  GROUP BY episodes.showId, episodes.season)
  AS innerJoin ON episodes.showId = innerJoin.showId AND episodes.season = innerJoin.season
WHERE episodes.showId = shows.id AND episodes.first_aired IS NOT NULL AND watched = 0
AND episodes.first_aired <= util.getCurrentTime()
GROUP BY shows.id

As you can see, I use the util.getCurrentTime() twice (for the outer where and inside the select of the LEFT JOIN). For the outer, no problem, @InexactContentUri and passing it through the code that queries the ContentResolver. The problem is inside the LEFT JOIN.

Another example:

SELECT episodes.showId, episodes.season, COUNT(*) as allEpisodes, watchedEpisodes, airedEpisodes
FROM episodes
LEFT JOIN
(SELECT episodes.showId as notWatchedShowId, episodes.season as episodeSeasonNotWatched, COUNT(*) as watchedEpisodes FROM episodes WHERE episodes.watched = passedShowId AND episodes.showId=1 GROUP BY episodes.season)
as notWatched ON episodes.season = episodeSeasonNotWatched
LEFT JOIN
(SELECT episodes.showId as notAiredShowId, episodes.season as episodeSeasonNotAired, COUNT(*) as airedEpisodes FROM episodes WHERE episodes.showId =passedShowId AND episodes.first_aired IS NOT NULL GROUP BY episodes.season)
as notAired ON episodes.season = episodeSeasonNotAired
WHERE episodes.showId = passedShowId
GROUP BY episodes.season

Here I need a passedShowId in the outer where but also inside the subqueries from the LEFT JOIN.

In both cases, I can't find a way of doing that using @InexactContentUri. Hope that clarifies my problem.

Not sure about a solution but... What about a way of passing parameters to inners queries? Would that be possible?

[SimonVT]

No, there where parameter for @ContentUri and @InexactContentUri are for static where statements. The method annotated with @Where is called every time the Uri is queried.

For joins there is currently no solution. A @Join annotation would need to be added and called for every query.

[ferrannp]

I see... Those SELECT into the join(s) are petty common... Do you think/plan to add that @Join annotation? I think it'd be a key feature.

[SimonVT]

Added a @Join annotation in version 0.6.6. Annotate a method and return a String[] of join statements.

[SimonVT]

Version 0.6.7 now, I messed up regular joins in 0.6.6.