# CriteriaBuilder 2nd root not working

**URL:** <https://discourse.hibernate.org/t/criteriabuilder-2nd-root-not-working/6037>\
**Category:** Hibernate ORM\
**Created:** [February 10, 2022, 11:59am UTC](https://discourse.hibernate.org/t/criteriabuilder-2nd-root-not-working/6037 "2022-02-10T11:59:26Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![kavin](https://avatars.discourse-cdn.com/v4/letter/k/ba8739/32.png) [@kavin](https://discourse.hibernate.org/u/kavin)\
**Post date:** [February 10, 2022, 11:59am UTC](https://discourse.hibernate.org/t/criteriabuilder-2nd-root-not-working/6037/1 "2022-02-10T11:59:26Z")

</div>

```java
            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:

```auto
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:

> <https://github.com/TeamPiped/Piped-Backend/blob/master/src/main/java/me/kavin/piped/utils/obj/db/User.java>

> <https://github.com/TeamPiped/Piped-Backend/blob/master/src/main/java/me/kavin/piped/utils/obj/db/Video.java>

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!

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [February 11, 2022, 7:50am UTC](https://discourse.hibernate.org/t/criteriabuilder-2nd-root-not-working/6037/2 "2022-02-11T07:50:44Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![kavin](https://avatars.discourse-cdn.com/v4/letter/k/ba8739/32.png) [@kavin](https://discourse.hibernate.org/u/kavin)\
**Post date:** [February 13, 2022, 7:23pm UTC](https://discourse.hibernate.org/t/criteriabuilder-2nd-root-not-working/6037/3 "2022-02-13T19:23:42Z")

</div>

The problem I have here is that the:

```java
.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.

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [February 14, 2022, 9:24am UTC](https://discourse.hibernate.org/t/criteriabuilder-2nd-root-not-working/6037/4 "2022-02-14T09:24:23Z")

</div>

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:

```java
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")));

```

---

<div class="post-metadata">

**Author:** ![kavin](https://avatars.discourse-cdn.com/v4/letter/k/ba8739/32.png) [@kavin](https://discourse.hibernate.org/u/kavin)\
**Post date:** [February 15, 2022, 10:49am UTC](https://discourse.hibernate.org/t/criteriabuilder-2nd-root-not-working/6037/5 "2022-02-15T10:49:55Z")

</div>

> [@beikov](#):
>
> ```auto
> 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")));
> 
> ```

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

```auto
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 `.`

---

<div class="post-metadata">

**Author:** ![beikov](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/beikov/32/258_2.png) [@beikov](https://discourse.hibernate.org/u/beikov)\
**Post date:** [February 15, 2022, 11:03am UTC](https://discourse.hibernate.org/t/criteriabuilder-2nd-root-not-working/6037/6 "2022-02-15T11:03:08Z")

</div>

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:

```java
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")));

```
