# Use @PartitionKey when loading related entities

**URL:** <https://discourse.hibernate.org/t/use-partitionkey-when-loading-related-entities/8009>\
**Category:** Hibernate ORM\
**Created:** [July 24, 2023, 3:35pm UTC](https://discourse.hibernate.org/t/use-partitionkey-when-loading-related-entities/8009 "2023-07-24T15:35:02Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![jcarrig](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/jcarrig/32/2579_2.png) [@jcarrig](https://discourse.hibernate.org/u/jcarrig)\
**Post date:** [July 24, 2023, 3:35pm UTC](https://discourse.hibernate.org/t/use-partitionkey-when-loading-related-entities/8009/1 "2023-07-24T15:35:02Z")

</div>

Recently in Hibernate 6.2 `@PartitionKey` was introduced, which will add the partition key column to the where clause of `update` and `delete` statements. Is it also possible to add the partition key column to the where clause when loading a related entity or entity collection?

See the two entities below with one-to-many and many-to-one relationships:

```auto
@Entity
@Table(name = "contacts")
public class Contact {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @NotNull
    @PartitionKey
    @Column(name = "tenant_id", updatable = false)
    private Long tenantId;

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

    @OneToMany(mappedBy = "contact")
    private Set<ContactEmail> emails = new HashSet<>();

    public Set<ContactEmail> getEmails() {
        return emails;
    }
}

@Entity
@Table(name = "contact_emails")
public class ContactEmail {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "id")
    private Long id;

    @NotNull
    @PartitionKey
    @Column(name = "tenant_id", updatable = false)
    private Long tenantId;

    @NotNull
    @Column(name = "email")
    private String email;

    @NotNull
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "contact_id")
    private Contact contact;

    public Contact getContact() {
        return contact;
    }
}

```

Example of accessing the entity collection:

```auto
// accessing the getter
contact.getEmails();

// today generates the following sql:
SELECT id, tenant_id, email, contact_id FROM contact_emails WHERE contact_id = ?;

// desired sql with partition key in where clause:
SELECT id, tenant_id, email, contact_id FROM contact_emails WHERE contact_id = ? AND tenant_id = ?;

```

Example of accessing the entity:

```auto
// accessing the getter
contactEmail.getContact();

// today generates the following sql:
SELECT id, tenant_id, name FROM contacts WHERE id = ?;

// desired sql with partition key in where clause:
SELECT id, tenant_id, name FROM contacts WHERE id = ? AND tenant_id = ?;

```

---

<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:** [July 24, 2023, 3:40pm UTC](https://discourse.hibernate.org/t/use-partitionkey-when-loading-related-entities/8009/2 "2023-07-24T15:40:16Z")

</div>

That’s not possible and probably also not very useful. If you want this to reliably work for joins and subselects, you should rather use the `tenant_id` as join column on your association.

---

<div class="post-metadata">

**Author:** ![jcarrig](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/jcarrig/32/2579_2.png) [@jcarrig](https://discourse.hibernate.org/u/jcarrig)\
**Post date:** [July 24, 2023, 3:50pm UTC](https://discourse.hibernate.org/t/use-partitionkey-when-loading-related-entities/8009/3 "2023-07-24T15:50:29Z")

</div>

Thanks for the quick response.

> [@beikov](#):
>
> That’s not possible and probably also not very useful.

My use case: without the partition key in the where clause, Vitess will perform a scatter query, which means it queries all shards because it doesn’t know which specific shard to query. I am currently using hibernate, but in cases like this I have to avoid using the getters so I can avoid the scatter queries.

Also, why is it impossible?

---

<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:** [July 24, 2023, 4:06pm UTC](https://discourse.hibernate.org/t/use-partitionkey-when-loading-related-entities/8009/4 "2023-07-24T16:06:46Z")

</div>

If you want this to reliably work, add the `tenant_id` to your association’s join columns.

---

<div class="post-metadata">

**Author:** ![raiduster](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/raiduster/32/3068_2.png) [@raiduster](https://discourse.hibernate.org/u/raiduster)\
**Post date:** [March 6, 2024, 11:47am UTC](https://discourse.hibernate.org/t/use-partitionkey-when-loading-related-entities/8009/5 "2024-03-06T11:47:21Z")

</div>

@jcarrig I need the same thing as you. How did you do it?