Inner join over @ElementCollection Map attribute

[Ramón Arnau Gómez]

I tried unsuccessfully make an ilike filter over map collection into entity bean.

The main object has this form:

@Entity
public class Fondo{

 @Columns(columns = @Column(name = "element"))
 @ElementCollection(targetClass = String.class, fetch = FetchType.EAGER)
 @MapKeyColumn(name = "mapkey")
 private Map<String,String> nombre;

 // getters and setters
}

The map contains language as key, and translation for the value. With this object, I can't make an detachedCriteria with ilike of translation. For example to retrieve MainObjects with name containing 'Pedro';

The query that I have is:


    JPAQuery<Fondo> query = new JPAQuery<>(entityManager);
     query.from(fondo);
     query.innerJoin(fondo.nombre).where(fondo.nombre.get("element").like(search.getPartialName())));
     query.orderBy(fondo.id.desc());


The resultant query is:

    select
        *
    from
        Fondo fondo0_ 
    inner join
        Fondo_nombre nombre1_ 
            on fondo0_.id=nombre1_.Fondo_id 
    left outer join
        Fondo_nombre nombre2_ 
            on fondo0_.id=nombre2_.Fondo_id 
            and (
                nombre2_.mapkey=? 
                and (
                    nombre2_.element like ? escape '!'
                )
            ) 
    where
        fondo0_.comunidad_id=? 
        and (
            fondo0_.baja is null 
            or fondo0_.baja>?
        ) 
    order by
        fondo0_.id desc limit ?

Any idea?

Thanks!

[timowest]

Hi.

You don't reuse the inner join in your query.

Why don't you write

JPAQuery<Fondo> query = new JPAQuery<>(entityManager);
     query.from(fondo);


     query.where(fondo.nombre.get("element").like(search.getPartialName())));
     query.orderBy(fondo.id.desc());

[Ramón Arnau Gómez]

Hi!, Thanks for your reply.

With your comment, the filter doesn't work fine. It show all 'Fondo's but no make the ilike operation over the hash map values. The query resultant now is:

    select *
    from
        Fondo fondo0_

    left outer join
        Fondo_nombre nombre1_
            on fondo0_.id=nombre1_.Fondo_id
            and (
                nombre1_.mapkey=?
            )
    where
        nombre1_.element like ? escape '!'

         and (
            fondo0_.baja is null
            or fondo0_.baja>?
        )
    order by
        fondo0_.id desc limit ?