Querying using unrelated entities or the other side of a unidirectional association

[Ben]

Hi,

I'm wondering if EBean supports these two querying scenarios:

• join unrelated entities: How do you "join unrelated entities" (https://vladmihalcea.com/how-to-join-unrelated-entities-with-jpa-and-hibernate/) with EBean, i.e. join across unmapped associations? Is the EBean approach to use findNative or subQueries for this?
  
  
  
• unidirectional association: How would a JPQL like "select c.parent from Child c where c.parent.id = :parentId" be expressed in EBean, assuming that there is no bidirectional association? Or does EBean recommend all associations to be mapped bidirectionally?

Thanks for your help!

-Ben

[Rob Bygrave]

> join unrelated entities:

findNative or subQueries or @Formula

> unidirectional association:

Check the docs here: https://ebean.io/docs/mapping/jpa/one-to-many#no-OneToMany

Unidirectional has the 2 flavours - no mapping the ManyToOne or no mapping of the OneToMany.  The non-mapping of the OneToMany side is pretty inconsequential.  The non-mapping of the ManyToOne side has the implication that cascade persist MUST be used.

The recommendation is to always map the ManyToOne side and there really isn't any downside to doing that.

> select c.parent from Child c where c.parent.id = :parentId

This has mapped the ManyToOne c.parent so - it isn't really missing / a problem - that would just work.

If we didn't model the parent property on Child (didn't map the ManyToOne) then ... technically the foreign key column is still there even if it is not explicitly mapped so we can actually use the column name rather than the bean property name.

where().eq("t0.parent_id", parentId)  

... where t0.parent_id is the foreign key column on the child table (rather than "parent" - this is relative to Child).  That is, Ebean allows us to "pass through" expressions and that includes database functions/procedures and table/column names.  So when Ebean does not recognise it as a bean property it lets it pass through. t0.parent_id is not recognised as a bean property of Child.

That all said, there really isn't a downside to mapping the ManyToOne side and the recommendation is to always map the ManyToOne. If we do then we can persist Child without loading the Parent (and loading all the children of the parent to then cascade persist - we really want to avoid that if we can).

Did that answer those questions?

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/23eb7fc2-cdbb-4b4d-ba8e-97543bbc085an%40googlegroups.com.

[Ben]

Hi Rob,

Thanks for answering so quickly.

> select c.parent from Child c where c.parent.id = :parentId

About "unidirectional mapping", I mostly meant the "select c.parent" part, i.e. in EBean, how would one express a query where we want to query all parents where some predicate holds for the children?

Does that require having the one-to-many mapping to the children? Or a subquery?

Thanks,

Ben

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/CAC%3Dts-FoM4DU9ZXCh-45F00aGYW52srV81e-vKgCJe%2B7nZUQoA%40mail.gmail.com.

[Rob Bygrave]

> query all parents where some predicate holds for the children?

Assuming Parent had a OneToMany List<Child> children property then ...

List<Parent> parents =

  new QParent()

  .children.status.eq(NEW)

  .findList();

Same as:

DB.find(Parent.class)

.where().eq("children.status", NEW)

.findList()

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/CAA%2B2OnkBJyYxDz0pXr0xN4rm%3DGX4B5VNCNFcBqaJSMh%2BGxai9Q%40mail.gmail.com.

[Ben]

Hi Rob,

Thanks, to confirm: in EBean, this kind of querying (fetch all parents where a predicate holds for the children) requires either a mapped one-to-many to the children (like in your example), or a subquery?

Best,

Ben

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/CAC%3Dts-EWnhSNFB_6p%3D82nrmuUPQxroGjZZDCvJZRDM6_z4FJVg%40mail.gmail.com.

[Rob Bygrave]

> requires either a mapped one-to-many to the children (like in your example), or a subquery?

Yes.

For an Ebean's ORM query like the one I used above that required the OneToMany mapping to be there.  If that OneToMany property isn't there we need a subquery (or native sql).

Cheers, Rob.

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/CAA%2B2Onk2X4oy_0gc0_4hrVgBPgozu3Q5%3D1utAssL%3DtNQd2XiaQ%40mail.gmail.com.

[Jens]

@Rob

Something like

var c = QChild.alias();

List<Parent> parents = new QChild().select(c.parent).where().c.parent.id.eq(1).findSingleAttributeList()

might be an option? 

Otherwise Query Beans might need something similar to asDto() but not as "detached". Something like

var c = QChild.alias();

List<Parent> parents = new QChild().select(c.parent).where().c.parent.id.eq(1).selectAs(Parent.class).findList()

or even

// custom class that holds selected columns

class SelectedColumns {

  Parent parent; // Should be a real entity managed by ebean incl. lazy load and such things.

  String childName;

  String otherName;

}

var c = QChild.alias();

List<SelectedColumns> rows = new QChild().select(c.parent, c.name, c.otherEntity.otherName).where().c.parent.id.eq(1).selectAs(SelectedColumns.class).findList()

When coming from JPA chances are you have queries selecting an Object[] (or a custom class holding selected columns). In an app I just looked up ~200 uses of Object[] because queries select multiple columns that are not related to a single Entity. That is something that Ebean can not do and would need to code rewriting. 

The main use cases why Object[] has been selected are:

- You want to index your query list result into a Map or Table structure. So you usually do "SELECT x.id, relatedEntity" or "SELECT x.id, yEntity, zEntity" or "SELECT x.id, yEntity.id, zEntity". 

- You want to workaround non-working JPA join fetches by selecting things manually in a flat Object[] or custom class and then work on the flat structure.

- In both above cases lazy load etc are still functional, since entities are managed by JPA.

-- J.

[Rob Bygrave]

I don't understand this yet.

> SelectedColumns

It could be this SelectedColumns type should be a @Entity @Sql entity type [ or @View but probably @Sql ]

> var c = QChild.alias();

> List<SelectedColumns> rows = new QChild().select(c.parent, c.name, c.otherEntity.otherName).where().c.parent.id.eq(1).selectAs(SelectedColumns.class).findList()

A query that is close to this is:

var c = QChild.alias();

var o = QOther.alias();

List<Child> rows = new QChild()

  .select(c.parent, c.name)

  .otherEntity.fetch(o.otherName)

  .parent.id.eq(1)

  .findList()

The question might be if that can be converted to a DtoQuery using asDto(SelectedColumns.class)

It seems interesting but I interpret it that the Child.id property is explicitly not desired?

> c.otherEntity.otherName

Also note that this for example could potentially be supported via @Formula? [That is mapped- explicitly if it is a common enough case]

To view this discussion on the web visit https://groups.google.com/d/msgid/ebean/f40d1f44-533f-4452-8b79-b7d45605343cn%40googlegroups.com.