How to perform a LEFT JOIN using hibernate with dynamic value based on logged in user?

Question

I have three entities:

class Items {
    @Id
    private String id;
    
    @JsonManagedReference
    @OneToOne(fetch = FetchType.LAZY, mappedBy = "items", cascade = CascadeType.ALL)
    @NotAudited
    private ExternalIds externalIds;
    
    // ... other fields, getters, setters, etc.
}

class OrgUnits {
    @Id
    @Column(nullable = false, updatable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Integer id;
    
    // ... other fields, getters, setters, etc.
}

class ExternalIds {
    @Id
    @Column(nullable = false, updatable = false)
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @JsonIgnore
    private Integer id;
    
    @JoinColumn(name = "item_id")
    @OneToOne(fetch = FetchType.LAZY)
    @JsonBackReference
    @NotAudited
    private Items items;
    
    @JoinColumn(name = "ou_id")
    @OneToOne(fetch = FetchType.LAZY)
    @JsonIgnore
    @NotAudited
    private OrgUnits orgUnits;
    
    @Column
    private String externalId;
    
    // ... other fields, getters, setters, etc.
}

The Items entity is a central catalog of items.
The OrgUnits entity is a catalog of organizational units where users work.
The ExternalIds entity is a linking table that contains an external ID for each item per organizational unit. This means that for each item in the catalog, each organization unit can define its own external ID.

Example data:

In the items table, I have 2 rows:

• id = LR-0000760884
• id = LR-0001508935

In the external_ids table, I have 2 rows:

• item_id = LR-0000760884, ou_id = 11, external_id = 81275
• item_id = LR-0000760884, ou_id = 4, external_id = 18975

In the org_units table, I have 4 rows:

• id = 11
• id = 4
• id = 13

I have implemented EntityGraph to optimize data retrieval in my JPA application. I have two NamedQueries: one is used to select items by their id, and the other one retrieves all items.

One of the queries I'm using looks like this:

SELECT f from Items i left join fetch i.externalIds where i.id = :item_id

Generated sql looks like this:

SELECT ... FROM items i LEFT JOIN external_ids e ON i.id = e.item_id WHERE i.id = :item_id

How can I modify entity to ensure that only one instance of the item is returned, along with the associated values from the "external_ids" table only for a specific "ou_id" using hibernate?

I'm expecting this result:

If I search for the item 'LR-0000760884' with organization unit 11, I should get the Items object with the related externalIds = 81275.
If I search for the item 'LR-0000760884' with organization unit 4, I should get the Items object with the related externalIds = 18975.
If I search for the item 'LR-0000760884' with organization unit 13, I should get the Items object with externalIds = null.
If I search for the item 'LR-0001508935' with organization unit 11,4,13, I should get the Items object with externalIds = null.