Sorting by parent entity using Specifications

Question

I’m dealing with an issue which to my understanding looks unsupported on Spring Data JPA.

I got a grid (using JqGrid plugin for jQuery) on the view which sends parameters to the server, they are parsed and then a dynamic query generated through Specifications is executed.

The issue comes when I want to order a column which doesn’t belong to the root entity.

Eg. Transaction, Card and Account are my entities and grid displays last4digits as a way for the user to identify the card. As you can imagine last4digits belongs to Card. I query transactions per account.

Using specifications I can filter by that attribute, joining tables and so on but sorting fails as findAll() implementation assumes properties from Sort class belongs to the root entity.

Code example:

JQGridRule panFirst6DigitsRule = FilterUtils.findSearchOrFilterRule(settings, Card_.panFirst6Digits.getName());
JQGridRule panLast4DigitsRule = FilterUtils.findSearchOrFilterRule(settings, Card_.panLast4Digits.getName());
if(panFirst6DigitsRule != null) {
    filterPan1 = TransactionSpecs.withPanFirst6Digits(panFirst6DigitsRule.getData(),
        panFirst6DigitsRule.getOp(), gridGroupOp);
}
if(panLast4DigitsRule != null) {
    filterPan2 = TransactionSpecs.withPanLast4Digits(panLast4DigitsRule.getData(),
        panLast4DigitsRule.getOp(), gridGroupOp);
}

Specification<Transaction> joinSpec = TransactionSpecs.withAccountId(account.getAccountId());
Specification<Transaction> activeSpec = BaseSpecs.withEntityStatus(true);
Page<Transaction> results = transactionRepository.findAll(
    Specifications.where(joinSpec).and(filterSpec).and(filterPan1).and(filterPan2).and(activeSpec), springPageable);

springPageable variable contains a Sort for last4Digits column generated this way*:

List<Order> sortOrders = new ArrayList<Order>();
Order sortOrder = new Order(Direction.ASC, "panLast4Digits");
sortOrders.add(sortOrder);
sort = new Sort(sortOrders);

*There are missing code parsing parameters and creating more Order objects

Does someone know how to implement that kind of sort over an attribute which belongs to a parent entity/class?

Thanks in advance

Version 1.4.3 for Spring-data-jpa and 4.2.8 for Hibernate

EDIT

Showing how Specification for panLast4Digits is generated

public static Specification<Transaction> withPanLast4Digits(final String panLast4Digits, final JQGridSearchOp op, final JQGridGroupOp whereOp) {
    Specification<Transaction> joinSpec = new Specification<Transaction>() {
        @Override
        public Predicate toPredicate(Root<Transaction> root, CriteriaQuery<?> query, CriteriaBuilder cb) {
            Join<Transaction, Card> join = joinCards(root, JoinType.INNER);
            return FilterUtils.buildPredicate(cb, join.get(Card_.panLast4Digits), op, panLast4Digits, null, whereOp);
        }
    };
    return joinSpec;
}

private static Join<Transaction, Card> joinCards(Root<Transaction> root, JoinType joinType) {
    Join<Transaction, Card> join = getJoin(root, Transaction_.parentCard, joinType);
    // only join if not already joined
    if (join == null) {
        join = root.join(Transaction_.parentCard, joinType);
    }
    return join;
}

protected static <C, T> Join<C, T> getJoin(Root<C> root, Attribute<? super C, T> attribute, JoinType joinType) {
    Set<Join<C, ?>> joins = root.getJoins();

    for (Join<C, ?> join : joins) {
        if (join.getAttribute().equals(attribute) && join.getJoinType().equals(joinType)) {
            return (Join<C, T>) join;
        }
    }

    return null;
}

Also I have updated to spring-data-jpa 1.6.0 and hibernate 4.3.5

Answer 1

the attribute for Sorting is "yourChildentity.attribute"

In your Case you can use the PagingAndSortingRepository this way:

let's assume you have two entities : an Account and a Card

@Entity
public class Account{
// Autogeneration and Ill just assume that your id is type long
private Long id;
@ManyToOne
@JoinColumn(name="CARD_ID")
private Card creditCard;
//getters and setters
}

@Entity
public class Card{
//Id and other attributes.
private String panLast4Digits;
//getters and Setters
}

Repository interface :

@Repository
public interface AccountRepository extends PagingAndSortingRepository<Account, Long>,
        JpaSpecificationExecutor<Account>{
}

Service Layer :

import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
public interface AccountService{
   //you can specify other arguments the one that you want to filter by
   Page<Account> filter(Pageable pageable);
}

Service Implementation:

 @Service
    public calss AccountServiceImpl implements AccountService{

   @Resource//or @Autowired
   private AccountRepository repository;

   @Override
   public Page<Account> filter(Pageable pageable){

    //Filter using Specifications if you have other arguments passed in the signature of the method.

   return repository.findAll(pageable);//if you have specifications than return   repository.findAll(yourspecification,pageable); 
}

Now the call to service throw an endpoint or a Controller: just a mthod to see how to sort throw child entity parameter :

import org.springframework.data.domain.Page;
import org.springframework.data.domain.PageRequest;
import org.springframework.data.domain.Sort.Direction;
// method
@Resource 
private AccountService service;

public Page<Account> consumeMyService(){
  // 0 : for Page 1
  // 12 for page size
  // Soting throw Child enntiy Account , by attribute panLast4Digits
  PageRequest pageable = new PageRequest(0,
                12, Direction.ASC, "mycard.panLast4Digits");
  Page<Account> service.filter(pageable);
}

You must register you beans by configuring Jpa:repositories for the repository interfaces, and context:component-scan for service implementation

this answer may be useful too.