3

I have a custom repository and this is the method that I used to find Transporters based on a certain filter, sorted by whatever column is picked (Either organization number, transporter, address line 1, postal code, and postal name)

    @Override
    public Page<Transporter> getTransportersPaged(Map<String, Object> filters, int first, int pageSize, String sortBy,
        boolean descending) throws DatabaseConnectionException
    {
        logger.info("Get paged transporters. First {}, pageSize {}, sortBy {}, filters {}", first, pageSize, sortBy, filters);
        List<Sort.Order> orders = new ArrayList<>();
        if (sortBy != null)
        {
            orders.add(new Sort.Order(descending ? Sort.Direction.DESC : Sort.Direction.ASC, sortBy).ignoreCase());
        }
        orders.add(new Sort.Order(Sort.Direction.ASC, "orgNumber"));
        Specification<Transporter> specification;
        try
        {
            specification = buildSearch(filters);
            if(sortBy!=null && sortBy.contains("address"))
            {
                specification = specification.and(TransporterSpecifications.sortByAddressField());
            }
        }
        catch (ApplicationNotFoundException e)
        {
            logger.debug("Application not found for license number {}", e);
            return null;
        }
        return transporterRepository.findAll(specification, PageRequest.of(first / pageSize, pageSize, Sort.by(orders)));
    }

I get ORA-01791 when I try to sort by address, so I made sortByAddressField() in my TransporterSpecification:

    public static Specification<Transporter> sortByAddressField()
    {
        return new JoinableSpecification<Transporter>()
        {
            @Override
            public Predicate toPredicate(Root<Transporter> root, CriteriaQuery<?> cq, CriteriaBuilder cb)
            {
                if (cq.getResultType() != Long.class && cq.getResultType() != long.class) {
                    Fetch<Address, Transporter> address = root.fetch("address", JoinType.LEFT);
                    Fetch<Postoffice, Address> postofficeFetch = address.fetch("postoffice", JoinType.LEFT);
                } else {
                    Join<Address, Transporter> address = root.join("address", JoinType.LEFT);
                    Join<Postoffice, Address> postoffice = address.join("postoffice", JoinType.LEFT);
                }
                return null;
            }
        };    
    }

Now, I can sort by address line 1 and postal code, but I still struggle to make sorting by postal name work. I still get ORA-01791 whenever I sort by postal name.

Transporter has many-to-one relationship with Address, and Address has many-to-one relationship with Postoffice.

How can I explicitly tell Spring to select fields of Postoffice? Thanks!

João Dias
  • 16,277
  • 6
  • 33
  • 45
N.M.
  • 41
  • 6
  • I don't think there is a way to force Hibernate to select specific redundant fields from joined tables in a query. The only workaround that I can think of is using Criteria API directly, selecting all the relevant fields using `criteriaQuery.multiselect(...)`. You would need to create a custom repository fragment, handlng sorting and paging manually, though – crizzis Oct 28 '20 at 19:48
  • I have tried multiselect btw, didn't work. – N.M. Oct 28 '20 at 19:57
  • How do you handle sorting and paging manually? – N.M. Oct 28 '20 at 19:57
  • What I meant was that when interacting with Criteria API directly, you would have to use `Query.setFirstResult()` and `Query.setMaxResults()`, instead of relying on Spring's `Pageable` – crizzis Oct 28 '20 at 20:01

0 Answers0