英文:
Adding interval to timestamp. Producing @Query in Repository interface
问题
@Query("select job from Job job, Project proj, User u, Userprops props where job.status LIKE 'CANCELED' AND job.project = proj.id AND " +
"proj.user = u.id AND props.user = u.id AND NOW() < job.modifiedAt + props.removeThingsAfterDays * INTERVAL '1 day'")
List<XTMJobEntity> getAllJobsThatShouldBeDeletedFromDbBasedOnUserProps();
英文:
Database: Postgres,
Programming Technology: SpringBoot (Java 11)
I am trying to create @Query but the way I implemented it Hibernate won't produce it and I get the exception that the problem is the interval function.
I want to add some days to a modifiedAt column depending on UserProps and then decide whether Db should select it or not. I call the Postgres INTERVAL internal function but it doesn't seem to work
@Query ("select a job from Job job, Project proj, User u, Userprops props, where job.status LIKE 'CANCELED' AND job.project = proj.id AND \ n" +
"proj.user = u.id AND props.user = u.id AND NOW () <job.modifiedAt + props.removeThingsAfterDays * FUNCTION ('INTERVAL' '1 day')")
List <XTMJobEntity> getAllJobsThatShouldBeDeletedFromDbBasedOnUserProps ();
答案1
得分: 1
在Postgres中，interval 不是一个函数，而是一种数据类型。即使它是一个函数，你仍然不会将FUNCTION用作关键字。在Postgres中，查询将如下（仍然使用过时的连接语法）：
select a job
from job job
, project proj
, user u
, userprops props
where job.status = 'CANCELED'
and job.project = proj.id
and proj.user = u.id
and props.user = u.id
and now () < job.modifiedat + props.removethingsafterdays * '1 day'::interval;
---- OR
select a job
from job
, project proj
, user u
, userprops props
where job.status = 'CANCELED'
and job.project = proj.id
and proj.user = u.id
and props.user = u.id
and now () < job.modifiedat + props.removethingsafterdays * interval '1 day';
注意：在SQL语句中，如果没有通配符（%或_），LIKE与等号（=）等效。<br/>
<br/>
所以也许你的查询字符串变成了：
@Query("select a job from Job job, Project proj, User u, Userprops props, where job.status LIKE 'CANCELED' AND job.project = proj.id AND \ n" +
"proj.user = u.id AND props.user = u.id AND NOW () < job.modifiedAt + props.removeThingsAfterDays * INTERVAL '1 day'")
英文:
In Postgres interval is not a function, but a data type. Even if it were a though you still would not use FUNCTION as a key word. In Postgres the query would be (leaving the obsolete join syntax in place):
select a job
from job job
, project proj
, user u
, userprops props
where job.status = 'CANCELED'
and job.project = proj.id
and proj.user = u.id
and props.user = u.id
and now () <job.modifiedat + props.removethingsafterdays * '1 day'::interval;
---- OR
select a job
from job
, project proj
, user u
, userprops props
where job.status = 'CANCELED'
and job.project = proj.id
and proj.user = u.id
and props.user = u.id
and now () <job.modifiedat + props.removethingsafterdays * interval '1 day';
Note: LIKE in a SQL statement without wild cards (% or _) is the equivalent of equal operator (=). <br/>
<br/>
So perhaps your query string becomes:
@Query ("select a job from Job job, Project proj, User u, Userprops props, where job.status LIKE 'CANCELED' AND job.project = proj.id AND \ n" +
"proj.user = u.id AND props.user = u.id AND NOW () <job.modifiedAt + props.removeThingsAfterDays * INTERVAL '1 day'")
答案2
得分: 0
我问题的答案是以下查询：
@Query(value = "select job.* " +
"from jobs job INNER JOIN projects proj ON job.project_id = proj.id INNER JOIN users u ON proj.user_id = u.id " +
"INNER JOIN user_properties props ON props.user_id = u.id " +
"where job.status = 'CANCELLED' " +
"AND NOW() < job.modified_at + props.remove_cancelled_jobs_days * Interval '1days';", nativeQuery = true)
英文:
The answer for my problem is following query:
@Query(value = "select job.* " +
"from jobs job INNER JOIN projects proj ON job.project_id = proj.id INNER JOIN users u ON proj.user_id = u.id " +
"INNER JOIN user_properties props ON props.user_id = u.id " +
"where job.status = 'CANCELLED' " +
"AND NOW() < job.modified_at + props.remove_cancelled_jobs_days * Interval '1days';", nativeQuery = true)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论