openjpa-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From "catalina wei" <catalina....@gmail.com>
Subject Re: Sub-sub-query not working
Date Mon, 01 Sep 2008 00:00:59 GMT
Hi,
This problem is resolved in OPENJPA-708, fix checked in under trunk
r690823..

Catalina Wei

On Thu, Aug 28, 2008 at 7:42 AM, Ognjen Blagojevic <ognjen@etf.bg.ac.yu>wrote:

> 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;
>    }
> }
>
>

Mime
  • Unnamed multipart/alternative (inline, None, 0 bytes)
View raw message