diesel Memo
I've been using diesel for work, but sometimes I find it difficult to search for information in the documentation or realize how to implement certain features.
So, I'll write down what I've learned as I go along. I plan to add more content as I think of it.
Executing Raw SQL
diesel does not support window functions or subqueries.
Therefore, I've been implementing pagination by directly manipulating SQL.
Although it's good to write frequently used code in this way, it can be a hassle when you just want to get it working. That's why it's convenient to know how to handle raw SQL.
To execute raw SQL, you can use sql_query, which performs by-name mapping when loading.
The struct that performs the mapping should derive QueryableByName.
#[derive(QueryableByName, Serialize, Debug)]
pub struct LastUpdate {
#[sql_type = "Datetime"]
pub dt: chrono::NaiveDateTime,
}
use diesel::sql_query;
use diesel::sql_types::Integer;
sql_query(
r#"
SELECT x.*, updated_at as dt
FROM hoge
LIMIT ?
"#
)
.bind::<Integer, _>(5)
.load::<(Hoge, Rank)>(&*conn)
.expect("cannot execute ....")
Associations
When retrieving related tables, you can use derive(Associations) and #[belongs_to(XXX)] on the struct, and then specify the ID using belonging_to.
This performs filtering using WHERE IN.
SELECT * FROM hoge
WHERE IN (1,2,3....)
Therefore, the ID must be determined at the time of SQL execution, and subqueries cannot be used.
If you're concerned about performance due to the need to execute two queries, you can try using raw SQL.
Grouped By
Unlike SQL's group_by, grouped_by is a function that groups data that has already been loaded.
It links using foreign keys.
By zipping afterwards, you can get a structure that combines parent and child data arrays.
For more information, see below.
Retrieving a Single Record
You can simply use find.
Load the struct with derive(Identifiable).
Select Restrictions when Using GROUP BY
There is a stronger constraint than MySQL 8.0's engine, where only columns that appear in GROUP BY can be specified in SELECT in the QueryBuilder.
Therefore, you need to write the following code using raw SQL (A and B have a 1:N relationship).
SELECT A.*
FROM B
LEFT JOIN A on B.a_id = A.id
GROUP BY B.A_id
Useful URLs
- diesel-rs/diesel - Github: Official
- Crate diesel: For when you need to check the API.
- Diesel Tag - κeen's Happy Hacκing Blog: I always appreciate your help.
- Using diesel at work: I forgot to mention this earlier.
Conclusion
diesel is fun.
I'll add more content as I think of it.
