Search code examples
javasqlspringspring-bootspring-data

Query a list with JPARepository

I need to get the exact values who match with my query but with my query it returns more values (who also contains my list) and return even two value of the same rows: the query that I do is this:

 List<Archive> findAllByIdentifierAndChannelsChannelNameIn(String identifier, List<String> channel);

my model class is this:

public class Archive {
    @Id
    @Column(name = "ARCHIVE_ID")
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    @Generated(GenerationTime.ALWAYS)
    private Long archiveId;

    @Column(name = "IDENTIFIER")
    private String identifier;

    @OneToMany(cascade = CascadeType.ALL, mappedBy = "archive")
    @JsonManagedReference
    private Set<Channel> channels;

}
public class Channel{
     @Id
        @GeneratedValue(strategy = GenerationType.IDENTITY)
        @Generated(GenerationTime.ALWAYS)
        private Long channelId;
    
        @ManyToOne
        @JoinColumn(name = "archive_id")
        @JsonBackReference
        private Archive archive;
        @Column(name = "Channel_Name")
        private String channelName;
    }

The problem is when I pass these body:

{
"identifier": "NGLCRS97D12G866L",
"channels": ["Sky news","Rai 4"]
}

It give me back this:

"archiveId": 24,
        "identifier": "NGLCRS97D12G866L",
        "channels": [
            {
                "channelId": 20,
                "channelName": "Sky news"
            }
        ]
    },
    {
        "archiveId": 9,
        "identifier": "NGLCRS97D12G866L",
        "channels": [
            {
                "channelId": 2,
                "channelName": "Rai 4"
            },
            {
                "channelId": 40,
                "channelName": "Sky news"
            }
        ]
    },
    {
        "archiveId": 9,
        "identifier": "NGLCRS97D12G866L",
        "channels": [
            {
                "channelId": 2,
                "channelName": "Rai 4"
            },
            {
                "channelId": 40,
                "channelName": "Sky news"
            }
        ]
    },
    {
        "archiveId": 25,
        "identifier": "NGLCRS97D12G866L",
        "channels": [
            {
                "channelId": 41,
                "channelName": "Sky news"
            },
            {
                "channelId": 1,
                "channelName": "Boing"
            }
        ]
    },
    {
        "archiveId": 8,
        "identifier": "NGLCRS97D12G866L",
        "portal": "PORTALE_TITOLARI",
        "channels": [
            {
                "channelId": 39,
                "channelName": "Sky news"
            }
        ]
    }

As you can see it will give me back 2 value og the same row (archiveId: 9) but I need to get the exact value when I pass more channels to match what I want beacuse I need to use in a delete. thanks to all. Even if I'm going to use the native query I don't know how to write the right query to get just the values that I need it Query on SQL

Solution

  • Solved By adding this to my query:

     @Query(value = "Select * from tbl_archive A where a.archive_id IN (select A.ARCHIVE_ID FROM tbl_archive A JOIN tbl_channel C ON(C.ARCHIVE_ID =A.ARCHIVE_ID)" +
        "WHERE a.identifier= :Identifier AND c.channel_name IN :channels group by A.ARCHIVE_ID" +
        " having count(c.ARCHIVE_ID) = :channelSize)", nativeQuery = true)

    if somebody has a better solution please let me know