Compare count result

Question

In my database, I have publications and each publication can have zero or more pages, which are stored in another table. When selecting them, I either want to get all publications or only the ones with at least one page. The following criteria query is not working because of this error: org.hibernate.QueryException: could not resolve property: p

Criteria c = Utils.getSession().createCriteria(Publication.class);

if(!showEmptyPublications)
{
    ProjectionList pl = Projections.projectionList();
    pl.add(Projections.count("pages").as("p"));
    c.setProjection(pl);
    c.add(Restrictions.gt("p", 0));
}

c.addOrder(Order.asc("titel"));
publications = c.list();

Publication:

CREATE TABLE IF NOT EXISTS `Publication` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    ...
    PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=47 ;

Page:

CREATE TABLE IF NOT EXISTS `Page` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `pub_id` int(11) NOT NULL,
    ...
    PRIMARY KEY (`id`),
    KEY `FOREIGN_KEY_DICT` (`pub_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6314 ;

ALTER TABLE `Page`
ADD CONSTRAINT `FOREIGN_KEY_PUBLICATION` FOREIGN KEY (`pub_id`)
REFERENCES `Publication` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Publication.java:

...
@OneToMany(fetch = FetchType.LAZY, mappedBy = "publication")
public Set<Page> getPages()
{
    return this.pages;
}

public void setPages(Set<Page> pages)
{
    this.pages = pages;
}

Could you add table definitions (or hibernate mappings)? "[pages] are stored in another table" suggests it is not a simple one table query — Deltharis, Sep 03 '14 at 14:02

user3487063 · Accepted Answer · 2014-09-03T15:36:49.200

1

the following will give you the publications where pages is not empty (means atleast one page is there for that publication):

Criteria c = session.createCriteria(Publication.class, "publication");
c.createAlias("publication.pages", "page");
c.add(Restrictions.isNotEmpty("page"));
c.setResultTransformer(CriteriaSpecification.DISTINCT_ROOT_ENTITY).list();

edited Sep 03 '14 at 15:36

answered Sep 03 '14 at 15:31

user3487063

3,672
1
17
24

Compare count result

1 Answers1