Incorrect JPQL query formed when joining tables

Question

Let's say I have 2 entities: Application and Organization. They are many-to-many related

Organization:

@Entity
@Table(name = "organizations")
public class Organization
{

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "organizationIdGenerator")
    private long id;  // primary key

    @AttributeOverride(name = "id", column = @Column(name = "organization_id", unique = true, updatable = false))
    @Embedded
    private OrganizationId organizationId;

    @AttributeOverride(name = "id", column = @Column(name = "application_id"))
    @CollectionTable(name = "organization_applications")
    @ElementCollection
    private Set<ApplicationId> applications = new HashSet<>();  // there is no explicit @Many-to-many connection between applications and organizations

    private String name;

    // other data
}

Application:

@Entity
@Table(name = "applications")
public class Application
{

    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "applicationIdGenerator")
    private long id; // primary key

    @AttributeOverride(name = "id", column = @Column(name = "application_id", unique = true, updatable = false))
    @Embedded
    private ApplicationId applicationId;

    private String label;

    // other data 
}

As they are many-to-many related, in relational database I have the following tables:

• organizations
• applications
• organization_applications

table organization_applications is created with the following liquibase-migration:

<createTable tableName="organization_applications">
  <column name="organization_id" type="bigint">
    <constraints nullable="false" foreignKeyName="organization_application_organization_id_fk"
                 referencedTableName="organizations" referencedColumnNames="id"/>
  </column>
  <column name="application_id" type="varchar(50)">
    <constraints nullable="false"/>
  </column>
</createTable>

---

What I want to do is get Applications (List<Application>) for particular Organization in one query

---

I can do it by getting applicationIds of particular organization (lets say with organizationId = 1) and join them with applications with a query like:

select apps.application_id, apps.label 
    from organization_applications org_apps
    join applications apps 
        on (apps.application_id = org_apps.application_id and org_apps.organization_id = 1)

But when I try to accomplish this with JPQL query like this:

SELECT new List(o.applications) FROM Organization o WHERE o.organizationId.id = 1

I get the following query and then and exception:

Hibernate: 
    /* SELECT
        new List(o.applications) 
    FROM
        Organization o 
    WHERE
        o.organizationId.id = :org_id */ select
            . as col_0_0_          // <-- problem here. why is there just a dot?
        from
            organizations organizati0_ 
        inner join
            organization_applications applicatio1_ 
                on organizati0_.id=applicatio1_.organization_id 
        where
            organizati0_.organization_id=?

SqlExceptionHelper   : SQL Error: 20000, SQLState: 42X01
SqlExceptionHelper   : Syntax error: Encountered "." at line 1, column 102.

Seems that in a relation like this where entities are related by custom id fields (applicationId, organiationId) hibernate cannot create query.

Any idea how can I accomplish this?

Thanks in advance!

---

UPD:

ApplicationId holds some validation and looks like this:

public class ApplicationId
{
    private String id;

    // constructor and getters
}

OrganizationId looks exactly the same

Answer 1

@Query("SELECT a FROM Application a WHERE a.applicationId IN " +
        "(SELECT apps FROM Organization o JOIN o.applications apps WHERE o.organizationId = :organization_id) ORDER BY label ASC")

Answer 2

You can select the ApplicationIds like this:

SELECT a FROM Organization o JOIN o.applications a WHERE o.organizationId.id = 1