Databases
StackOverflow2013
Postsdbo
POHibernate Criteria: Perform JOIN in Subquery/DetachedCriteria
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHibernate Criteria: Perform JOIN in Subquery/DetachedCriteria
    text
    copied!<p>I'm running into an issue with adding JOIN's to a subquery using DetachedCriteria. The code looks roughly like this:</p> <pre><code>Criteria criteria = createCacheableCriteria(ProductLine.class, "productLine"); criteria.add(Expression.eq("productLine.active", "Y")); DetachedCriteria subCriteria = DetachedCriteria.forClass(Model.class, "model"); subCriteria.setProjection(Projections.rowCount()); subCriteria.createAlias("model.modelLanguages", "modelLang"); subCriteria.createAlias("modelLang.language", "lang"); criteria.add(Expression.eq("lang.langCode", "EN")); subCriteria.add(Restrictions.eqProperty("model.productLine.productLineId","productLine.productLineId")); criteria.add(Subqueries.lt(0, subCriteria)); </code></pre> <p>But the logged SQL does not contain the JOIN in the subquery, but does include the alias which is throwing an error</p> <pre><code>SELECT * FROM PRODUCT_LINE this_ WHERE this_.ACTIVE=? AND ? &lt; (SELECT COUNT(*) AS y0_ FROM MODEL this0__ WHERE lang3_.LANG_CODE ='EN' AND this0__.PRODUCT_LINE_ID =this_.ID ) </code></pre> <p><strong>How can I add the joins to the DetachedCriteria?</strong></p> <hr> <pre><code>@Entity @Table(name = "PRODUCT_LINE") public class ProductLine implements java.io.Serializable { private long productLineId; private char active; private Set&lt;Models&gt; models = new HashSet&lt;Models&gt;(0); @OneToMany(fetch = FetchType.LAZY, mappedBy = "productLine") public Set&lt;Models&gt; getModels() { return this.models; } } @Entity @Table(name = "MODEL") public class Model implements java.io.Serializable { private long modelId; private ProductLine productLine; private String name; private Set&lt;ModelLanguages&gt; modelLanguages = new HashSet&lt;ModelLanguages&gt;(0); @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "PRODUCT_LINE_ID") public ProductLine getProductLine() { return this.productLine; } @Column(name = "NAME", nullable = false) public String getName() { return this.name; } @OneToMany(fetch = FetchType.LAZY, mappedBy = "model") public Set&lt;ModelLanguages&gt; getModelLanguages() { return this.modelLanguages; } } @Entity @Table(name = "MODEL_LANGUAGES") public class ModelLanguages implements java.io.Serializable { private long id; private Language language; private Model model; @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "LANGUAGE_ID", nullable = false, insertable = false, updatable = false) public Language getLanguage() { return this.language; } @ManyToOne(fetch = FetchType.LAZY) @JoinColumn(name = "MODEL_ID", nullable = false, insertable = false, updatable = false) public Model getModel() { return this.model; } } @Entity @Table(name = "LANGUAGES", uniqueConstraints = @UniqueConstraint(columnNames = "LANG_CODE")) public class Language implements java.io.Serializable { private long languageId; private String langCode; private Set&lt;ModelLanguages&gt; modelLanguages = new HashSet&lt;ModelLanguages&gt;( 0); @Column(name = "LANG_CODE", unique = true, nullable = false) public String getLangCode() { return this.langCode; } @OneToMany(fetch = FetchType.LAZY, mappedBy = "language") public Set&lt;ModelLanguages&gt; getModelLanguages() { return this.modelLanguages; } } Hibernate version: 3.2.6.ga Hibernate core: 3.3.2.GA Hibernate annotations: 3.4.0.GA Hibernate commons-annotations: 3.3.0.ga Hibernate entitymanager: 3.4.0.GA Hibernate validator: 3.1.0.GA </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload