JDO : querying between classes without relation

Let’s say we have the following classes and are using RDBMS for persistence
public class Country
{
long id;
String name;
}

public class Region
{
long id;
String name;
Long countryId;
}

public class District
{
long id;
String name;
Long regionId;
}

So we ignored all good object-oriented guidance and didn’t use real relations. Now lets suppose I want to query for the Country that has a district called “Cross Gates”. We need to write some JDOQL, but don’t have any real relations … just countryId and regionId to navigate through. So we introduce 2 variables to navigate across the 2 “pseudo relations”.

Query q = pm.newQuery("SELECT FROM mydomain.Country " +
"WHERE this.id == reg.countryId && reg.id == dist.regionId && dist.name == 'Cross Gates' " +
"VARIABLES mydomain.Region reg; mydomain.District dist;");

This creates the following SQL
SELECT DISTINCT 'mydomain.Country' AS NUCLEUS_TYPE,A0.ID,A0."NAME"
FROM COUNTRY A0
CROSS JOIN REGION VAR_REG ON A0.ID = VAR_REG.COUNTRYID
CROSS JOIN DISTRICT VAR_DIST ON VAR_REG.ID = VAR_DIST.REGIONID
WHERE VAR_DIST."NAME" = 'Cross Gates'

In our particular case we know that there are no NULL pseudo relations, so would like “INNER JOIN” for performance. So we make use of a DataNucleus extension, like this

Query q = pm.newQuery("... query as before ...");
q.addExtension("datanucleus.query.jdoql.reg.join", "INNERJOIN");
q.addExtension("datanucleus.query.jdoql.dist.join", "INNERJOIN");

So we set joins for both variables to INNER JOIN, and the SQL becomes

SELECT DISTINCT 'mydomain.Country' AS NUCLEUS_TYPE,A0.ID,A0."NAME"
FROM COUNTRY A0
INNER JOIN REGION VAR_REG ON A0.ID = VAR_REG.COUNTRYID
INNER JOIN DISTRICT VAR_DIST ON VAR_REG.ID = VAR_DIST.REGIONID
WHERE VAR_DIST."NAME" = 'Cross Gates'

Easy really 😉 Would clearly have been easier if we had designed our classes around good O-O practice though. And if using some other type of datastore then your datastore would have to allow joins to even attempt this