Not unique table/alias: 'language'

[kdhrubo]

Query generated for

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

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

Causes - Not unique table/alias: 'language' error

[kdhrubo]

On assignment of unique alias to language table - the mybatis query engine throws error

org.mybatis.dynamic.sql.exception.DuplicateTableAliasException: Table "language" with requested alias "jtc2" is already aliased in this query with alias "jtb1". Attempting to re-alias a table in the same query is not supported

Logged issue - https://github.com/mybatis/mybatis-dynamic-sql/issues/733

[kdhrubo]

One solution is to refer the projected column and based on that only use the first join. For multi join - with same referenced table for now rely on custom query until the mybatis-dynamic-sql team relaxes the this condition or suggests some alternative solution.

[thadguidry]

Why not also use an AND ?

SELECT *
FROM table1
JOIN table2
ON table1.column1 = table2.column1
AND table1.column2 = table2.column2

[kdhrubo]

How to turn the above query with AND? @thadguidry

[thadguidry]

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

[kdhrubo]

What if original_language_id is nullable col?

[thadguidry]

do the "not null" check in the WHERE clause, since that's a filter condition if you want only rows

 WHERE ao.original_language_id IS NOT NULL

or IS NULL if that's what your wanting to return. :-)

[kdhrubo]

Wouldn't the AND remove those rows - it should be outer join ?

[thadguidry]

are you making it a 2 matching columns AND or either matching columns OR ?

[kdhrubo]

How to build this logic? a bit confused. Shed some light

[thadguidry]

;language(language_id,name)

So the ; means AND in RSQL, right, so maybe that could be an additional WHERE condition or it might be an additional JOIN condition, whatever. and the , comma means ?? dunno actually, is that language_id AND name or is it implied as language_id OR nname? WTF does the , comma mean there? dunno.

Also, where do I even see original_language_id in that RSQL AND expression in the original query? I don't see it even being expressed? So you implied it somehow and for what real reason? Seems counterintuitive to the actual user's API query they asked for?

[kdhrubo]

;language(language_id,name)

In select this should only refer to projection. So this means getting the data from language_id, name columns of the language table.

Now where do you see original_language_id? - DB2Rest is trying to apply implicit join based on the path or table.column combination. The goal is to make it super simple for developers. So film table has 2 columns - language_id, original_language_id with FKs to language table.

[thadguidry]

So the FK's are hidden to a user. Right? In other words, relationships are hidden to a user. Right? Sorry, but we have to work through my questions because I'm just coming to grips with all this now and broadening my understanding of DB2Rest. Which I now know is to make it simple for developers, and also avoid them learning SQL constructs at all.

[kdhrubo]

[kdhrubo]

Know minimal but get maximum results - making it as much intuitive as possible for nocode developers.

Goal - is both sides can work with DB2Rest - those who are not familiar with SQL using the minimal RSQL DSL of DB2Rest or those who know SQL make use of it with custom SQL features.

[thadguidry]

So the user asked: "also extend the data with the language name field" ? If so, it might make sense that you look at this as value replacement. where a film HAS one or many language names? If so, then seems you wouldn't even want them to worry about expressing the JOIN through RSQL syntax, but imply through relations. I.E. imagine if in the film ERD, where it says original_language_id that would instead say language.name. So I could imagine that a user would just do this:

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

So given the above paragraph...maybe could even imply that extra field language.name asked for inside the ?select fields themselves and not even use the RSQL AND or ; in place there?

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

and then special filtering conditions can go into &filter such as &filter=language.name!=null ?

[kdhrubo]

makes a lot of sense. a little bit of extra typing to get to join table columns.

Now back to the basic question of AND how will that join work? INNER JOIN or OUTER JOIN?

[kdhrubo]

I quite like this syntax

select=film_id:id,title,description,release_year:yearOfRelease;language.name

Makes parsing easier.

[thadguidry]

The AND in my SQL example is an INNER JOIN. (which is the implied default join type for I think every RDBMS in 2024, but not quite default among all prior to 2002). So when the parser sees JOIN and the engine does an INNER JOIN.

The idea there is just from Relational Algebra and Set Theory. Having automapped attributes (predicates in my semantic world also) through inspection of FK relations.

[kdhrubo]

how to do OUTER JOIN? and when to do that? in this case where one fk col is nullable

[thadguidry]

Venn Diagrams (set theory) is your friend. And so are pets like Cats and Dogs. The interset is an INNER JOIN. Let's say Cat,Dog.

Cat, {hasBlueEyes: true}, {name: Kitty}
Cat, {hasBlueEyes:false}, {name:Fluffy}

Dog, {hasBlueEyes: false}, {name:Spot}
Dog, {hasBlueEyes: true}, {name:Fido}

Find (return) the interset of Dogs and Cats with blue eyes Find (return) the non-interset of Dogs and Cats with blue eyes Find (return) Dogs with blue eyes AND Cats without blue eyes Find (return) Dogs without blue eyes AND Cats with blue eyes Find (return) Dogs without blue eyes AND Cats without blue eyes

https://www.sqlshack.com/sql-outer-join-overview-and-examples/

[kdhrubo]

Understood if its nullable use - OR to get LEFT join?

[thadguidry]

what is OR ? oh you mean OR ?

[kdhrubo]

    SELECT a0.film_id           AS id,
       a0.title             AS title,
       a0.description       AS description,
       a0.release_year      AS yearOfRelease,
       language.language_id AS language_id,
       language.name        AS name
FROM   film a0
       JOIN language
         ON ((language.language_id = a0.language_id)
         OR (language.language_id = a0.original_language_id)) -- OR if nullable join column

[thadguidry]

The rule to do a LEFT join, might be tricky, but usually it's the exact meaning of "EXTEND DATA FROM THIS TABLE, USING THIS OTHER TABLE". Whereas INNER JOIN is not extension of data, but ONLY MATCHING ROWS FROM BOTH.

[kdhrubo]

Right.

So in this typical situation, how can DB2Rest take that smart decision on what basis? what rules?

[thadguidry]

I don't think RSQL has Relational Algebra expressions unfortunately. Only constraints and predicate operators. If RSQL had a syntax for "extend my data with" then it would always be a LEFT JOIN. When RSQL has filter constraints on any table with a FK relation, then... thinking...

then we still don't know if the user wants to "extend" or simply "filter". hmm. I guess, if they are asking for fields from another table, and that table can be left joined.

UPDATE: actually we do know when they want to extend and filter. The implied joining when fields with FK are expressed in the &select and &filter.

[kdhrubo]

1. if join col is not null then AND
2. if join col is null then OR

this should be a default rule. else set a parameter to force AND

[thadguidry]

you can certainly make defaults. but there will be users who need to override them.

[kdhrubo]

For now lets lock this to move forward.

[thadguidry]

the syntax is good enough. We'll need to test with simple data and attribute pairs across 3 tables.

[thadguidry]

Something like this: film <category> actor

Film names that are in category Horror with actors first_name="Ed" and &filter=language.name=="English"

Ideally, we would want to have returned 1 Row in the test. But data mapped in tables, we should have 3 Horror films, and 2 actors named Ed but different last names appearing in 1 of the Horror films together.