Joins spanning multiple schemas. Need advise on how this can also be supported without custom query

[thadguidry]

We would like to support Joins spanning multiple schemas. Currently you can do this with a custom query. However, it would be nice to have a way to express this without a custom query. What might the query syntax look like?

REFERENCES:

• https://stackoverflow.com/questions/57100185/spring-data-specifications-rsql-with-join
• ???

[kdhrubo]

Let's try to narrow down the problem.
This is only applicable in MULTI_TENANT_MODE = false

Now there is a metadata cache that has schema, table, columns, and constraints.

Schema	Table
emp	employee_detail
dept	department_detail

We can make this work by simply ignoring schema to find a list of employees in a certain department.
Even if we do consider schema this should still work - if we apply little bit of intelligence to look up the presence of the table in the
cache, instead complicating the RSQL syntax.

[thadguidry]

One possiblity is that =in= can be used to specify a DB schema.columnName.
And constraints using ( ) can be used to specify the JOIN conditions.

Explicit Join (id=id)

firstName=in=biomed.firstname and degree=in=edu.degree==assoc* and (biomed.id==edu.id)

[thadguidry]

I think it's important to understand the differences of what happens with =in= through RSQL and how this slightly differs with regular subquery operations in databases like PostgreSQL.
https://www.postgresqltutorial.com/postgresql-tutorial/postgresql-in/
https://www.postgresql.org/docs/current/functions-subquery.html

Even I don't quite understand what RSQL is translating down if I perform certain constraints against =in= and potential schema.table

[kdhrubo]

http://localhost:8080/film?select=film_id:id,title,description,release_year:yearOfRelease;language(language_id,name)&filter=release_year==2006&page=2&size=10&sort=title

This will result in the following SQL:

SELECT   film.film_id         AS id,
         film.title           AS title,
         film.description     AS description,
         film.release_year    AS yearofrelease,
         language.language_id AS language_id,
         language.name        AS name
FROM     film
JOIN     language
ON       language.language_id = film.language_id
JOIN     language
ON       language.language_id = film.original_language_id
WHERE    film.release_year = :p1
ORDER BY .title
LIMIT    :p2
offset   :p3

In this case, implicit join is applied. The engine is smart it detects foreign keys and can join film and language.
There is some work to be done here - the aliases have to be fixed, for nullable FK, it should be an OUTER JOIN (globally configurable to do INNER JOIN).

Explicit join can be added in the filter with film.language_id=eq=language.language_id syntax, its not yet implemented.

If this looks ok, please create a couple of separate issues to tackle for Feb 24 release.

[kdhrubo]

RSQL IN matches a list of string or number against a certain column - like ENUMs

[thadguidry]

One simple operator might be to have something like Excel does. VLOOKUP

The field that links the tables (listed in parentheses in the dialog box). This is the lookup_value for your VLOOKUP formula.

Where you can simply supply the field (columns) that links both tables together.
Here I think =in= will be useful to state the schema.tables and provide the common field (column)

## queries both `courses` table and `degree` table in same default schema (not declared)
## for a column `name` value =*web*
## and where both have `id` column

/courses?query=id=in=degree and name=*web*

But I think it's much better to just provide a custom operator that would operate like VLOOKUP kinda does? Specify a field or fields that links both tables? dunno. But =in= certainly could do that and your able to supply a list of fields that are common or even supply explictly within =in= some schema.table.field 's?

[kdhrubo]

ok in RSQL with explicit join

/courses?filter=id=eq=degree.id;name=like=web

Omitting select means all columns from the courses table will be fetched.