Issue
I am getting following error after starting my spring application:
Caused by: java.lang.IllegalArgumentException: org.hibernate.QueryException: cannot dereference scalar collection element: metadata_descriptor_id [SELECT d FROM com.org.entity.documents.Document d INNER JOIN d.documentMetadata md ON md.document_id = d.id WHERE (md.metadata_descriptor_id.id NOT IN (:documentMetadataId))]
Here is my Document class:
@Entity
@Table(name = "documents")
public class Document {
@Id
@GeneratedValue(strategy = GenerationType.IDENTITY)
@Column(name = "id")
private long id;
@Column(name = "name", nullable = false, unique = true)
private String name;
@Column(name = "archived", nullable = false)
private boolean archived;
@Column(name = "creation_date", nullable = false)
private Instant creationDate;
@Column(name = "file_text_processing_status", nullable = false)
@Enumerated(EnumType.STRING)
private FileTextProcessingStatus fileTextProcessingStatus;
@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinColumn(name = "template_file_id", referencedColumnName = "id")
private TemplateFile templateFile;
@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinColumn(name = "state_descriptor_id", referencedColumnName = "id")
private StateDescriptor stateDescriptor;
@ManyToOne(fetch = FetchType.LAZY, cascade = CascadeType.ALL)
@JoinColumn(name = "user_id", referencedColumnName = "id")
private UserEntity user;
@ElementCollection(targetClass = String.class)
@Column(name = "tag", nullable = false)
@CollectionTable(name = "document_tags", joinColumns = @JoinColumn(name = "document_id"))
private Set<String> tags = new HashSet<>();
@ElementCollection
@CollectionTable(name = "document_metadata", joinColumns = {
@JoinColumn(name = "document_id", referencedColumnName = "id")})
@MapKeyJoinColumn(name = "metadata_descriptor_id", referencedColumnName = "id")
@Column(name = "value", nullable = false)
private Map<MetadataDescriptor, String> documentMetadata = new HashMap<>();
@OneToMany(mappedBy = "document", cascade = CascadeType.ALL, orphanRemoval = true)
private Set<DocumentFileVersion> documentVersions = new HashSet<>();`enter code here`
And here is Repository method that fails:
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query(value = "SELECT d FROM Document d INNER JOIN d.documentMetadata md ON md.document_id = d.id"
+ " WHERE (md.metadata_descriptor_id NOT IN (:documentMetadataId))")
List<Document> findAllByDocumentRequiredMetadataNotSet(long documentMetadataId);
I have seen an answer for similar question here: getting Caused by: org.hibernate.QueryException: cannot dereference scalar collection element: roles
But it's not helpful in my case. I will be very glad for any suggestions.
Solution
The type of md
is String
this is why you see an error. What you need is something like this:
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query(value = "SELECT d FROM Document d INNER JOIN d.documentMetadata md WHERE (KEY(md).id NOT IN (:documentMetadataId))")
List<Document> findAllByDocumentRequiredMetadataNotSet(long documentMetadataId);
You also had a ON clause which is no necessary/wrong. I think you should read into HQL/JPQL first as you don't seem to understand that a join of an association, will add the join predicate in the SQL automatically.
Note though, that the syntax KEY(..).id
is only supported in newer Hibernate versions, so you might have to update.
Answered By - Christian Beikov