Unknown column ..id in order clause

Question

@Query(value = "SELECT * FROM product_option WHERE 
                sale_closed is false AND product_id IN(SELECT id from product 
                where category_id IN(WITH RECURSIVE cte (id) AS (SELECT id FROM category WHERE id =:parentId 
                UNION SELECT t1.id FROM category t1 INNER JOIN cte t2 ON t1.parent_id = t2.id) 
                SELECT * FROM cte))",nativeQuery=true)
Page<ProductOption> find(Integer parentId,Pageable pageable);

I have categories structured like Category_id | Name | Parent_Id

Product like Product_id | title | brand_id | category_id

ProductOption like Product_option_id | product_id | underTitle | price

The purpose is : Parent category will return all products of its child categories..

Im getting this error : Unknown column 't1.id' in 'order clause'

It is caused by Sort.by(Sort.Direction.ASC, "id") inside PageRequest,

 PageRequest.of(page, size, Sort.by(Sort.Direction.ASC, "id"))

Am i missing something?

Or is there any other way achieving this?

The error mentions a reference to t1.id in the order clause, but the query you show above does not have any ORDER BY clause. I think the error is coming from some other query. — Bill Karwin
Well yes, it was caused by , Sort.by(Sort.Direction.ASC, "id") inside pageable.. — joachim

joachim joachim · Accepted Answer · 2021-01-12T12:09:54

SELECT * FROM product_option p WHERE  p.sale_closed is false AND p.product_id IN

Adding p after product_option solved my problem.

Unknown column ..id in order clause

1 Answers