英文:
is it possible to update limit 1 when using rust diesel
问题
我想在使用 PostgreSQL 13 时使用 Rust Diesel diesel = { version = "2.0.4", features = ["postgres","64-column-tables","chrono","serde_json"] } 更新表格行的标志，代码如下：
pub fn pick_task() -> CvGen{
use crate::model::diesel::cv::cv_schema::cv_gen::dsl::*;
let predicate = crate::model::diesel::cv::cv_schema::cv_gen::gen_status.eq(0);
let result = diesel::update(cv_gen.filter(predicate))
.set(
gen_status.eq(1)
)
.get_result::<CvGen>(&mut get_connection())
.expect("unable to update ren result");
return result;
}
现在我遇到一个问题，即使有许多行与过滤条件匹配，我只想更新一行。我应该怎么做？我已经阅读了官方文档 https://docs.diesel.rs/master/diesel/dsl/fn.update.html 和 https://diesel.rs/guides/all-about-updates.html，但没有找到如何实现更新行数限制的线索。在使用 Rust Diesel 时是否有可能限制更新行数？
英文:
I want to update a table rows flag in PostgreSQL 13 when using rust diesel diesel = { version = "2.0.4", features = ["postgres","64-column-tables","chrono","serde_json"] }, this is the code looks like:
pub fn pick_task() -> CvGen{
use crate::model::diesel::cv::cv_schema::cv_gen::dsl::*;
let predicate = crate::model::diesel::cv::cv_schema::cv_gen::gen_status.eq(0);
let result = diesel::update(cv_gen.filter(predicate))
.set(
gen_status.eq(1)
)
.get_result::<CvGen>(&mut get_connection())
.expect("unable to update ren result");
return result;
}
Now I am facing a issue that I just want to update one row althrough there is many rows match the filter. what should I to? I have read the official document from https://docs.diesel.rs/master/diesel/dsl/fn.update.html and https://diesel.rs/guides/all-about-updates.html but did not found any clue about how to implement the update rows limit. is it possible to update with limit when using rust diesel?
答案1
得分: 1
Here's the translated content without the code parts:
"不太好，部分原因是因为 SQL 并不直接支持这种方式。
Diesel 只生成 SQL 语句，如果从 SQL How to Update only first Row 中得到的答案可信，唯一实现这一目标的方式是使用带有 LIMIT 1 的子查询。
Diesel 支持子查询，但遗憾的是，如果子查询使用与父查询相同的表，否则这大致相当于以下 SQL 语句：
UPDATE cv_gen
SET gen_status = 0
WHERE id = (SELECT id FROM cv_gen WHERE gen_status = 1 LIMIT 1);
实际上，这样做的方式是自己编写 SQL，使用 sql_query 或分两步进行：一次查询以获取要更新的元素，另一次更新该元素。"
英文:
Not nicely, partially because SQL doesn't make it nice.
Diesel only produces SQL statements and if the answers from SQL How to Update only first Row are to be believed, the only way to achieve that goal is by using a subquery with LIMIT 1.
Diesel does support subqueries but unfortunately not if the subquery uses a same table as the parent, otherwise this would be roughly the equivalent:
UPDATE cv_gen
SET gen_status = 0
WHERE id = (SELECT id FROM cv_gen WHERE gen_status = 1 LIMIT 1);
// DOES NOT WORK
let result = diesel::update(cv_gen)
.filter(id.nullable().eq(cv_gen.filter(gen_status.eq(0)).select(id).single_value()))
.set(gen_status.eq(1))
.get_result::<CvGen>(&mut get_connection())
.expect("unable to update ren result");
The way to actually do this then would be to write the SQL yourself with sql_query or do this in two steps: one query to get an element to update, one to update that element.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论