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<Child>
)
@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:
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<Child>
)
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<Child>
)
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:
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<Child>
) {
fun children(): List<Child> {
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.
@MusikPolice Thanks for a detailed feature request! It makes sense to me. I would lean more towards the @OrderBy annotation, since it's something that is used in JPA.
Contributions are welcomed!
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:
as well as a repository that allows us to fetch a particular instance of
Parent
:then we can fetch an instance of
Parent
along with all of its relatedChild
instances:The problem is that the order of items in the
Parent.children
list is arbitrary. This can be demonstrated with a simple unit test:This test will fail intermittently, because the order of interleaved
Child
instances is undefined, which in turn means thatactual
will not always be equal toexpected
.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 anORDER BY
clause to be appended to theJOIN
that@Interleaved
implicitly generates.This might look like:
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: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:
or
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(...)
andhashCode()
methods to use that accessor instead of the private field.Here's an example:
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.