[FIXED] Spring Data JPA: JPQL query for many to many relationship using inner join?

Issue

I have three entities with many to many relationship between them.
Packages <--> join table <--> ProtectedItems <--> join table <--> ContentItems.

I want to query all Packages that are associated with some Content Items. Currently i am using a native sql query with inner joins.

SELECT ci.stream_id, p.package_id, p.package_name FROM packages p
    INNER JOIN jt_packages_protected_items jtppi
    ON p.account_id=jtppi.p_account_id AND p.package_id=jtppi.package_id 
        INNER JOIN protected_items pi
        ON jtppi.pi_account_id=pi.account_id AND jtppi.protected_item_id=pi.protected_item_id
            INNER JOIN jt_protected_items_stream_mappings jtpism
            ON pi.account_id=jtpism.pi_account_id AND pi.protected_item_id=jtpism.protected_item_id
                INNER JOIN content_items ci
                ON jtpism.ci_account_id=ci.account_id AND jtpism.content_id_extension=ci.content_id_extension
            

How can i convert the above native query to jpql query. Any help will be greatly appreciated. Basically i want to know how to join multiple tables in JPQL.

Edit following answer from @Eugene - What if i want to also filter results by packageId.
I tried below -
1)This did not work - does not compile

@Query("SELECT p2 from (SELECT p FROM Packages p WHERE p.packageId.packageId=1) as p2 inner join pp.protectedItems pi inner join pi.streamMappings ci WHERE ci.streamId=:streamId")

2)This worked - but is this the right way and is this optimized.

@Query("SELECT p FROM Packages p inner join p.protectedItems pi inner join pi.streamMappings ci WHERE ci.streamId=:streamId AND p.packageId.packageId=:packageId")

If there is a better way - then plz suggest..

Solution

JPQL example. Returns two entities, Packages and their ContentItems as described in your native query.

SELECT p, ci FROM Packages p inner join p.protectedItems pi inner join pi.contentItems ci

Repository example:

public interface PackagesRepository extends JpaRepository<Packages, Long> {
    @Query("SELECT p, ci FROM Packages p inner join p.protectedItems pi " +
           "                             inner join pi.contentItems ci")
    List<Object[]> findPackages();
}

Generated query:

    select
        packages0_.id as id1_29_0_,
        contentite4_.id as id1_12_1_,
        packages0_.package_name as package_2_29_0_,
        contentite4_.stream_id as stream_i2_12_1_ 
    from
        packages packages0_ 
    inner join
        packages_protected_items protectedi1_ 
            on packages0_.id=protectedi1_.packages_id 
    inner join
        protected_items protectedi2_ 
            on protectedi1_.protected_items_id=protectedi2_.id 
    inner join
        protected_items_content_items contentite3_ 
            on protectedi2_.id=contentite3_.protected_items_id 
    inner join
        content_items contentite4_ 
            on contentite3_.content_items_id=contentite4_.id

Example to return Packages by ContentItems criteria

public interface PackagesRepository extends JpaRepository<Packages, Long> {
    @Query("SELECT p FROM Packages p inner join p.protectedItems pi inner join pi.contentItems ci WHERE ci.streamId = :streamId")
    List<Packages> findPackages(String streamId);
}

Generated query:

    select
        packages0_.id as id1_29_,
        packages0_.package_name as package_2_29_ 
    from
        packages packages0_ 
    inner join
        packages_protected_items protectedi1_ 
            on packages0_.id=protectedi1_.packages_id 
    inner join
        protected_items protectedi2_ 
            on protectedi1_.protected_items_id=protectedi2_.id 
    inner join
        protected_items_content_items contentite3_ 
            on protectedi2_.id=contentite3_.protected_items_id 
    inner join
        content_items contentite4_ 
            on contentite3_.content_items_id=contentite4_.id 
    where
        contentite4_.stream_id=?

UPDATE:
JPQL query with streamId and packageId criteria.

public interface PackagesRepository extends JpaRepository<Packages, Long> {
    @Query("SELECT p, ci FROM Packages p inner join p.protectedItems pi inner join pi.contentItems ci WHERE ci.streamId = :streamId and p.id = :packageId")
    List<javax.persistence.Tuple> findPackages(String streamId, Long packageId);
}

For such a complex structure, this is an optimal query. It is the same like you described at point 2. Instead of Object[] for returning multiple entities from the query you can use javax.persistence.Tuple also.
Generated query:

    select
        packages0_.id as id1_29_0_,
        contentite4_.id as id1_12_1_,
        packages0_.package_name as package_2_29_0_,
        contentite4_.stream_id as stream_i2_12_1_ 
    from
        packages packages0_ 
    inner join
        packages_protected_items protectedi1_ 
            on packages0_.id=protectedi1_.packages_id 
    inner join
        protected_items protectedi2_ 
            on protectedi1_.protected_items_id=protectedi2_.id 
    inner join
        protected_items_content_items contentite3_ 
            on protectedi2_.id=contentite3_.protected_items_id 
    inner join
        content_items contentite4_ 
            on contentite3_.content_items_id=contentite4_.id 
    where
        contentite4_.stream_id=? 
        and packages0_.id=?

Answered By - Eugene
Answer Checked By - Mary Flores (JavaFixing Volunteer)