Skip to content

How to use join on nested association? #14422

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

Open
Grestorn opened this issue Jul 28, 2021 · 0 comments
Open

How to use join on nested association? #14422

Grestorn opened this issue Jul 28, 2021 · 0 comments

Comments

@Grestorn
Copy link

Grestorn commented Jul 28, 2021

I have a complex structure which I'll simplify for this example:

class Site {
...
}

class Series {
  Site primarySite
  Set<Site> additionalSites
  ...
}

class Story {
  Series series
  ...
}

I need to select all stories associated with a Site and make further more detailed selections with that.

First I select all series and that works fine using this:

    def siteSeriesCriteria() {
          return Series.where {
              join("additionalSites", JoinType.LEFT)
              or {
                  eq('primarySite', thisSite)
                  additionalSites {
                      eq('id', thisSite.id)
                  }
              }
          }
   }

   list = siteSeriesCriteria().id().list()

That produces the intended SQL:

select
	this_.id as y0_
from
	series this_
left outer join series_site additional3_ on
	this_.id = additional3_.series_additional_sites_id
left outer join site additional1_ on
	additional3_.site_id = additional1_.id
where
	(this_.primary_site_id =?
		or (additional1_.id =?))

But when I try to use the deeper nested level that doesn't work anymore:

    def siteStoryCriteria() {
            return Story.where {
                series in siteSeriesCriteria().id()
            }
    }
   list = siteStoryCriteria().id().list()

Now the join is no longer a left outer:

select
	this_.id as y0_
from
	story this_
where
	this_.series_id in (
	select
		this_.id as y0_
	from
		series this_
	inner join series_site additional3_ on
		this_.id = additional3_.series_additional_sites_id
	inner join site additional1_ on
		additional3_.site_id = additional1_.id
	where
		(this_.primary_site_id =?
			or (additional1_.id =?)))

I've read that join() only works on the tree root of the Criteria-tree. But how am I supposed to solve this?

I've tried to add
join("series.additionalSites", JoinType.LEFT)
to the nested criteria, but that doesn't work either. I'm stumped here...

jamesfredley referenced this issue in grails/grails-data-hibernate5 Feb 19, 2025
Co-authored-by: renovate[bot] <29139614+renovate[bot]@users.noreply.github.com>
@jdaugherty jdaugherty transferred this issue from grails/grails-data-hibernate5 Mar 13, 2025
@jdaugherty jdaugherty transferred this issue from apache/grails-data-mapping Apr 22, 2025
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

2 participants