Filter child object in Spring Data Query

Question

I have a following domain model:

Playlist -> List<PlaylistItem> -> Video

@Entity
class Playlist{
   // id, name, etc
   List<PlaylistItem> playlistItems;
   // getters and setters
}


@Entity
class PlaylistItem{
   // id, name, etc.
   Video video;
   // getters and setters
}


@Entity
class Video{
   // id, name, etc.
   boolean isDeleted;
   // getters and setters
}

And my repository:

public interface PlaylistRepository extends JpaRepository<Playlist, Long> {
   List<Playlist> findAll();
}

Now, how do I return a playlist with only existing videos, ie, if there are three videos in the database assigned to that playlist item and one of those videos has isDeleted set to true, then I need to get only two items instead.

Answer 1

All you have to do is declare this method on your PlaylistRepository interface:

List<Playlist> findByPlaylistItemsVideoIsDeleted(boolean isDeleted);

And call it like this:

playListRepository.findByPlaylistItemsVideoIsDeleted(false);

That will return all playlist with videos that are not removed.

Answer 2

Maksim, you could use the @query annotation like this :

public interface PlaylistRepository extends JpaRepository<Playlist, Long> {
   @Query("select playlist from Playlist playlist 
           fetch join playlist.playlistItems itens
           fetch join itens.video as video
           where video.isDeleted = false")
   List<Playlist> findAll();
}

Or even better way :

public interface PlaylistRepository extends JpaRepository<Playlist, Long> {
   @Query("select playlist from Playlist playlist 
           fetch join playlist.playlistItems itens
           fetch join itens.video as video
           where video.isDeleted = :hasVideo ")
   List<Playlist> findPlayList(@Param("hasVideo") boolean hasVideo);
}

Answer 3

You may have already resolved this issue, but I thought I would contribute this in hopes it might help you, or anyone else visiting this page.

Using Spring JPA Specifications, you would:

1. Enable your PlaylistRepository to use JPA Specifications
2. Write the Specification as a reusable method
3. Make use of the Specification as the query

Here are the details.

1. Implement JpaSpecificationExecutor

Update PlaylistRepository to implement JpaSpecificationExecutor. This adds find* methods that accept Specification<T> parameters to your PlaylistRepository.

public interface PlaylistRepository extends JpaRepository<Playlist, Long>, 
            JpaSpecificationExecutor<Playlist> {

}

2. Create the Specification

Create a class with a static method for use in creating a reusable Specification.

public final class PlaylistSpecifications {

    private PlaylistSpecifications() {}

    public static Specification<Playlist> hasExistingVideos() {
        return (root, query, cb) -> {
            return cb.equal(root.join("playlistItems").join("video")
                    .get("isDeleted"), false);
        };
    }
}

Using root.join (and subsequent joins) is similar to using JOIN in SQL. Here, we are joining on the fields of classes, instead of on columns of tables.

3. Issue the Query

I don't know how you plan to issue your query, but below is an example of how it could be done in a "service" class:

@Service
public class PlaylistService {

    @Autowired
    private PlaylistRepository playlistRepository;

    public List<Playlist> findPlaylistsWithExistingVideos() {

        Specification<Playlist> spec = PlaylistSpecifications.hasExistingVideos();
        return playlistRepository.findAll(spec);
    }
}

Hope this helps!

Answer 4

You can look into Spring Data Specifications. You use them by calling repository.findAll(s);

Specifications allow you add on arbitrary conditions to your query, including the filter you want to add. Another nice thing about Specifications is that they can be type-safe. See here:

http://docs.spring.io/spring-data/jpa/docs/current/reference/html/#specifications