Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Add support for implicit join over to-one relationships #1502

Closed
lukaseder opened this Issue Jul 22, 2012 · 1 comment

Comments

Projects
None yet
1 participant
@lukaseder
Copy link
Member

lukaseder commented Jul 22, 2012

It would be nice if jOOQ could detect necesary JOINs automatically using a syntax such as:

// Query 1
create.select(FIRST_NAME, LAST_NAME)
      .from(AUTHORS)
      .where(AUTHORS.BOOKS().TITLE.equal("1984"))

// Query 2
create.select(TITLE)
      .from(BOOKS)
      .where(BOOKS.AUTHORS().LAST_NAME.equal("Orwell"))

This is the SQL that would be generated:

-- Query 1
SELECT first_name, last_name
FROM   authors
WHERE  EXISTS (
  SELECT 1 FROM books
  WHERE books.author_id = author.id
  AND books.title = '1984'
)

-- Query 2
SELECT title
FROM   books
JOIN   authors ON authors.id = books.author_id
WHERE  authors.last_name = 'Orwell'

This issue will cover the "to-one" part of implicit joins, whose implementation is straightforward. Work in progress:

  • Flag to activate the feature (not a configuration flag. On the first call to the path API, we'll activate a static variable): Won't fix
  • Code generation (see #7148)
  • Public API in Table (for use with plain SQL): Won't fix for now
  • Introduce a select scope map where objects can be registered for the scope of a SELECT
  • Documentation
  • Formatting
  • Parser support (once the metadata is known to the configuration: #5296): Moved to external issue #7506
  • Scala generation
  • Implicit join on aliased root table
  • Path alias generation
  • Left join / inner join semantics!: Moved to external issue: #7507
  • Nested queries
  • Usage in DML: Moved to external issue: #7508
  • Self joins
  • Join same table several times
  • Join "diamond" A -> B1 -> C and A -> B2 -> C
  • Join "circuit" A -> B -> C -> A
  • Aliased join trees: Moved to follow-up issue: #7536
  • Cross-schema implicit joins
  • Accessing projected fields from result (including ambiguities!): Moved to follow-up issue: #7536
  • Table name redefinition in nested scope: Moved to follow-up issue: #7536
  • Possible column ambiguities in the join graph: Moved to follow-up issue: #7536
  • Using a join path with the JOIN operator: Moved to follow-up issue: #7536
  • Explicit path aliasing: Moved to follow-up issue: #7536
  • Correct semantics of DSL.asterisk() will be fixed separately: #7156
  • Generate inner join on non-nullable foreign keys: Moved to follow-up issue: #7536

@ghost ghost assigned lukaseder Jul 22, 2012

@lukaseder lukaseder changed the title Add support for HQL-like navigation syntax for JOIN simplification Add support for implicit join over relationships Oct 30, 2015

@lukaseder

This comment has been minimized.

Copy link
Member Author

lukaseder commented Oct 30, 2015

Findings from an initial implementation:

  1. There is a very simple solution that puts markers to the positions of all tables in the RenderContext's StringBuilder, collects all implicit joins while traversing the AST, and patches additional OUTER JOIN expressions into those positions when required, at the end of the rendering phase.
    • Advantage: This solution has no performance impact on the existing API and is rather easy to implement and understand
    • Disadvantage: This solution bypasses the VisitListener SPI. It is not possible, for instance, to implement row-level security on tables that are joined implicitly. That's a show-stopper for this solution
  2. There is a more complex solution that uses an intermediary Context to collect all implicit joins in the scope of the current SELECT statement prior to rendering the FROM clause, where OUTER JOIN expressions are amended to the relevant tables
    • Advantage: This solution is correct as the VisitListener contract is formally applied
    • Disadvantage: This solution traverses the SELECT AST twice during rendering, which is something we want to avoid for performance reasons (see also #4650). The additional overhead is significant. What's worse, the overhead appears also in queries that do not make use of implicit joins, just to check for the presence of such a join.

As it looks right now, we might not implement this feature in jOOQ, unless we find a way to significantly speed up solution 2), or we find another solution 3) that is not yet exposed here.

@lukaseder lukaseder changed the title Add support for implicit join over relationships Add support for implicit join over one to many relationships Feb 7, 2018

@lukaseder lukaseder changed the title Add support for implicit join over one to many relationships Add support for implicit join over to-one relationships Feb 7, 2018

lukaseder added a commit that referenced this issue Feb 7, 2018

[#1502] Add support for implicit join over to-one relationships (WIP)
- [#7152] Add Context.scopeStart() and scopeEnd()
- [#2791] Add a Context data map scoped to the outer scope of a subquery
- [#1502] Add support for implicit join over to-one relationships

lukaseder added a commit that referenced this issue Feb 7, 2018

lukaseder added a commit that referenced this issue Feb 9, 2018

lukaseder added a commit that referenced this issue Feb 9, 2018

lukaseder added a commit that referenced this issue Feb 9, 2018

[#1502] Eagerly initialise scope with all tables
This is required to support implicit join paths referencing roots from outer scopes from within correlated subqueries in the SELECT clause

lukaseder added a commit that referenced this issue Feb 9, 2018

lukaseder added a commit that referenced this issue Feb 12, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.
You signed in with another tab or window. Reload to refresh your session. You signed out in another tab or window. Reload to refresh your session.