6

I want to make a distinct select in my table with pagination, but it is claiming this error. Does anyone know how to solve it?

Error:

org.postgresql.util.PSQLException: ERROR:
for SELECT DISTINCT, ORDER BY expressions must appear in select list

@Query(value = "SELECT DISTINCT budget.* FROM budget LEFT JOIN user_budget ON budget.id = user_budget.budget_id ORDER BY budget.created DESC, ?#{#pageable}", 
        countQuery = "SELECT DISTINCT count(*) FROM budget LEFT JOIN user_budget ON budget.id = user_budget.budget_id", 
        nativeQuery = true) 
public Page<Budget> findAllByfilter(Pageable pageable);
Albina
  • 1,901
  • 3
  • 7
  • 19
Vinicius Augutis
  • 61
  • 1
  • 4
  • The error states that whatever value is in ?#{#pageable} needs to be in the select list.. so you probably need to put it into the select. – Julia Leder Jul 12 '18 at 18:02
  • How i put this pageable into the select? – Vinicius Augutis Jul 12 '18 at 18:34
  • Assuming `?#{#pageable}` is a parameter: `@Query(value = "SELECT DISTINCT budget.*, ?#{#pageable} FROM budget LEFT JOIN user_budget ON budget.id = user_budget.budget_id ORDER BY budget.created DESC` You may need to remove it later from wherever you're displaying it. There may be other options and I haven't worked with pagination, so can't tell you, I'm just giving you a suggestion on how to resolve this particular error. – Julia Leder Jul 12 '18 at 20:24
  • I managed to do this through this solution, using DISTINCT ON: – Vinicius Augutis Jul 12 '18 at 20:38
  • `@Query(value = "SELECT DISTINCT ON (budget.created, budget.id) budget.*, user_budget.* FROM budget LEFT JOIN user_budget ON budget.id = user_budget.budget_id ORDER BY budget.created DESC, budget.id, ?#{#pageable}", countQuery = "SELECT COUNT(*) FROM budget LEFT JOIN (SELECT DISTINCT user_budget.budget_id FROM user_budget) ub ON budget.id = ub.budget_id ORDER BY ?#{#pageable}", nativeQuery = true ` – Vinicius Augutis Jul 12 '18 at 20:39
  • So I return all of the budget, whether or not having a relationship in the user_budget class, without duplicates row. – Vinicius Augutis Jul 12 '18 at 20:42

1 Answers1

0

I know this is an old question, but I wanted to add some explanation to the right answer. The question is more focused on PostgreSQL DISTINCT ON clause.

As the documentation says,

  • SELECT DISTINCT clause eliminates duplicate rows from the result (the overall tuple).
  • SELECT DISTINCT ON (your_column1, your_column2 ...) eliminates rows that match on all the specified expressions (the specified columns sort of group the rows and remove duplicates from the result set).
    To keep the returning result set predictable in terms of order, it is better to use ORDER BY clause.
    N.B. If you use DISTINCT ON clause with ORDER BY expression, it is necessary that the leftmost ORDER BY expression(s) need to match DISTINCT ON expression.

More details are covered in this answer.

Albina
  • 1,901
  • 3
  • 7
  • 19