Duplicated joins in generated query

[Daniel Alejandro Rodriguez Caballero]

Hello everyone, I have the following db structure:

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
abstract class BaseClass(
    @Id
    val uuid: String = UUID.randomUUID().toString(),
    val attr0: String
) : Model()

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
abstract class BaseAssociationClass(
    @Id
    val uuid: String = UUID.randomUUID().toString(),
    val attr0: String
) : Model()

@Entity
class AssociationClassChild0(
    val attr1: String,
    attr0: String
) : BaseAssociationClass(attr0 = attr0)

@Entity
class AssociationClassChild1(
    attr0: String,
    val attr2: String
) : BaseAssociationClass(attr0 = attr0)

@Entity
class BaseClassChild0(
    attr0: String,
    @ManyToOne
    @JoinColumn(name = "association_class_uuid")
    val associationClassChild0: AssociationClassChild0
) : BaseClass(attr0 = attr0)

@Entity
class BaseClassChild1(
    attr0: String,
    @ManyToOne
    @JoinColumn(name = "association_class_uuid")
    val associationClassChild1: AssociationClassChild1
) : BaseClass(attr0 = attr0)

- a first base class with two children classes using single table inheritance

- a second base class with two more children classes single table inheritance

The two children of the first base class has a many to one relation with the other children of the second base class, the foreign key for both relations is the same. Then when I execute this query:

QBaseClass().findList()

the following sql is generated:

select t0.dtype, t0.uuid, t0.attr0, t1.dtype, t0.association_class_uuid, t2.dtype, t0.association_class_uuid from base_class t0 join base_association_class t1 on t1.uuid = t0.association_class_uuid join base_association_class t2 on t2.uuid = t0.association_class_uuid; --bind() --micros(23808)

Note that the query is joining two times with base_association_class using the same conditions.

I think this somehow expected because I'm declaring two different many-to-one relations, but this way I can query by the specific attributes of every child class of BaseAssociationClass like:

QBaseClassChild0().associationClassChild0.uuid.eq("bla") or

QBaseClassChild1().associationClassChild1.uuid.eq("bla")

The problem with this is that as the number of children with the foreign key relation increases the number of duplicated joins also increases degrading the performance of the query.

I tried creating an intermediary class between BaseClass and the children that contain the relation definition, but I still faced the same issue.

Do you guys know if there is some way to tell ebean to not generate those extra joins? Or do you recommend some workaround to avoid this use case?

[Rob Bygrave]

> two different many-to-one relations,

The associationClassChild0 and associationClassChild1 are "the same relationship / foreign key" but ebean is seeing them as 2 different properties.

> I tried creating an intermediary class between BaseClass and the children that contain the relation definition, but I still faced the same issue.

Can you show us what that looks like?  In theory there is the one property maybe called associationClassChild rather than the associationClassChild0 + associationClassChild1.

> @Id val uuid: String = UUID.randomUUID().toString(),

Just to say this is sub-optimal to map UUID to varchar. Some databases natively support UUID type and ebean supports other storage options for UUID.

Background:

TLDR @Inheritance is something we should be a little wary of ... in that there are no major databases that support inheritance on tables that developers would think of as inheritance. So this construct is one where we can end up with something that isn't particularly "natural" from a SQL Database design perspective. So imo people should be wary of over reliance on @Inheritance because from the SQL perspective "it's all flat".  Some use of @ElementCollection also falls into this category of being something you would not necessarily do from a SQL database design perspective.

For myself in terms of how I look at @Inheritance,  I see it more that we are effectively hiding some columns / relationships based on the type (discriminator value / the specific Java type in our Java inheritance hierarchy). If we flattened it all down and an entity mapped to the table then our code sees all the properties [columns].  Putting the Java side inheritance in can somewhat be viewed as about hiding some of those columns / relationships based on type.

I don't know if that helps but it might be worth pondering.

Cheers, Rob.

--

---
You received this message because you are subscribed to the Google Groups "Ebean ORM" group.
To unsubscribe from this group and stop receiving emails from it, send an email to ebean+un...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/574ad7ff-f340-4ef5-ad48-935d8fe30b47n%40googlegroups.com.

[Daniel Alejandro Rodriguez Caballero]

Hey Rob, thanks a lot for your feedback.

> Can you show us what that looks like?  In theory there is the one property maybe called associationClassChild rather than the associationClassChild0 + associationClassChild1.

I just tried something like this:

@Entity
@Inheritance(strategy = InheritanceType.SINGLE_TABLE)
abstract class BaseClassWithAssociation(
    attr0: String,

    @ManyToOne
    @JoinColumn(name = "association_class_uuid")

    val baseAssociation: BaseAssociationClass

) : BaseClass(attr0)

@Entity
class BaseClassChild0(
    attr0: String,
    @ManyToOne
    @JoinColumn(name = "association_class_uuid")
    val associationClassChild0: AssociationClassChild0

) : BaseClassWithAssociation(attr0 = attr0)

@Entity
class BaseClassChild1(
    attr0: String,
    @ManyToOne
    @JoinColumn(name = "association_class_uuid")
    val associationClassChild1: AssociationClassChild1

) : BaseClassWithAssociation(attr0 = attr0)

I was naively hoping for ebean to detect that the column name was declared in the parent and then don't repeat the join. But as you mentioned ebean interpreted it as a different property and added one more join.

I think I could flatten the BaseAssociationClass inheritance design to only one class and then use it in a design like the example above, but then I'll lose the inheritance cool features: automatically adding the discriminator filter and hiding columns. Besides, maybe it could be risky to apply that in my production/legacy codebase where we're already relying on the inheritance features in several places, but it should be possible though.

Do you think it could be valuable to create an annotation  to tell a property to reuse a parent relation definition without generating new joins? or maybe it could be inferred automatically? I think I could help with that.

Best regards, Daniel.