Hi devs,
Is it allowed to have use a "sub-sub-query" in JPQL? For instance, if I
want to select publishers with last published magazines, I can use this
select
SELECT p, m
FROM Publisher p
LEFT OUTER JOIN p.magazineCollection m
WHERE m.datePublished = (SELECT MAX(m3.datePublished)
FROM Magazine m3
WHERE m3.idPublisher.id = p.id)
But if I add another select, to make sure that ony one magazine per
publisher is selected:
SELECT p, m
FROM Publisher p
LEFT OUTER JOIN p.magazineCollection m
WHERE m.id = (SELECT MAX(m2.id)
FROM Magazine m2
WHERE m2.idPublisher.id = p.id
AND m2.datePublished =
(SELECT MAX(m3.datePublished)
FROM Magazine m3
WHERE m3.idPublisher.id = p.id))
OpenJPA 1.2.0 translates this into native (MySQL) query:
SELECT t0.id, t1.id, t1.date_published, t1.id_publisher, t1.name
FROM Publisher t0
LEFT OUTER JOIN Magazine t1
ON t0.id = t1.id_publisher
WHERE (t1.id = (SELECT MAX(t4.id)
FROM Magazine t4
WHERE (t2.id_publisher = t0.id
AND t2.date_published =
(SELECT MAX(t3.date_published)
FROM Magazine t2, Magazine t3
WHERE (t3.id_publisher = t0.id)))))
and that query throws an exception:
NestedThrowables:
com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: Unknown column
't2.id_publisher' in 'where clause'
Note, in the first subquery the FROM clause declares t4, but WHERE
clause references t2, which was daclared in the second subquery, and
therefore not visible.
Also note, the second subquery selects from two tables "magazine", which
is unnecessary.
Find the entity beans in the attachment.
Configuration:
- OpenJPA 1.2.0
- MySQL 5.0.22
Regards,
Ognjen
package magazine.model;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.OneToMany;
import java.util.Set;
@Entity
public class Publisher implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="id")
private int id;
@Column(name="name")
private String name;
@OneToMany(mappedBy="idPublisher")
private Set<Magazine> magazineCollection;
private static final long serialVersionUID = 1L;
public int getId() {
return this.id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public Set<Magazine> getMagazineCollection() {
return this.magazineCollection;
}
public void setMagazineCollection(Set<Magazine> magazineCollection) {
this.magazineCollection = magazineCollection;
}
@Override
public String toString() {
return name;
}
}
package magazine.model;
import java.io.Serializable;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.Id;
import javax.persistence.GeneratedValue;
import javax.persistence.GenerationType;
import javax.persistence.JoinColumn;
import javax.persistence.ManyToOne;
import javax.persistence.FetchType;
import java.sql.Date;
@Entity
public class Magazine implements Serializable {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name="id")
private int id;
@Column(name="name")
private String name;
@Column(name="date_published")
private Date datePublished;
@ManyToOne(fetch=FetchType.LAZY)
@JoinColumn(name="id_publisher")
private Publisher idPublisher;
private static final long serialVersionUID = 1L;
public int getId() {
return this.id;
}
public void setId(int id) {
this.id = id;
}
public String getName() {
return this.name;
}
public void setName(String name) {
this.name = name;
}
public Publisher getIdPublisher() {
return this.idPublisher;
}
public void setIdPublisher(Publisher idPublisher) {
this.idPublisher = idPublisher;
}
public Date getDatePublished() {
return datePublished;
}
public void setDatePublished(Date datePublished) {
this.datePublished = datePublished;
}
@Override
public String toString() {
return name;
}
}
