Mapping native query to JPQL (With extra join condition >> ON ... AND ...)

Question

JPA Entities

Organization entity

@Entity
public class Organization implements Serializable{
    private Integer id; // @Id
    private String name;

    // Getters and Setters
}

Place entity

@Entity
public class Place implements Serializable{
    private Integer id; // @Id
    private String name;
    private Organization organization; // @ManyToOne relation

    // Getters and Setters
}

Profile entity

@Entity
public class Profile implements Serializable{
    private String username;// @Id
    private Set<VisitedPlace> visitedPlaces; // @OneToMany(mappedBy = "profile")
    // Getters and Setters
}

VisitedPlace entity

@Entity
public class VisitedPlace implements Serializable{
    private Integer id; // @Id
    private Date visitedDate;
    private Place place; // @ManyToOne relation
    private Profile profile; // @ManyToOne relation

    // Getters and Setters
}

Which will create these tables, but i will show it with sample data too

Organization table

 ===================== 
| id |      name      |
-----+----------------+
| 1  | Organization 1 |
 =====================

Place table

 ================================= 
| id |   name   | organization_id |
-----+----------+-----------------+
| 1  | Place 1  |        1        |
-----+----------+-----------------+
| 2  | Place 2  |        1        |
-----+----------+-----------------+
| 3  | Place 3  |        1        |
 =================================

Profile table

 ============= 
|  username   |
--------------
| 111@xxx.com |
--------------
| 222@xxx.com |
 =============

Visitedplace table

 ================================================ 
| id | visiteddate | place_id | profile_username |
-----+-------------+----------+---------------- -+
| 1  | 2017-01-01  |     1    |   111@xxx.com    |
-----+-------------+----------+------------------+
| 2  | 2017-02-01  |     2    |   111@xxx.com    |
-----+-------------+----------+------------------+
| 3  | 2017-01-15  |     1    |   111@xxx.com    |
 ================================================

My question is how to map next native SQL query to JPQL

SELECT p.username, count(distinct pl.id) 
FROM profile p 
LEFT OUTER JOIN visitedplace vp
ON p.username = vp.profile_username
LEFT JOIN place place
ON vp.place_id = pl.id AND pl.organization_id = 1
GROUP BY p.username;

So i will get next result

 ===================== 
|  username   | count |
 -------------+-------
| 111@xxx.com |   2   |
 -------------+-------
| 222@xxx.com |   0   |
 =====================

I have been try next JPQL

SELECT profile.username, COUNT( DISTINCT place.id )
FROM Profile profile
LEFT OUTER JOIN profile.visitedPlaces visitedPlace
LEFT JOIN visitedPlace.place place
WHERE place.organization.id = 1
GROUP BY profile.username

but it do return

 ===================== 
|  username   | count |
 -------------+-------
| 111@xxx.com |   2   |
 =====================

So my real problem is in converting next line to JPQL

LEFT JOIN place place ON vp.place_id = pl.id AND pl.organization_id = 1

Answer 1

Unfortunately, adding ON clause is only supported in JPA 2.1 spec.

So till now we can't use AND with ON clause.

You can get JPA 2.1 specification from http://download.oracle.com/otndocs/jcp/persistence-2_1-fr-eval-spec/index.html

So lets hope we will get it in next release.