0

I have a one-to-many relationship between routes and stops. In order to maintain an audit trail, my Stop entities have a "historic" boolean.

When fetching a route, I want to ignore historic stops, and so I constructed this query:

@Query("select r from Route r " +
            "left join fetch r.schedules schedule " +
            "left join fetch r.stops stop " +
            "where r.routeId = :routeId and stop.historic = false ")
Optional<Route> findByIdLoadStops(@Param("routeId") int routeId);

This works fine when the route has non-historic stops and no stops, but when the route only has a historic stop (which shouldn't happen but I want to be able to at least handle it), it returns an empty optional as though an inner join has been performed.

When logging the JPA query created by hibernate, I can see that the query uses a left outer join.

What have I done incorrectly?

Route and Stop entities:

@Table(name = "route")
@Entity
public class Route {

    @Id
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Column(name = "routeId", columnDefinition = "SMALLINT(5) UNSIGNED")
    private int routeId;

    @Column(name = "locked")
    private boolean locked = false;

    @OneToMany(mappedBy = "route",
            cascade = CascadeType.ALL,
            fetch = FetchType.LAZY)
    @OrderBy("stopTime asc")
    private SortedSet<Stop> stops = new TreeSet<>();

    public Route() {
    }

}

@Table(name = "stop", uniqueConstraints = {
        @UniqueConstraint(columnNames = {"stopTime", "routeId"}),
        @UniqueConstraint(columnNames = {"stopName", "routeId"})})
@Entity
public class Stop implements Comparable<Stop> {

    @Id
    @Column(name = "stopId")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private int stopId;

    @Column(name = "routeId",
            columnDefinition = "SMALLINT(5)",
            updatable = false, insertable = false)
    private int routeId;

    @ManyToOne(cascade = CascadeType.MERGE,
            fetch = FetchType.LAZY)
    @JoinColumn(name = "routeId")
    private Route route;

    @Column(name = "stopTime")
    private LocalTime stopTime;

    @Column(name = "stopName")
    private String stopName;

    @JoinColumn(name = "originalId", referencedColumnName = "stopId")
    @ManyToOne(fetch = FetchType.LAZY)
    private Stop originalStop = this;

    @Column(name = "historic")
    private boolean historic = false;

    public Stop() {
    }

}
Jordan Mackie
  • 2,264
  • 4
  • 25
  • 45
  • I think you probably need `left outer join` which returns rows when the join has no rows. Simple joins only return rows when there are join relations. – K.Nicholas Dec 31 '18 at 00:32
  • @K.Nicholas LEFT JOIN and LEFT OUTER JOIN are synonymous – Simon Martinelli Dec 31 '18 at 13:25
  • [LEFT JOIN vs. LEFT OUTER JOIN in SQL Server](https://stackoverflow.com/questions/406294/left-join-vs-left-outer-join-in-sql-server) – K.Nicholas Dec 31 '18 at 16:12
  • Yeah no luck with left outer join – Jordan Mackie Dec 31 '18 at 22:50
  • 1
    It would be helpful if you show your entities. – K.Nicholas Jan 01 '19 at 19:48
  • Not sure I follow, what do you need the `stop.historic = false` condition for? Which entities **don't** you want in the response? – crizzis Jan 02 '19 at 15:23
  • @crizzis Everytime a Stop is updated, I need to be able to refer to the old version for auditing purposes. I do this by creating a new Stop for every update, the old version is marked historic and contains a pointer to the original. See [here](https://www.codeproject.com/Articles/105768/Audit-Trail-Tracing-Data-Changes-in-Database) for an example of this design pattern – Jordan Mackie Jan 02 '19 at 21:43

0 Answers0