In my Android SQLite database, I have a column that is a datetime in the format YYYY-MM-DD HH:MM. I want a query (or a cursor, actually) that will give me all of the rows in, let's say May 2015. The sorting is where I'm getting stuck. I want my results broken down first by week, then for each week a sort based on other columns.
Right now I'm just sorting by the datetime column, followed by the other columns. Eg: ORDER BY t.TASKS_DATETIME ASC, s.STATUSES_RANK ASC, t.TASKS_CATEGORY. But basically every row has a unique value for datetime (even though I don't care about hours and minutes in this case) so no other sorting happens and the end result is not very user friendly. The sort order is really important for the user to look at and understand this data.
I thought about doing a separate select statement for each of the weeks, then doing a UNION ALL to get the month data. But you can't use the ORDER BY clause with a UNION, because it sorts the whole union, not just the individual select statements inside. Not to mention, it's pretty ugly.
I thought maybe I could do it on the Android/Java side and concatenate multiple Cursor objects. But I can't find any way to do that. Does anyone here know a way? I've been away from Java for a few years and was never that intimate with the language to start.
I thought maybe I could use the SQLite date functions [http://ift.tt/11EE2rC ] in some way. Maybe add a new column in my select that has the week of year. Then I could sort by that new column, followed by the other sort criteria that I want. Or maybe there is a way to put a range (or simplified datetime) into the ORDER BY clause. But this is pushing the boundaries of my SQLite skills. Does this seem possible? FYI, on the Android/Java side, I convert this datetime String to a GregorianCalendar.
I'm hoping someone smarter than me on here can point me in a good direction! None of these options seems great and I can't think of any others.
If I can't figure out anything else, I'll go with the UNION option. And add a "week_of_year" column to each select statement inside to identify that week, as suggested by other union/sorting questions here. Then sort by that "week_of_year" column, followed by my other sort criteria. I've started working on this. But I took a break to ask for better ideas here, cause my gut says there has to be a better way. But maybe not :)
As additional background, that I don't think is relevant, but just in case...
This scary query is being used in a StickyHeaderList [http://ift.tt/19Phzt2 ], where the weeks are the headers. If I don't sort first by datetime, the headers/weeks end up out of order. Sometimes they even duplicate.
I will probably at some point write my own alternative or alter this StickyHeaderList code, perhaps something that takes a cursor for each week/header rather than just one for the whole month. I can deal with dates easier in Java then SQL. But I don't have the resources for that today. The biggest headache, I imagine, will be having the multiple-select CAB menu allow me to select across different lists. If you happen to know an easy work around for that, maybe I will just do that and accept the major rework over figuring out this query. :)
Thanks in advance for any help, guidance, or feedback!
Aucun commentaire:
Enregistrer un commentaire