JPQL distinct with string function

I have a JPQL like this one:

select distinct d 
from Depart开发者_如何学运维ment d
left join fetch d.employees

When I want to fetch one of the lazy property of my Department entity, the distinct is not working any more.

select distinct d, substring(d.htmlDescription, 1,400)
from Department d
left join fetch d.employees

The query returns as much Department as the number of employees in it.

The substring(d.htmlDescription) is important because the property is defined as a CLOB (type TEXT under postgresql):

@Column(columnDefinition = "TEXT")
@Basic(fetch = FetchType.LAZY)
String htmlBody;

The substring function is translated in sql thus limiting the amount of data transfered beetween the database and the web server.

As a workaround, I tried to break the query in two parts : 

select d, substring(d.htmlDescription, 1,400)
from Department d where d in (
    select distinct d1
    from Department d1  left join fetch d1.employees
)

This doestn't work because the JOIN FETCH must not be used in the FROM clause of a subquery.

Finally I found a solution to my problem by :

1. modifying my mapping
2. cutting the request in 2 calls.

The htmlBody field is now in another entity. Thus the departement entity is lighter.

class Department{
...
@OneToOne (fetch = FetchType.LAZY, 
    cascade = {CascadeType.PERSIST, CascadeType.MERGE, CascadeType.REMOVE})
Content content = new Content();
...
}


class Content{
...
@Column(columnDefinition = "TEXT")
@Basic(fetch = FetchType.LAZY)
String htmlBody;
...
}

I can then use the following requests :

    List<Department> deps = em.get().createQuery(
            "select distinct d " +
                    "from Department d " +
                    "order by d.id desc ", Department.class)
            .setFirstResult(first)
            .setMaxResults(count)
            .getResultList();

    List<Object[]> tuple = em.get().createQuery(
            "select d, substring(d.content.htmlBody, 1,400)" +
                    "from Department d " +
                    "left join fetch d.employees" +
                    "where d in (:deps) order by d.id desc")
            .setParameter("deps", deps)
            .getResultList();

    ... //Filter the duplicates due to the fetching

That way, I have 2 sql queries. The fetching of employees is done in the second query witch occurs on a small amount of datas. The substring is realized in SQL. Perfect!

Since I cannot make comments, I would like to point out few things that stick out to me as doubtfull.

1. What is the object returned with distinct d, substring(d.htmlDescription, 1,400)? Could you fetch that String with separate query, or get that substing using Java?
2. I would trust that that query can be rewritten into one without left join statement.
3. Maybe you could rewrite the query so you could put substring statement first and then distinct d?