Sorting

# application.yml: global paging and sorting settings
spring:
  data:
    web:
      pageable:
        default-page-size: 20
        max-page-size: 100            # the default is 2000
        one-indexed-parameters: false
      sort:
        sort-parameter: sort

// GET /users?sort=name,asc
// GET /users?sort=lastName,asc&sort=createdAt,desc       mixed directions: repeat the parameter
// GET /users?sort=lastName,firstName,desc                both fields descending
// GET /users?page=0&size=20&sort=createdAt,desc

@GetMapping
public PagedModel<UserResponse> findAll(
        @PageableDefault(size = 20, sort = {"createdAt", "id"}, direction = Sort.Direction.DESC)
        Pageable pageable) {
    return new PagedModel<>(userService.findAll(pageable));
}

// Sort inside the Pageable becomes: ORDER BY created_at DESC, id DESC
// Unknown property names throw PropertyReferenceException, which is a 500 unless you handle it.
// The next section turns that case into a clean 400.

@GetMapping("/all")
public List<UserResponse> all(@SortDefault(sort = "name", direction = Sort.Direction.ASC) Sort sort) {
    return userService.findAll(sort);
}

public interface UserRepository extends JpaRepository<UserEntity, Long> {
    List<UserEntity> findByStatus(UserStatus status, Sort sort);       // findAll(Sort) is inherited
}

// Building sorts
Sort byName   = Sort.by("name");                                        // ascending by default
Sort newest   = Sort.by(Sort.Direction.DESC, "createdAt", "id");
Sort mixed    = Sort.by(Sort.Order.desc("createdAt"), Sort.Order.asc("name").nullsLast());

// Type-safe: refactoring-proof
Sort typed    = Sort.sort(UserEntity.class).by(UserEntity::getName).ascending();

// Case-insensitive: Order.asc("name").ignoreCase()   -> ORDER BY lower(name); plain index not used

public class SortRules {

    private final Map<String, String> allowed;      // public name -> entity property path
    private final Sort defaultSort;

    public SortRules(Map<String, String> allowed, Sort defaultSort) {
        this.allowed = allowed;
        this.defaultSort = defaultSort;
    }

    public Pageable apply(Pageable pageable) {
        Sort requested = pageable.getSort();

        Sort resolved = requested.isUnsorted()
            ? defaultSort
            : Sort.by(requested.stream().map(order -> {
                  String property = allowed.get(order.getProperty());
                  if (property == null) {
                      throw new InvalidSortException(order.getProperty(), allowed.keySet());
                  }
                  return order.withProperty(property);          // keeps direction, ignoreCase, nulls
              }).toList());

        // Unique tie-breaker so pages never overlap or skip rows
        boolean hasId = resolved.stream().anyMatch(o -> o.getProperty().equals("id"));
        if (!hasId) resolved = resolved.and(Sort.by("id"));

        return PageRequest.of(pageable.getPageNumber(), pageable.getPageSize(), resolved);
    }
}

public class InvalidSortException extends RuntimeException {
    public InvalidSortException(String field, Set<String> allowed) {
        super("Cannot sort by '" + field + "'. Allowed: " + new TreeSet<>(allowed));
    }
}

@RestController
@RequestMapping("/api/v1/users")
public class UserController {

    private static final SortRules SORT = new SortRules(
        Map.of("fullName", "name",
               "joinDate", "createdAt",
               "email",    "email",
               "status",   "status"),
        Sort.by(Sort.Direction.DESC, "createdAt"));

    private final UserService userService;
    public UserController(UserService userService) { this.userService = userService; }

    @GetMapping
    public PagedModel<UserResponse> findAll(@PageableDefault(size = 20) Pageable pageable) {
        return new PagedModel<>(userService.findAll(SORT.apply(pageable)));
    }
}

@RestControllerAdvice
class SortErrorHandler {
    @ExceptionHandler(InvalidSortException.class)
    ProblemDetail invalidSort(InvalidSortException e) {
        return ProblemDetail.forStatusAndDetail(HttpStatus.BAD_REQUEST, e.getMessage());
    }
}

// GET /users?sort=fullName,asc   -> ORDER BY name ASC, id ASC
// GET /users?sort=password,asc   -> 400 Cannot sort by 'password'. Allowed: [email, fullName, joinDate, status]

public final class UserSpecifications {

    public static Specification<UserEntity> hasStatus(UserStatus status) {
        return (root, query, cb) -> status == null ? null : cb.equal(root.get("status"), status);
    }

    public static Specification<UserEntity> nameContains(String name) {
        return (root, query, cb) -> {
            if (name == null || name.isBlank()) return null;
            String escaped = name.toLowerCase()
                .replace("!", "!!").replace("%", "!%").replace("_", "!_");   // neutralise wildcards
            return cb.like(cb.lower(root.get("name")), "%" + escaped + "%", '!');
        };
    }
}

public interface UserRepository extends JpaRepository<UserEntity, Long>, JpaSpecificationExecutor<UserEntity> {}

@Service
public class UserService {

    private final UserRepository userRepo;
    public UserService(UserRepository userRepo) { this.userRepo = userRepo; }

    public Page<UserResponse> search(UserStatus status, String name, Pageable pageable) {
        Specification<UserEntity> spec = Specification.allOf(          // older versions: Specification.where(a).and(b)
            UserSpecifications.hasStatus(status),
            UserSpecifications.nameContains(name));
        return userRepo.findAll(spec, pageable).map(UserResponse::from);   // the Pageable carries the sort
    }
}

// Controller: SORT.apply(pageable) first, then userService.search(status, name, safePageable)

// Keyset scrolling: no offset, no count query
public interface UserRepository extends JpaRepository<UserEntity, Long> {
    Window<UserEntity> findFirst20ByStatusOrderByCreatedAtDescIdDesc(UserStatus status, ScrollPosition position);
}

Window<UserEntity> first = userRepo.findFirst20ByStatusOrderByCreatedAtDescIdDesc(ACTIVE, ScrollPosition.keyset());
if (first.hasNext()) {
    ScrollPosition next = first.positionAt(first.size() - 1);          // hand this back to the client as a cursor
    Window<UserEntity> second = userRepo.findFirst20ByStatusOrderByCreatedAtDescIdDesc(ACTIVE, next);
}

-- Index that serves "WHERE status = ? ORDER BY created_at DESC, id DESC"
CREATE INDEX idx_users_status_created ON users (status, created_at DESC, id DESC);

@WebMvcTest(UserController.class)
class UserSortTest {

    @Autowired MockMvc mvc;
    @MockitoBean UserService userService;

    @Test
    void renamedFieldIsMappedToEntityProperty() throws Exception {
        when(userService.findAll(any())).thenReturn(Page.empty());

        mvc.perform(get("/api/v1/users?sort=fullName,desc")).andExpect(status().isOk());

        ArgumentCaptor<Pageable> captor = ArgumentCaptor.forClass(Pageable.class);
        verify(userService).findAll(captor.capture());
        assertThat(captor.getValue().getSort())
            .isEqualTo(Sort.by(Sort.Order.desc("name"), Sort.Order.asc("id")));     // renamed + tie-breaker
    }

    @Test
    void unlistedFieldIsRejected() throws Exception {
        mvc.perform(get("/api/v1/users?sort=password,asc")).andExpect(status().isBadRequest());
    }
}