JPQL Query to select entity from one-to-one relationship where it’s relative has a field matching a certain condition

I have three entities: EntityA maps to table_a, EntityB maps to table_b, and Catalog maps to catalog. In the database, there’s a many-to-many table between table_b and catalog, b_catalog_xref. EntityB has a field: Long aId, and a field: List<Catalog> catalogs. The Catalog entity has a field: String name. Given a list of IDs for EntityB, and a string representing a catalog name, I need to retrieve all occurrences of EntityA whose ID matches that of an EntityB’s aId, and where the given catalog name matches that of one of EntityB’s catalogs.

I’ve successfully grabbed the correct data via regular SQL, but I’m struggling to recreate the query in JPQL. Here’s the SQL query:

SQL:

SELECT
   *
FROM
    table_a a
WHERE
    a.table_a_id in (
    SELECT
        b.table_a_id
    FROM
        table_b b
        INNER JOIN b_catalog_xref bcx ON bcx.table_b_id = b.table_b_id
        INNER JOIN catalog c ON c.catalog_id = bcx.catalog_id
    WHERE
        c.catalog_name = 'Example Catalog Name'
);

Java:

@Entity
@Table(name = "table_a")
public class EntityA {

    @Id
    @Column(name = "table_a_id")
    private Long aId;

    ...
}
@Entity
@Table(name = "table_b")
public class EntityA {

    @Id
    @Column(name = "table_b_id")
    private Long bId;

    @Column(name = "table_a_id")
    private Long aId;

    @OneToMany(fetch = FetchType.EAGER, cascade = {CascadeType.DETACH})
    @JoinTable(name = "b_catalog_xref",
               joinColumns = {@JoinColumn(name = "table_b_id")},
               inverseJoinColumns = {@JoinColumn(name = "catalog_id")})
    @Fetch(FetchMode.SELECT)
    @OrderBy("name ASC")
    List<Catalog> catalogs

    ...
}

@Entity
@Table(name = "catalog")
public class Catalog {

    @Id
    @Column(name = "catalog_id")
    private Long catalogId;

    @Column(name = "catalog_name")
    private String name;

    ...
}

Answer

Yes, you can use something like

TypedQuery<TableA> q = entityManager.createQuery
            ("Select a from TableA a where a.aId in(Select b.aId from TableB b " +
                    "join b.catalogs c where c.name=:name)", TableA.class);
q.setParameter("name", "some2");

I advise you to consider creating a relationship between tables A and B instead of copying the key of table A to table B

@Entity
@Table(name = "table_b")
public class EntityB {
...
//    @Column(name = "table_a_id")
//    private Long aId;

    @OneToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "table_a_id")
    private TableA tableA;
...
}

Then JPQL will look like:

TypedQuery<TableA> q = entityManager.createQuery
                ("Select distinct b.tableA from TableB b join b.catalogs c " +
                        "where c.name=:name", TableA.class);
q.setParameter("name", "some2");

Pay attention to the keyword distinct, it removes all duplicates in the result list.

And do not use FetchType.EAGER unless absolutely necessary, use FetchType.LAZY.