# How to use Hibernate's @Filter annotation in an @EmbeddedId property?

**URL:** <https://discourse.hibernate.org/t/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property/2220>\
**Category:** Hibernate ORM\
**Created:** [February 5, 2019, 4:32pm UTC](https://discourse.hibernate.org/t/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property/2220 "2019-02-05T16:32:22Z")\
**Posts on this page:** 9\
**Page:** 1

<div class="post-metadata">

**Author:** ![yagotlima](https://avatars.discourse-cdn.com/v4/letter/y/0ea827/32.png) [@yagotlima](https://discourse.hibernate.org/u/yagotlima)\
**Post date:** [February 5, 2019, 4:32pm UTC](https://discourse.hibernate.org/t/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property/2220/1 "2019-02-05T16:32:22Z")

</div>

## tl;dr

Can’t make Hibernate filter work with an embedded id property.

Sample project to reproduce the issue [here](https://gitlab.com/yagotlima/hibernate-filter-issue-example-project)

## Some Introduction

Hey there. I’m new to this forum so if I did something wrong I’ll fix it as soon as possible if you guys point out.

I’m creating this question after I tried to fix the problem with help from [this post on stackoverflow](https://stackoverflow.com/questions/54486142/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property?answertab=oldest#tab-top) (many thanks to @Andronicus there).

There is a sample project to reproduce the issue. Link in the first section (can’t put more links in this post)

The following section contains my question copied from stackoverflow.

## Actual question

I’m struggling with this query for quite a while.

Suppose the following entity mapping example:

```auto
@Entity
class Client {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "client_id")
    @Basic(optional = false)
    private Integer id;

    @Basic(optional = false)
    private String name;

    @OneToMany(mappedBy = "client")
    private List<CarRent> rentHistory;

    // ... getters and setters
}

@Entity
class Car {
    @Id
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "car_id")
    @Basic(optional = false)
    private Integer id;

    @Basic(optional = false)
    private String foo;

    // ... getters and setters
}

@Entity
class CarRent {
    @EmbeddedId
    private CarRentKey carRentKey;

    @MapsId("clientId")
    @ManyToOne()
    @JoinColumn(name = "client_id", nullable = false, insertable = false, updatable = false)
    private Client client;

    @MapsId("carId")
    @ManyToOne()
    @JoinColumn(name = "car_id", nullable = false, insertable = false, updatable = false)
    private Car car;

    @Basic(optional = false)
    private String bar;

    // ... getters and setters
}

@Embeddable
class CarRentKey {
    private int clientId;
    private int carId;
    @Column(name = "date_due")
    private Date dateDue;

    // ... getters and setters
}

```

I need to fetch all Clients with theis rentHistory populated with CarRents from a certain date. The following query would work perfectly for me:

```auto
from Client cl
    left outer join fetch c.rentHistory as rent with rent.car = c and rent.dateDue = :date

```

But Hibernate keeps telling me to use a filter when fetching a join in the exception.

I tryed

```auto
@Entity
@FilterDef(name="dateDueFilter", parameters= {
    @ParamDef( name="dateDue", type="date" ),
})
@Filters( {
    @Filter(name="dateDueFilter", condition="dateDue = :dateDue"),
})
class CarRent {
    // ...
}

```

but then when I run my query like:

```auto
EntityManager em;
// ...
Session hibernateSession = em.unwrap(Session.class);
hibernateSession.enableFilter("dateDueFilter").setParameter("dateDue", dateDue);
em.createQuery("from Client cl"
    + "left outer join fetch c.rentHistory");
List<Client> clientList = q.getResultList();
// clientList contains CarRent of all dates

```

The filter is just ignored. Same result with `condition="carRentKey.dateDue = :dateDue"` and `condition="date_due = :dateDue"` .

I use filters for other left outer joins on the same query and they work just fine. But this one relation that evolves an embedded parameter I can’t find a way to make it work.

Is it possible? Are there alternatives?

PS: filtering in the where section e.g. `from Client cl left outer join fetch c.rentHistory as rent where rent.dateDue is null or rent.dateDue = :date` is not an option since my real query has other joins which results get filtered and gets really slow when I do so.

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 5, 2019, 5:12pm UTC](https://discourse.hibernate.org/t/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property/2220/2 "2019-02-05T17:12:31Z")

</div>

First of all, you can filter a JOIN FETCH collection:

```
from Client cl
left outer join fetch c.rentHistory as rent with rent.car = c and rent.dateDue = :date

```

You could use a query like this one:

```auto
select cl, cr
from Client cl
join CarRent cr on cr.client = cl and cr.dateDue = :date

```

Check out this article for more details about [joining unrelated entities](https://vladmihalcea.com/how-to-join-unrelated-entities-with-jpa-and-hibernate/).

The `@Filter` annotation is just for filtering a collection in a Parent-Child association, not for expressing LEFT JOIN queries with dynamic joining filtering criteria.

---

<div class="post-metadata">

**Author:** ![yagotlima](https://avatars.discourse-cdn.com/v4/letter/y/0ea827/32.png) [@yagotlima](https://discourse.hibernate.org/u/yagotlima)\
**Post date:** [February 5, 2019, 6:17pm UTC](https://discourse.hibernate.org/t/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property/2220/3 "2019-02-05T18:17:19Z")

</div>

Thank you for the fast response.

> Check out this article for more details about [joining unrelated entities](https://vladmihalcea.com/how-to-join-unrelated-entities-with-jpa-and-hibernate/).

I looked at the article you sent me but I can’t see how to solve the problem yet. It hinted me to upgrade hibernate version for now.

We are on hibernate 4.3.11 for this project but I upgraded my example to 5.4.1 to run some tests.

> ```auto
> select cl, cr
> from Client cl
> join CarRent cr on cr.client = cl and cr.dateDue = :date
> 
> ```

On hibernate 5.4.1 I can run this one actually:

```auto
select cl, cr
from Client cl
join CarRent cr on cr.client = cl and cr.carRentKey.dateDue = :date

```

But it’s not quite what I expected.

My original query returned a list of clients filled with the list of rents on that date or an empty list if none.

The alternative would be to query for a Tuple, like in your article, and manually build the structure my method is expected to return.

Is it the only way to do it?

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 5, 2019, 6:50pm UTC](https://discourse.hibernate.org/t/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property/2220/4 "2019-02-05T18:50:11Z")

</div>

> [@yagotlima](#):
>
> Is it the only way to do it?

You can always transform a tuple or table-based result set to a parent-child hierarchy. Check out [this article](https://vladmihalcea.com/fix-hibernate-hhh000104-entity-fetch-pagination-warning-message/) for more details.

---

<div class="post-metadata">

**Author:** ![yagotlima](https://avatars.discourse-cdn.com/v4/letter/y/0ea827/32.png) [@yagotlima](https://discourse.hibernate.org/u/yagotlima)\
**Post date:** [February 6, 2019, 4:15pm UTC](https://discourse.hibernate.org/t/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property/2220/5 "2019-02-06T16:15:32Z")

</div>

> [@vlad](#):
>
> > [@yagotlima](#):
> >
> > Is it the only way to do it?
> 
> You can always transform a tuple or table-based result set to a parent-child hierarchy. Check out [this article](https://vladmihalcea.com/fix-hibernate-hhh000104-entity-fetch-pagination-warning-message/) for more details.

Wow. Thank you. Didn’t know that was possible.

I think I can adapt my query to join these tables without fetch and then apply a transformer to build my object tree.

That would be much better than relying on a filter indeed.

Will give it a try.

If it works as expected my problem for this specific query would be solved.

But there’s one last thing I’d like to know: is it possible to apply a filter to an embedded property? What would be the correct way to do it?

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 6, 2019, 4:37pm UTC](https://discourse.hibernate.org/t/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property/2220/6 "2019-02-06T16:37:57Z")

</div>

> [@yagotlima](#):
>
> But there’s one last thing I’d like to know: is it possible to apply a filter to an embedded property? What would be the correct way to do it?

The `@Filter` takes a SQL condition, so you can apply it to any DB column you want even if that’s mapped to an Enum property.

---

<div class="post-metadata">

**Author:** ![yagotlima](https://avatars.discourse-cdn.com/v4/letter/y/0ea827/32.png) [@yagotlima](https://discourse.hibernate.org/u/yagotlima)\
**Post date:** [February 6, 2019, 4:58pm UTC](https://discourse.hibernate.org/t/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property/2220/7 "2019-02-06T16:58:49Z")

</div>

> [@vlad](#):
>
> > [@yagotlima](#):
> >
> > But there’s one last thing I’d like to know: is it possible to apply a filter to an embedded property? What would be the correct way to do it?
> 
> The `@Filter` takes a SQL condition, so you can apply it to any DB column you want even if that’s mapped to an Enum property.

Great. Tested it and I think that the strange behaviour I observed was because of misuse of the @Filter annotation as you said.

```auto
select distinct(cl)
    from Client cl
    left outer join fetch cl.rentHistory

```

Is not filtered but the following query is:

```auto
from CarRent cr

```

I’m still working on my new query as proposed but I believe this problem is solved.

Thank you very much for your help.

---

<div class="post-metadata">

**Author:** ![vlad](https://yyz1.discourse-cdn.com/flex035/user_avatar/discourse.hibernate.org/vlad/32/658_2.png) [@vlad](https://discourse.hibernate.org/u/vlad)\
**Post date:** [February 6, 2019, 5:48pm UTC](https://discourse.hibernate.org/t/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property/2220/8 "2019-02-06T17:48:31Z")

</div>

The `@Filter` works when fetching entities directly or navigating them, not for queries.

---

<div class="post-metadata">

**Author:** ![yagotlima](https://avatars.discourse-cdn.com/v4/letter/y/0ea827/32.png) [@yagotlima](https://discourse.hibernate.org/u/yagotlima)\
**Post date:** [February 6, 2019, 6:32pm UTC](https://discourse.hibernate.org/t/how-to-use-hibernates-filter-annotation-in-an-embeddedid-property/2220/9 "2019-02-06T18:32:16Z")

</div>

For the sake of completeness the solution I used to solve the problem follows:

```auto
org.hibernate.query.Query<Client> q = em.createQuery("select cl, cr " + 
		"from Client cl " + 
		"join CarRent cr on cr.client = cl and cr.carRentKey.dateDue = :date")
		.setParameter("date", dateDue)
		.unwrap(org.hibernate.query.Query.class).setResultTransformer(new BasicTransformerAdapter() {
			@Override
			public List transformList(List list) {
				Map<Serializable, Client> clientMap =
			            new LinkedHashMap<>(list.size());

		        for (Object entityArray: list) {
		            if (Object[].class.isAssignableFrom(entityArray.getClass())) {
		                Client client = null;
		                CarRent carRent = null;
		 
		                Object[] tuples = (Object[]) entityArray;
		                
		                for (Object tuple : tuples) {
	                        em.detach(tuple);
	 
	                        if (tuple instanceof Client) {
	                        	client = (Client) tuple;
	                        }
	                        else if (tuple instanceof CarRent) {
	                        	carRent = (CarRent) tuple;
	                        }
	                        else {
	                            throw new UnsupportedOperationException(
	                                "Tuple " + tuple.getClass() + " is not supported!"
	                            );
	                        }
		                }
		 
		                if (client != null) {
		                    if (!clientMap.containsKey(client.getId())) {
		                    	clientMap.put(client.getId(), client);
		                    	client.setRentHistory(new ArrayList<>());;
		                    }
		                    if (carRent != null) {
		                    	client.getRentHistory().add(carRent);
		                    }
		                }
		            }
		        }
		        return new ArrayList<>(clientMap.values());
			}
		});

List<Client> clientList = q.getResultList();

```