title: JPA Criteria Query selectCase on join expression
body: <p>I have a Tuple query that is loading some values and a relationship (join) count. I'm trying to get a third column that is based on a select case value from that join whether or not the join collection contains some value, but I'm really struggling.</p>
  
  <p>The general gist is that Assets can be favorited by users through a UserFavorite object. I am trying to select 3 things:</p>
  
  <p>1) Asset id
  2) count of times it has been favorited
  3**) whether this asset has been favorited by the user requesting the query </p>
  
  <p>In SQL land, I do this:</p>
  
  <pre><code>select distinct a.asset_id, count(u.asset_id) as favorite_count, case when u.user_id in (XXXX) then 1 else 0 end as is_favorite_for_user from asset a left join user_favorite u on (a.asset_id = u.asset_id) group by a.asset_id;
  </code></pre>
  
  <p>The first 2 parts of the Tuple query below are working fine, but I can't get the rest:</p>
  
  <pre><code>Root&lt;Asset&gt; assetRoot = assetQuery.from(Asset.class);
  SetJoin&lt;Asset, UserFavorite&gt; userFavoriteJoin = assetRoot.join(Asset_.userFavorites, JoinType.LEFT);
  assetQuery.select(criteriaBuilder.tuple(
      assetRoot.get(Asset_.assetId),
      criteriaBuilder.count(userFavoriteJoin)
  ));
  assetQuery.distinct(true);
  assetQuery.groupBy(assetRoot.get(Asset_.assetId));
  </code></pre>
  
  <p>I can't figure out how to do the select case/in statement in JPA Criteria. Any thoughts?</p>
  
  <p>Is there a better way to do this than the select/case ?</p>