CODEWITHSUNDEEP
javamongodbspring-data-mongodb
user13929082
user13929082

Reputation:

Case-insensitive sort using Query class in Spring Data Mongodb

I am using this type of sorting, and I want to be case insensitive.

            Query query = new Query();
            query.with(new Sort(new Order(Sort.Direction.ASC,"title").ignoreCase()));
            return db.find(query, Video.class);

I tried this query but I don't get any results back.

Imports used:

import org.springframework.data.domain.Sort;
import org.springframework.data.domain.Sort.Direction;
import org.springframework.data.domain.Sort.Order;
import org.springframework.data.mongodb.core.query.Criteria;
import org.springframework.data.mongodb.core.query.Query;

For example, if I have this type of titles: "Inception","BlackList","adore","123", "city","desperadoS"

The order should be: "123","adore","BlackList","city","desperadoS","Inception"

If i use it this way

        Query query = new Query();
        query.with(new Sort(Sort.Direction.ASC,"title"));
        return db.find(query, Video.class);

It returns

"123","BlackList","Inception","adore","city","desperadoS"

Spring-data-mongodb version 1.9.2

Upvotes: 1

Views: 2401

Answers (2)

Prasanth Rajendran
Prasanth Rajendran

Reputation: 5512

Another approach to use Collation with @Query annotation to get the expected sorting order of "123","adore","BlackList","city","desperadoS","Inception"

Document

@Document(collection = "video")
@Data
public class Video {

    @Id
    private String id;
    private String title;

    public Video(String title) {
        this.title = title;
    }
}

Repository

@Repository
public interface VideoRepository extends MongoRepository<Video, String> {

    @Query(collation = "en", value = "{}")
    List<Video> getAllSortedVideos(Sort sort);

}

Integration Test class to assert the changes

@ActiveProfiles("test")
@SpringBootTest(classes = DemoApplication.class, 
        webEnvironment = SpringBootTest.WebEnvironment.RANDOM_PORT)
class VideoRepositoryITest {

    @Autowired
    private VideoRepository videoRepository;

    @Test
    void getAllSortedVideos() {
        List<String> expectedSystemNamesInOrder = Arrays.asList("123", "adore", "BlackList",
                                                    "city", "desperadoS", "Inception");
        //breaking the order for fun
        Set<String> expectedSystemNamesSet = new HashSet<>(expectedSystemNamesInOrder);
        //saving the videos of each title
        expectedSystemNamesSet.stream().map(Video::new)
                .forEach(videoRepository::save);
        //fetching sorted Videos by title
        List<Video> videos = videoRepository.getAllSortedVideos(Sort.by(Direction.ASC, "title"));
        //fetching sorted Video tiles to assert
        List<String> titles = videos.stream().map(Video::getTitle).collect(Collectors.toList());
        //asserting the result video title order with the expected order
        for (int i = 0; i < titles.size(); i++) {
            String actualTitle = titles.get(i);
            String expectedTitle = expectedSystemNamesInOrder.get(i);
            //Test case will fail if the retrieved title order doesn't match with expected order
            Assertions.assertEquals(expectedTitle, actualTitle);
        }
    }

}

Using org.springframework.data:spring-data-mongodb:3.0.4.RELEASE

Upvotes: 2

Rolaman
Rolaman

Reputation: 33

Use collation to sort ignoring cases. Example is here

You will get an IllegalArgumentException, when use new Order(Sort.Direction.ASC,"title").ignoreCase())

In your case:

Query query = new Query().with(Sort.by(new Sort.Order(Sort.Direction.ASC, "title")));
query.collation(Collation.of("en").strength(Collation.ComparisonLevel.secondary()));
return mongoTemplate.find(query, Video.class);

Upvotes: 0

Related Questions