Simplify the select id of the *ToOne properties

[nPraml]

Hello @rbygrave ,

We have a feature in our application that exports all data to JSON files so that it can be imported into other applications (such a backup & restore feature). For this, we always need the linked object relations (e.g., *ToOnes). We use PathProperties for this, which precisely define what needs to be loaded for each entity during export.

We always create a dependency graph that shows the order in which the objects need to be exported and imported for it to work. For example: Customer is exported before Order, and for Order, it's sufficient if we only export the Customer ID; this way, the relations remain intact.

Some of our objects are very large, and MariaDB throws an exception stating that only 61 tables can be used in the query:

Query threw SQLException:(conn=14) Too many tables; MariaDB can only use 61 tables in a join

For this example, we wrote the following test:

 @Test
  void test_withToOne() {
    ResetBasicData.reset();

    PathProperties root = PathProperties.parse("*,customer(id)");
    LoggedSql.start();
    Query<Order> query = DB.find(Order.class).apply(root);
    query.findList();
    List<String> sql = LoggedSql.stop();
    assertThat(sql).hasSize(1);
    // test gives this sql:
    // select t0.id, t0.status, t0.order_date, t0.ship_date, t1.name, t0.cretime, t0.updtime, t1.id from o_order t0 join o_customer t1 on t1.id = t0.kcustomer_id
    // we would assume this:
    assertThat(sql.get(0)).contains("select t0.id, t0.status, t0.order_date, t0.ship_date, t1.name, t0.cretime, t0.updtime, t0.kcustomer_id from o_order t0");
  }

We discovered that some IDs trigger an unnecessary join (from o_order t0 join o_customer t1 on t1.id = t0.kcustomer_id), and then only the ID is finally selected from t1. In our opinion, the customer_id could simply come from the "main" table, and the join would be unnecessary (select ..., t0.kcustomer_id. We analyzed how we could reduce our query from the current 109 tables to 40-50 tables.

Can you please give us feedback on whether this simplification of the joins would make sense in your opinion? What kind of SQL would be expected in the test case?

@rPraml , @jonasPoehler FYI

[rbygrave]

Can you repeat the example without using PathProperties and instead just use a query?

In an ORM query a customer.id would get translated back to the foreign key column (and not require the join) so that is the expectation of an ORM query. So then that might lead to the question of how PathProperties is working.

I suspect this a "projection specific" issue, probably relating to select("..., customer") vs fetch("customer", "id). That is, the fetch("customer", "id) and select("..., customer") are ideally the same "projection" (and don't require the join and can be satisfied by the foreign key column)

[nPraml]

@rbygrave Thanks for the quick response. I've opened a PR with the test cases: https://github.com/ebean-orm/ebean/pull/3644

Unfortunately, fetch("customer", "id) and select("..., customer") do not work the same