@Query and Pageable Issue

[kraymondksc]

Hi all,

I'm trying to use Pageable in one of my named queries. I'm specifying the page, size, and sort. When the query is executed it looks like pagination is applied but the sort is ignored.

This is running against an HSQLDB database version 2.2.0 and I'm using Spring Data version 1.1.2

My named query is this:

SELECT
    ae
FROM
    AssetEntity ae
JOIN
    ae.workspace w
LEFT JOIN
    ae.viewPoints v
WHERE
    ae.workspace = :workspace
AND ae.name LIKE :name
OR  v.name LIKE :viewPointName

My repository method is defined as:

@Query(name = AssetEntity.FIND_BY_WORKSPACE_AND_NAME_LIKE_OR_VIEWPOINTS_NAME_LIKE)
List<AssetEntity> findByWorkspaceAndNameLikeOrViewPointsNameLike(@Param("workspace") WorkspaceEntity workspace, @Param("name") String name, @Param("viewPointName") String viewPointName, Pageable pageable);

And the meat of my unit test:

Order order = new Order(Direction.DESC, "name");
PageRequest pageable = new PageRequest(0, 10, new Sort(order));
Collection<AssetEntity> assets = repository.findByWorkspaceAndNameLikeOrViewPointsNameLike(WORKSPACE, filter, filter, pageable);

I've removed columns to shorten the query but this is what is being generated:

SELECT
    *
FROM
    "RULE" assetentit0_
INNER JOIN
    "WORKSPACE" workspacee1_
ON
    assetentit0_."WORKSPACE_ID"=workspacee1_."ID"
LEFT OUTER JOIN
    "RULE_VIEW_POINT" viewpoints2_
ON
    assetentit0_."ID"=viewpoints2_."RULE_ID"
LEFT OUTER JOIN
    "VIEW_POINT" viewpointe3_
ON
    viewpoints2_."VIEW_POINT_ID"=viewpointe3_."ID"
WHERE
    assetentit0_."DTYPE"='AssetEntity'
AND (
        assetentit0_."WORKSPACE_ID"=1000
    AND (
            assetentit0_."NAME" LIKE 'foo')
    OR  viewpointe3_."NAME" LIKE 'foo') limit 10

Just wondering if anyone had any ideas on why the sort wouldn't be applied?

[kraymondksc]

I guess I should pay closer attention to the logs. Looks like this isn't supported:

WARN  o.s.d.j.repository.query.NamedQuery - Finder method public abstract java.util.List AssetRepository.findByWorkspaceAndNameLikeOrViewPointsNameLike(WorkspaceEntity,java.lang.String,java.lang.String,org.springframework.data.domain.Pageable) is backed by a NamedQuery but contains a Pageable parameter! Sorting delivered via this Pageable will not be applied!