Hello. I really hope, that you can help me.
I have entity class, called property and it has OneToMany relation with Image:
Code:
private Set<Image> images;
@OneToMany(fetch = FetchType.EAGER)
@Where(clause="source_type = 1")
@JoinColumn(name="source_id", nullable = true)
public Set<Image> getImages() {
return images;
}
public void setImages(Set<Image> images) {
this.images = images;
}
I use Criteria API for search, but I must get exactly ONE image per property. So select querry has to be like this:
Code:
select p.id, p.something1, p.something2, (select i.file from image where i.sourceId = p.id LIMIT 1), p.something3 FROM property p WHERE ................... LIMIT 20
or
Code:
select DISTINCT ON(p.id) p.id. p.something1, p.something2, i.file
from property p inner join image i where ............... LIMIT 20
Currently, I make LEFT JOIN with Image, but of course, it is not, that I need:
Code:
Criteria imageCrit = propertyCrit.createCriteria("images", "i", CriteriaSpecification.LEFT_JOIN);
offerCrit.setFirstResult(offset);
offerCrit.setMaxResults(limit);
offerCrit.setProjection(projectionList().create().
add(property("bt.parentId")).add(property("o.id")).
add(property("ot.name")).add(property("bt.name")).
add(property("c.currency")).add(property("o.price")).
add(property("o.priceSqm")).add(property("p.nrRooms")).
add(property("p.nrBedrooms")).add(property("p.name")).
add(property("i.file")). //HERE IT IS
add(property("p.id")).
add(property("l.name")).add(property("o.areaWholeBuilt")).
add(property("o.ranking")).add(property("o.stars")).add(property("p.id")));
Is it possible, to make "subselect" with Projections??
I tried to insert this subselect in sqlProjection, but not succeeded with this.
unfortunately, DISTINCT_ROOT_ENTITY can't help me in this situation..
Please help!!
P.S. sorry for my english
P.S. Hibernate version - 3.2.6ga[/code]
Login
FAQ
Search
