如何在HQL查询中检查NULL或无效 [英] How to check NULL or not valid in HQL query

问题描述

我有两个不同的表工件和分类。我想检索没有分类的工件列表。分类表包含ID（PK），Artifact_Id（外键），Active（0/1），而工件表包含ID（PK），Name。每个工件可以有很多分类。我想在没有分类时返回工件列表（即分类不包含该工件，或者工件已分类，但所有实例都不处于活动状态（0））

I have two different table artifact and classification. I want to retrieve list of artifacts who dont have classification. The classification table contains ID(PK), Artifact_Id(foreign key), Active(0/1) while the artifact table contains ID(PK),Name. Each artifact can have many classification. I want to return a list of artifacts when there is no classification for it (i.e. classification does not contain that artifact or when the artifact has classification but all of instances are not active(0))

例如

Artifact
Id Name
1  xyz
2  pqr
3  abc

Classification
Id Artifact_id active
a1 1            0 
a2 1            0
a3 1            0
a4 3            0
a5 3            1

在上述情况下，工件1和2没有分类，具有分类，因为一个实例是活动的（1）。所以我想返回1和2.我不知道如何查询这个。
这是我试过的（不是太多，但是错了）

In the above case the artifact 1 and 2 has no classification but 3 has classification since one instance is active(1). So I want to return 1 and 2. I am not sure how to query this. This is what I have tried (not too much though and is wrong)

def list = findAll("from artifact as a full outer join classification as c on a.id=c.artifact_id where active == 0 OR c.aritfact_id is NULL,[max:limit, offset:startPos])

我也在sql中尝试了一些东西

I also tried something in sql

select *  from ARTIFACT full outer join classification  on artifact.id = classification.ARTIFACT_ID where sum(classification.active) == 0 OR classification.aritfact_id is NULL

推荐答案

在SQL中，您可以使用

In SQL you could do this with

SELECT * FROM Artifact WHERE Id NOT IN
SELECT Artifact_id FROM Classification WHERE active = 1）;

SELECT * FROM Artifact WHERE Id NOT IN ( SELECT Artifact_id FROM Classification WHERE active = 1);

基本上，你得到一个具有活动分类的所有工件ID的列表，即在该列表中，即无效分类或根本没有分类。

Basically, you get a list of all Artifact Ids that have an active classification and then get only the Artifacts that are not in that list, i.e. inactive classification or no classification at all.

这篇关于如何在HQL查询中检查NULL或无效的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！