The other day I wrote about how to do a IN and GROUP BY query using Java's de facto ORM, Hibernate. I thought it would be interesting to see how other ORMs handled the same query. This is the query I want to generate:
SELECT COUNT(*),state FROM download_request WHERE id IN (<id list>) GROUP BY state;
Below is the code, output and SQL generated for the three ORMs.
Hibernate
class HibernateDAO implements ApplicationDAO {
public Map getStateCounts(final Collection ids) {
HibernateSession hibernateSession = new HibernateSession();
Session session = hibernateSession.getSession();
Criteria criteria = session.createCriteria(DownloadRequestEntity.class)
.add(Restrictions.in("id", ids));
ProjectionList projectionList = Projections.projectionList();
projectionList.add(Projections.groupProperty("state"));
projectionList.add(Projections.rowCount());
criteria.setProjection(projectionList);
List results = criteria.list();
Map stateMap = new HashMap();
for(Object[] obj: results) {
DownloadState downloadState = (DownloadState)obj[0];
stateMap.put(downloadState.getDescription().toLowerCase(), (Integer)obj[1]);
}
hibernateSession.closeSession();
return stateMap;
}
public static void main(String args[]) {
HibernateDAO downloadRequestDAO = new HibernateDAO();
Collection ids = new ArrayList();
for (int i = 1000; i < 1010; i++ )
ids.add(i);
Map stateCounts = downloadRequestDAO.getStateCounts(ids);
for (String state: stateCounts.keySet()) {
System.out.println(state + ": " + stateCounts.get(state));
}
}
}
Output
failed: 5
downloaded: 1
completed: 4
SQL
select this_.state as y0_, count(*) as y1_ from download_request this_
where this_.id in (1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009)
group by this_.state
Django
counts = models.DownloadRequest.objects.filter(
id__in=range(1000, 1010),
).values('state').annotate(Count('state'))
for count in counts:
print count
Output
{'state': u'FAILED', 'state__count': 5}
{'state': u'COMPLETED', 'state__count': 4}
{'state': u'DOWNLOADED', 'state__count': 1}
SQL
SELECT `download_request`.`state`, COUNT(`download_request`.`state`)
AS `state__count` FROM `download_request`
WHERE `download_request`.`id` IN (1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009)
GROUP BY `download_request`.`state` ORDER BY NULL
SQLAlchmey
query = session.query(
func.count(DownloadRequest.state), DownloadRequest.state,
).filter(
DownloadRequest.id.in_(range(1000,1010)),
).group_by(DownloadRequest.state)
for count in query.all():
print count
Output
(4L, 'COMPLETED')
(1L, 'DOWNLOADED')
(5L, 'FAILED')
SQL
SELECT count(download_request.state) AS count_1, download_request.state
AS download_request_state FROM download_request
WHERE download_request.id IN (1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009)
GROUP BY download_request.state
As you can see SQLAlchemy is the most similar to SQL, django's is the briefest and Hibernate (obviously) is the most Java-like. Of the three I'd say I like SQLAlchemy the best as it is the most similar to SQL and me being from an SQL background it is the most natural. However all three get the job done and it is always great to have options.
Be the first to comment.