CriteriaBuilder 2nd root not working

Hibernate ORM
1 
            CriteriaBuilder cb = s.getCriteriaBuilder();

            // Get all videos from subscribed channels, with channel info
            CriteriaQuery<Video> criteria = cb.createQuery(Video.class);
            criteria.distinct(true);
            var root = criteria.from(Video.class);
            var userRoot = criteria.from(User.class);
            root.fetch("channel", JoinType.LEFT);

            criteria.select(root)
                    .where(root.get("channel").get("uploader_id").in(userRoot.get("subscribed_ids"))) // This isn't working
                    .where(cb.equal(userRoot.get("id"), user.getId()))
                    .orderBy(cb.desc(root.get("uploaded")));

            List<Video> videos = s.createQuery(criteria).list();

This generates:

Hibernate: 
    select
        distinct video0_.id as id1_4_0_,
        channel2_.uploader_id as uploader1_0_1_,
        video0_.uploader_id as uploader7_4_0_,
        video0_.duration as duration2_4_0_,
        video0_.thumbnail as thumbnai3_4_0_,
        video0_.title as title4_4_0_,
        video0_.uploaded as uploaded5_4_0_,
        video0_.views as views6_4_0_,
        channel2_.uploader as uploader2_0_1_,
        channel2_.uploader_avatar as uploader3_0_1_,
        channel2_.verified as verified4_0_1_ 
    from
        videos video0_ cross 
    join
        users user1_ 
    left outer join
        channels channel2_ 
            on video0_.uploader_id=channel2_.uploader_id 
    where
        user1_.id=**redacted** 
    order by
        video0_.uploaded desc

Code for entities:

github . com /TeamPiped/Piped-Backend/blob/master/src/main/java/me/kavin/piped/utils/obj/db/Channel.java

Is there something I’m doing wrong?

Thanks!

2

I don’t see a question or problem description? How should anyone understand from the code you posted here what you want?

3

The problem I have here is that the:

.where(root.get("channel").get("uploader_id").in(userRoot.get("subscribed_ids"))) // This isn't working

This line isn’t filtering channel entities with the uploader_id being from the user’s subscribed_ids collection.

4

If you read the java doc of the JPA Criteria API carefully, you will see that where will replace the existing predicate with the given one. What you want is this:

criteria.select(root)
    .where(
        cb.and(
            root.get("channel").get("uploader_id").in(userRoot.get("subscribed_ids")), 
            cb.equal(userRoot.get("id"), user.getId())
        )
    )
    .orderBy(cb.desc(root.get("uploaded")));
1 Like
5

Thank you! Now, I seem to be getting an SQLGrammarException:

Hibernate: 
    select
        distinct video0_.id as id1_7_0_,
        channel2_.uploader_id as uploader1_0_1_,
        video0_.uploader_id as uploader7_7_0_,
        video0_.duration as duration2_7_0_,
        video0_.thumbnail as thumbnai3_7_0_,
        video0_.title as title4_7_0_,
        video0_.uploaded as uploaded5_7_0_,
        video0_.views as views6_7_0_,
        channel2_.uploader as uploader2_0_1_,
        channel2_.uploader_avatar as uploader3_0_1_,
        channel2_.verified as verified4_0_1_ 
    from
        videos video0_ cross 
    join
        users user1_ 
    left outer join
        channels channel2_ 
            on video0_.uploader_id=channel2_.uploader_id cross 
    join
        users_subscribed subscribed3_ 
    where
        user1_.id=subscribed3_.subscriber 
        and (
            video0_.uploader_id in (
                .
            )
        ) 
        and user1_.id=708864909373472770 
    order by
        video0_.uploaded desc
10:46:49.501 [pool-4-thread-7] ERROR org.hibernate.engine.jdbc.spi.SqlExceptionHelper - ERROR: at or near ".": syntax error
  Detail: source SQL:
select distinct video0_.id as id1_7_0_, channel2_.uploader_id as uploader1_0_1_, video0_.uploader_id as uploader7_7_0_, video0_.duration as duration2_7_0_, video0_.thumbnail as thumbnai3_7_0_, video0_.title as title4_7_0_, video0_.uploaded as uploaded5_7_0_, video0_.views as views6_7_0_, channel2_.uploader as uploader2_0_1_, channel2_.uploader_avatar as uploader3_0_1_, channel2_.verified as verified4_0_1_ from videos video0_ cross join users user1_ left outer join channels channel2_ on video0_.uploader_id=channel2_.uploader_id cross join users_subscribed subscribed3_ where user1_.id=subscribed3_.subscriber and (video0_.uploader_id in (.)) and user1_.id=708864909373472770 order by video0_.uploaded desc

It looks like the userRoot.get("subscribed_ids") is being compiled to a .

6

You should actually see an error earlier, because what you are trying to do here is “illegal”, but I guess the old query parser just isn’t smart enough. You should be using this instead:

criteria.select(root)
   .where(
       cb.and(
           cb.isMember(root.get("channel").get("uploader_id"), userRoot.get("subscribed_ids")), 
           cb.equal(userRoot.get("id"), user.getId())
       )
   )
   .orderBy(cb.desc(root.get("uploaded")));
1 Like