Как сформировать запрос в MYSQL содержащий поиск в коллекции?

Question

[johnmohnachev]

DAO

@Query("select campaign from CampaignEntity campaign " +
            "left join fetch campaign.clientOwner " +
            "left join fetch campaign.attachedClient " +
            "left join fetch campaign.clients " +
            "where " +
            "(campaign.type = 'event' AND campaign.attachedClient.name like ?1) OR " +
            "(campaign.type = 'system' AND campaign.clientsRelation = 'all') OR " +
            "(campaign.type = 'system' AND campaign.clientsRelation = 'some' AND campaign.clients.name like ?1) OR " +
            "(campaign.type = 'system' AND campaign.clientsRelation = 'all_except' AND campaign.clients.name not like ?1)")
    CampaignEntity findByClientName(String name);

Entity

@Data
@Entity
@Table(name = "Campaign")
public class CampaignEntity implements Serializable, DependsOnClient {

    @Id
    @Column
    @GeneratedValue(strategy = GenerationType.IDENTITY)
    private Long id;

    @ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "attached_client_id")
    private ClientEntity attachedClient;

    @Column(name = "type", nullable = false)
    @Convert(converter = CampaignTypeJPAConverter.class)
    private CampaignType type;

    @Column(name = "clients_relation")
    @Convert(converter = CampaignClientsRelationJPAConverter.class)
    private CampaignClientsRelation clientsRelation;

    @ManyToMany(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
    @JoinTable(name = "System_Campaign_To_Client",
        joinColumns = @JoinColumn(name = "campaign_id", nullable = false),
        inverseJoinColumns = @JoinColumn(name = "client_id", nullable = false))
    private Set<ClientEntity> clients;

}

При инициализации приложения вылетают исключения

org.hibernate.QueryException: illegal attempt to dereference collection [campaignen0_.id.clients] with element property reference [name]

Помогите пожалуйста :)

Answer 1

[johnmohnachev]

Ребят, все огонь, методом тыка выявил верный синтаксис :)

@Query("select campaign from CampaignEntity campaign " +
            "left join fetch campaign.attachedClient attachedClient " +
            "left join fetch campaign.clients clients " +
            "where " +
            "(campaign.type = 'event' AND lower(attachedClient.name) like CONCAT('%',lower(?1),'%')) OR " +
            "(campaign.type = 'system' AND campaign.clientsRelation = 'all') OR " +
            "(campaign.type = 'system' AND campaign.clientsRelation = 'some' AND lower(clients.name) like CONCAT('%',lower(?1),'%')) OR " +
            "(campaign.type = 'system' AND campaign.clientsRelation = 'all_except' AND lower(clients.name) not like CONCAT('%',lower(?1),'%'))")
    Set<CampaignEntity> findByClientName(String name);