@OrderBy Hibernate: ERROR: missing FROM-clause entry for table

Question

So I have three entities. A FormCollection contains multiple Form. The Form is created from a template and thus has also a many-to-one relation to FormTemplate.

@Table(name = "form_collection", schema = "public")
public class FormCollectionDO extends BaseAuditableDO {
    @OneToMany(mappedBy = "formCollection")
    @OrderBy("formTemplate.templateId") //throws error
    private List<FormDO> forms = new ArrayList<>();
}

@Table(name = "form", schema = "public")
public class FormDO extends BaseAuditableDO {

    @NotNull
    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "form_template_id")
    private FormTemplateDO formTemplate;
}

@Table(name = "form_template", schema = "public")
@JsonIdentityInfo(generator = ObjectIdGenerators.PropertyGenerator.class, property = "id")
public class FormTemplateDO extends BaseDO {

    @Column(name = "template_id", nullable = false)
    @NotNull
    private Long templateId;
}

@OrderBy("formTemplate.templateId") throws an error:

o.h.engine.jdbc.spi.SqlExceptionHelper : ERROR: missing FROM-clause entry for table "formtemplate"

@OrderBy("formTemplate.id") works fine. The id comes from the abstract class BaseDO. Why does it not work with any of the fields from the FormTemplateDO class?

form_template and formTemplate aren't the same, which one do you need? — Frank Heikens, Dec 11 '20 at 14:50
For my understanding `form_template` is on DB level while `formTemplate` is on Java level. If I use `@OrderBy("form_template.template_id")` I get the same error. — simibac, Dec 11 '20 at 15:56
I get auto-complete with `formTemplate.templateId` in IntelliJ. Also, since it works with `formTemplate.id`, I guess I need `fromTemplate`. — simibac, Dec 11 '20 at 15:59

score 0 · Answer 1 · answered Dec 11 '20 at 15:05

Although I am not sure about this solution, What I am suspecting is this issue happens because the formTemplate.templateId isnt part of your query indeed.

I see you are using @OneToMany for defining the relationship, but in hibernate, the default FetchMode is SELECT which means your order by parameter isnt part of your query. To make this parameter part of your query, you will have to make a Join query.

Try this out -

@Fetch(value = FetchMode.JOIN) 
@OneToMany(mappedBy = "formCollection")
@OrderBy("formTemplate.templateId") //throws error
private List<FormDO> forms = new ArrayList<>();

And propogate this join to further levels. It might solve your problem.

throws the same error for me – beatrice Aug 12 '22 at 12:53 — beatrice, Aug 12 '22 at 12:53

score 0 · Answer 2 · answered Dec 22 '22 at 19:55

When you want to order by a collection of entities by a nested attribute, you can not use @OrderBy, because the nested attribute is not part of your query. You can only use @OrderBy for of first level attribute OR a nested attribute IF it is a collection of @Embeddable.

So for this case, you have to use @SortNatural or @SortComparator.

Similar issue : Hibernate - How to sort internal query lists (or List in List)?
More about @OrderBy vs @SortNatural: Sort vs OrderBy - performance impact

@OrderBy Hibernate: ERROR: missing FROM-clause entry for table

2 Answers2