Content
Updated by Sebastian Fiedlschuster over 1 year ago
Pull Request: [https://github.com/opf/openproject/pull/11678](https://github.com/opf/openproject/pull/11678)
### Querying without timestamps
This is how one would search work packages with a description containing the expression "foo", which also works without this pull request:
```ruby
query = Query.new
query.add_filter 'description', '~', "foo"
query.results.work_packages
```
This searches for work packages that have the term "foo" in their description in their most current states, i.e. **today**.
### Adding timestamps to a Query
For the baseline-comparison feature, we would like to also search historic data, i.e. find those work packages that
* have the term "foo" in their description today **or**
* had the term "foo" in their description at an earlier point in time (baseline date).
```ruby
today = Time.zone.now
baseline_date = 1.year.ago
query = Query.new
query.add_filter 'description', '~', "foo"
query.timestamps = [baseline_date, today]
query.results.work_packages
```
This returns the work packages in their today's state.
In order to get the historic state of a specific work package at the `baseline_date`, call:
```ruby
work_package = query.results.work_packages.first
work_package.at_timestamp(baseline_date)
```
In order to get the historic state of all the work packages in the query result at the `baseline_date`, call:
```ruby
work_packages = query.results.work_packages
work_packages.at_timestamp(baseline_date)
```
### Query only for historic data
If you are not interested in the work pacakges as they are today, but only in their historic state, configure the query like this:
```ruby
historic_date = 1.year.ago
query = Query.new
query.add_filter 'description', '~', "foo"
query.timestamps = [historic_date]
query.results.work_packages
```
This returns the the work packages in their states at the `historic_date`.
### Work to be done
* [x] Implement `Timestamp` model as abstraction for absolute and relative timestamps ➝ https://github.com/fiedl/openproject/pull/2/commits/49d14e6631a43fcb15e9bd823db894e8004eea21
* [x] Add serialized `timestamps` attribute to the `Query` model ➝ https://github.com/fiedl/openproject/pull/2/commits/49d14e6631a43fcb15e9bd823db894e8004eea21
* [x] Query results: Search work packages at all `timestamps` ➝ https://github.com/fiedl/openproject/pull/2/commits/2f51f91f23f78d0130bd952e2e24d5f53f07d89e
* [ ] Make sure that the previous model specs are still green
* [ ] Handle case where the last timestamp is not "now"
* [ ] Use sub queries rather than storing the ids
### See also
* https://github.com/opf/openproject/pull/11243
* **Epic**: https://community.openproject.org/projects/openproject/work\_packages/26448
### Querying without timestamps
This is how one would search work packages with a description containing the expression "foo", which also works without this pull request:
```ruby
query = Query.new
query.add_filter 'description', '~', "foo"
query.results.work_packages
```
This searches for work packages that have the term "foo" in their description in their most current states, i.e. **today**.
### Adding timestamps to a Query
For the baseline-comparison feature, we would like to also search historic data, i.e. find those work packages that
* have the term "foo" in their description today **or**
* had the term "foo" in their description at an earlier point in time (baseline date).
```ruby
today = Time.zone.now
baseline_date = 1.year.ago
query = Query.new
query.add_filter 'description', '~', "foo"
query.timestamps = [baseline_date, today]
query.results.work_packages
```
This returns the work packages in their today's state.
In order to get the historic state of a specific work package at the `baseline_date`, call:
```ruby
work_package = query.results.work_packages.first
work_package.at_timestamp(baseline_date)
```
In order to get the historic state of all the work packages in the query result at the `baseline_date`, call:
```ruby
work_packages = query.results.work_packages
work_packages.at_timestamp(baseline_date)
```
### Query only for historic data
If you are not interested in the work pacakges as they are today, but only in their historic state, configure the query like this:
```ruby
historic_date = 1.year.ago
query = Query.new
query.add_filter 'description', '~', "foo"
query.timestamps = [historic_date]
query.results.work_packages
```
This returns the the work packages in their states at the `historic_date`.
### Work to be done
* [x] Implement `Timestamp` model as abstraction for absolute and relative timestamps ➝ https://github.com/fiedl/openproject/pull/2/commits/49d14e6631a43fcb15e9bd823db894e8004eea21
* [x] Add serialized `timestamps` attribute to the `Query` model ➝ https://github.com/fiedl/openproject/pull/2/commits/49d14e6631a43fcb15e9bd823db894e8004eea21
* [x] Query results: Search work packages at all `timestamps` ➝ https://github.com/fiedl/openproject/pull/2/commits/2f51f91f23f78d0130bd952e2e24d5f53f07d89e
* [ ] Make sure that the previous model specs are still green
* [ ] Handle case where the last timestamp is not "now"
* [ ] Use sub queries rather than storing the ids
### See also
* https://github.com/opf/openproject/pull/11243
* **Epic**: https://community.openproject.org/projects/openproject/work\_packages/26448