GoogleCloudPlatform / GoogleCloudPlatform/spring-cloud-gcp
Spring Data Cloud Spanner @Interleaved annotation should allow an ORDER BY clause to be specified
- Dominant language
- Java
- Stars
- 551
- Forks
- 349
- Avg merge
- 1d 13h
- Merged PRs (30d)
- 14
Description
**Is your feature request related to a problem? Please describe.**
Our project is using `spring-cloud-gcp-starter-data-spanner:2.0.0`. We are making heavy use of Google Cloud Spanner's interleaved tables to express relationships between entities, but have found that interleaved objects are returned in an arbitrary order when fetched from the database.
As an example, we can define two classes that represent a pair of interleaved database tables:
```
@Table(name = "Parent")
data class Parent (
@PrimaryKey
val parentId: String,
@Interleaved
val children: List
)
@Table(name = "Child")
data class Child (
@PrimaryKey
val childId: String,
)
```
as well as a repository that allows us to fetch a particular instance of `Parent`:
```
@Repository
interface ParentRepository : SpannerRepository
```
then we can fetch an instance of `Parent` along with all of its related `Child` instances:
```
val p: Parent = parentRepository.findById("parentId")
```
The problem is that the order of items in the `Parent.children` list is arbitrary. This can be demonstrated with a simple unit test:
```
class ParentRepositoryTest(private val parentRepository: ParentRepository) {
@Test
fun findByIdTest() {
val children = listOf(
Child(UUID.randomUUID().toString()),
Child(UUID.randomUUID().toString()))
val expected = Parent(UUID.randomUUID().toString(), children)
parentRepository.save(expected)
val actual = parentRepository.findById(expected.parentId).orElseThrow()
assertThat(actual).isEqualTo(expected)
}
}
```
This test will fail intermittently, because the order of interleaved `Child` instances is undefined, which in turn means that `actual` will not always be equal to `expected`.
**Describe the solution you'd like**
One potential solution to this problem is to create an annotation similar to the existing `@Where` annotation that allows me to specify an `ORDER BY` clause to be appended to the `JOIN` that `@Interleaved` implicitly generates.
This might look like:
```
@Table(name = "Parent")
data class Parent (
@PrimaryKey
val parentId: String,
@Interleaved
@OrderBy("childId")
val children: List
)
```
Given that there is no other obvious use for this annotation, it could instead be expressed as an optional argument of the existing `@Interleaved` annotation:
```
@Table(name = "Parent")
data class Parent (
@PrimaryKey
val parentId: String,
@Interleaved(orderBy = "childId")
val children: List
)
```
In either case, the value of the annotation/argument needs to be the name of some attribute of the interleaved object. In my example, I chose `childId`, which happens to be the primary key of the interleaved object, but this need not be a limitation of the implementation.
Bonus points if the chosen solution also allows the user to specify multiple sort columns, and to choose the direction of the ordering:
```
@OrderBy(column = "childId DESC, anotherField ASC")
```
or
```
@Interleaved(orderBy = "childId DESC, anotherField ASC")
```
**Describe alternatives you've considered**
Currently, we're solving this problem with a work-around: we make the interleaved collection a private attribute of the parent class, provide a public accessor that forces a deterministic sort order on the list, and then override the `.equals(...)` and `hashCode()` methods to use that accessor instead of the private field.
Here's an example:
```
@Table(name = "Parent")
data class Parent (
@PrimaryKey
val parentId: String,
@Interleaved
private val children: List
) {
fun children(): List {
return children.sortedBy { it.childId }
}
override fun equals(other: Any?): Boolean {
if (this === other) return true
if (javaClass != other?.javaClass) return false
other as Parent
if (parentId != other.parentId) return false
if (children() != other.children()) return false
return true
}
override fun hashCode(): Int {
var result = parentId.hashCode()
result = 31 * result + children().hashCode()
return result
}
}
```
Obviously, this is less than ideal, as we have to maintain the overridden methods and clearly communicate to all developers on the project _why_ this pattern exists and write unit tests to ensure that they don't accidentally break it in the future.
Contributor guide
Assessment
This issue has not been assessed yet.