...
[14:53:38 CST(-0600)] <EricDalquist> then translate it to the criteria builder 
[14:53:53 CST(-0600)] <EricDalquist> oh and the JS that displays the chart and uses that view is just in the source of the page: https://my-reports.doit.wisc.edu/StatsReporter/reports/loginReport.html
[14:54:27 CST(-0600)] <athena> awesome, thanks 
[15:38:40 CST(-0600)] <athena> ok.
[15:38:55 CST(-0600)] <athena> i'm thinking we need to get loginaggregationimpl to extend comparable?
[15:39:11 CST(-0600)] <EricDalquist> sounds reasonable
[15:39:15 CST(-0600)] <athena> er
[15:39:16 CST(-0600)] <EricDalquist> to sort them by date/time?
[15:39:17 CST(-0600)] <athena> datedimension
[15:39:19 CST(-0600)] <athena> but probably that too
[15:39:29 CST(-0600)] <EricDalquist> yeah
[15:39:41 CST(-0600)] <athena> right now i can't actually do before/after queries
[15:39:50 CST(-0600)] <athena> assuming that's because the field isn't comparable
[15:39:54 CST(-0600)] <EricDalquist> really?
[15:39:59 CST(-0600)] <EricDalquist> can you paste an example
[15:41:06 CST(-0600)] <athena> cb.greaterThanOrEqualTo(root.get(LoginAggregationImpl_.dateDimension), this.dateDimensionParameter),
[15:41:06 CST(-0600)] <athena> cb.lessThan(root.get(LoginAggregationImpl_.dateDimension), this.date2DimensionParameter),
[15:41:06 CST(-0600)] <athena> cb.equal(root.get(LoginAggregationImpl_.interval), this.intervalParameter)
[15:41:27 CST(-0600)] <athena> this looks like it expects the expression to extend comparable: http://docs.oracle.com/javaee/6/api/javax/persistence/criteria/CriteriaBuilder.html#lessThanOrEqualTo(javax.persistence.criteria.Expression, javax.persistence.criteria.Expression)
[15:41:27 CST(-0600)] <EricDalquist> ah .... right
[15:41:35 CST(-0600)] <EricDalquist> so that isn't the issue
[15:41:44 CST(-0600)] <EricDalquist> it is that the query requires a join
[15:41:54 CST(-0600)] <EricDalquist> the table for LoginAggregationImpl has no date or time info in it
[15:42:00 CST(-0600)] <EricDalquist> that is all stored in the dimension tables
[15:42:15 CST(-0600)] <EricDalquist> so you need to actually query for the list of date dimensions in the range
[15:42:30 CST(-0600)] <EricDalquist> and then join in the corresponding login aggregations
[15:43:23 CST(-0600)] <athena> ugh.
[15:43:31 CST(-0600)] <athena> yeah, i'm just not familiar with this API whatsoever
[15:43:37 CST(-0600)] <athena> this api meaning the criteria builder
[15:43:54 CST(-0600)] <EricDalquist> are you comfortable with how you would write that SQL?
[15:44:08 CST(-0600)] <athena> vaguely
[15:44:15 CST(-0600)] <athena> i don't know our new API either, apparently
[15:44:43 CST(-0600)] <athena> seems like i hardly ever use SQL anymore
[15:44:50 CST(-0600)] <athena> though certainly this is a good reason to catch up
[15:46:44 CST(-0600)] <EricDalquist> https://gist.github.com/1665699
[15:46:49 CST(-0600)] <EricDalquist> that is about what the SQL would look like
[15:46:58 CST(-0600)] <EricDalquist> the criteria builder adds some verbosity
[15:47:06 CST(-0600)] <EricDalquist> but the result reads pretty close to the SQL
[15:47:39 CST(-0600)] <EricDalquist> the big difference is that where clause would look more like "where dd.date >= startDate and dd.date <= endDate"
[15:47:52 CST(-0600)] <EricDalquist> but that was harder to write in an actually functional sql example
[15:48:41 CST(-0600)] <athena> ok, i think that makes sense
[15:49:10 CST(-0600)] <EricDalquist> so the first thing you're interested is actually the date range
[15:49:13 CST(-0600)] <athena> so we're going to wind up with some pretty complex queries to get the right date range back
[15:49:30 CST(-0600)] <EricDalquist> yes
[15:49:36 CST(-0600)] <athena> ick.
[15:49:42 CST(-0600)] <EricDalquist> an unfortunate side effect of the dimensionality
[15:49:49 CST(-0600)] <EricDalquist> which is needed to reduce the number of rows
[15:49:49 CST(-0600)] <athena> and even worse once we start considering datetimes
[15:50:08 CST(-0600)] <EricDalquist> if we embed all the date/time info in each aggregate the number of rows starts to balloon
[15:50:21 CST(-0600)] <athena> yeah
[15:50:25 CST(-0600)] <athena> makes sense
[15:50:34 CST(-0600)] <EricDalquist> the queries do get complex
[15:51:29 CST(-0600)] <athena> yeah.
[15:51:30 CST(-0600)] <athena> hm.
[15:51:52 CST(-0600)] <athena> so honestly, before i can get anything done on this i'm probably going to need to go read a bunch about the criteria stuff
[15:52:22 CST(-0600)] <EricDalquist> would some examples help?
[15:52:59 CST(-0600)] <athena> if they were sufficiently close enough, maybe
[15:53:19 CST(-0600)] <athena> i mean i get the overall picture, but figuring out exactly how all this changes the root, etc. . . .
[15:53:32 CST(-0600)] <athena> ultimately i need to actually udnerstand what's going on, probably
[15:57:50 CST(-0600)] <EricDalquist> hrm ... there may actually be flaws in the current data model that prevents the query you want from working
[15:57:53 CST(-0600)] <EricDalquist> I'll take a pass at it
[16:05:24 CST(-0600)] <EricDalquist> yup, there isn't any way to do the query you want with the current model
[16:05:31 CST(-0600)] <EricDalquist> looks like it should be an easy fix ...
[16:12:42 CST(-0600)] <athena> that's good at least
[16:16:48 CST(-0600)] <EricDalquist> https://gist.github.com/1665818
[16:16:53 CST(-0600)] <EricDalquist> that is what it would look like I think
[16:17:07 CST(-0600)] <EricDalquist> the part that is missing is DateDimensionImpl_.loginAggregations
[16:17:10 CST(-0600)] <EricDalquist> which I've added locally
[16:17:13 CST(-0600)] <EricDalquist> I'll try testing this
[16:17:18 CST(-0600)] <EricDalquist> and if it works I'll get it committed
[16:17:35 CST(-0600)] <athena> awesome, thanks
[16:26:39 CST(-0600)] <EricDalquist> yup that works
[16:26:43 CST(-0600)] <EricDalquist> a little annoying
[16:26:59 CST(-0600)] <EricDalquist> as our date and time dimensions will now have a collection of aggregation as a field
[16:27:04 CST(-0600)] <athena> yay!
[16:27:10 CST(-0600)] <EricDalquist> but without that hibernate won't let us join 
[16:27:19 CST(-0600)] <athena> yeah
[16:27:28 CST(-0600)] <athena> now i feel less bad for having a headache
[16:27:52 CST(-0600)] <EricDalquist>
[16:47:11 CST(-0600)] <EricDalquist> ok athena
[16:47:19 CST(-0600)] <EricDalquist> I just pushed the example to master
[16:47:29 CST(-0600)] <EricDalquist> I also pushed the hibernate & spring updates
[16:47:39 CST(-0600)] <EricDalquist> since those made the date dimension stuff a little easier
[16:48:09 CST(-0600)] <EricDalquist> so you'll need to run "ant db-hibernate-aggr-events db-hibernate-raw-events"
[16:50:30 CST(-0600)] <athena> thanks!
[16:50:44 CST(-0600)] <EricDalquist> I updated the example to use an in clause too
[16:50:56 CST(-0600)] <EricDalquist> so the api is (start, end, interval, groups...)
[16:51:16 CST(-0600)] <EricDalquist> really the best way to figure these out is to write the SQL first
[16:51:21 CST(-0600)] <EricDalquist> then translate it into the critera api
[16:51:32 CST(-0600)] <EricDalquist> at least that is the way that is easiest for me
[16:51:43 CST(-0600)] <EricDalquist> since I get immediate feedback from the sql if it is correct or not
[16:53:00 CST(-0600)] <EricDalquist> ok, time to head home
[16:53:12 CST(-0600)] <athena> thanks _
[16:53:17 CST(-0600)] <EricDalquist> good luck with the reporting stuff JimH is very exited that other people might be writing reports
[16:53:23 CST(-0600)] <athena> yay!