Nullable fields with Option

[tcpdcp22]

Hello. I'm trying to figure out how to allow insertion/retrieval of null values ​​from a table with *RETURNING state. I don't use the "DEFAULT" block in migrations and would like to somehow get "NULL" values ​​in the output - is this possible or should I use "NOT NULL" or "DEFAULT" argument when creating tables?

version:
diesel = { version = "2.2.4", features = ["postgres", "r2d2", "chrono", "serde_json"] }

diesel::table! {
    use diesel::sql_types::*;

    entity (id) {
        id -> Int4,
        name -> Varchar,
        duration -> Nullable<Float8>,
        keywords -> Nullable<Jsonb>,
        start -> Float8,
        stop -> Nullable<Float8>
    }
}

#[derive(Insertable)]
#[diesel(table_name = schema::entity)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct NewEntity<'a> {
    pub name: &'a String,
    pub duration: &'a Option<f64>,
    pub keywords: &'a Option<Value>,
    pub start: &'a f64,
    pub stop: &'a Option<f64>

#[derive(Queryable, Selectable, Debug, Clone)]
#[diesel(table_name = schema::entity)]
#[diesel(check_for_backend(diesel::pg::Pg))]
pub struct SelectEntity {
    pub id: i32,
    pub name: String,
    pub duration: Option<f64>,
    pub keywords: Option<Value>,
    pub start: f64,
    pub stop: Option<f64>,
}

problem is there:

diesel::insert_into(entity)
        .values(new_entity_instance)
        .get_result::<SelectEntity>(conn)

.get_result method gives me a warning:

Trait `AppearsOnTable<NoFromClause>` is not implemented for `Option<f64>` [E0277] 
 Inspection info: Reports the use of types that do not implement the necessary traits. 
Trait `AppearsOnTable<NoFromClause>` is not implemented for `Option<Value>` [E0277] 
 Inspection info: Reports the use of types that do not implement the necessary traits. 
Trait `QueryFragment<Pg, NotSpecialized>` is not implemented for `f64` [E0277] 
 Inspection info: Reports the use of types that do not implement the necessary traits. 
Trait `QueryFragment<Pg, NotSpecialized>` is not implemented for `Value` [E0277] 
 Inspection info: Reports the use of types that do not implement the necessary traits.

is this because Rust can't handle a null/none/nil type properly? If I remove the "optional" block from fields in NewEntity structure - I don't get any warnings/errors in .get_result method

2. is it possible to use window/aggregate functions directly in queries or should i write the query inline and execute it via driver directly? how can i execute the query via diesel?
   For example:

WITH customer_orders AS (
    SELECT
        o.customer_id,
        c.name,
        c.city,
        COUNT(o.order_id) AS total_orders,
        SUM(o.amount) AS total_amount,
        AVG(o.amount) AS average_order_amount
    FROM
        orders o
    JOIN
        customers c ON o.customer_id = c.customer_id
    GROUP BY
        o.customer_id, c.name, c.city
),
ranked_customers AS (
    SELECT
        customer_id,
        name,
        city,
        total_orders,
        total_amount,
        average_order_amount,
        RANK() OVER (ORDER BY total_amount DESC) AS rank_by_total_amount
    FROM
        customer_orders
)
SELECT
    customer_id,
    name,
    city,
    total_orders,
    total_amount,
    average_order_amount,
    rank_by_total_amount
FROM
    ranked_customers
ORDER BY
    rank_by_total_amount;

or

WITH RECURSIVE employee_hierarchy AS (

    SELECT
        employee_id,
        name,
        manager_id,
        1 AS level
    FROM
        employees
    WHERE
        employee_id = 3 

    UNION ALL

    SELECT
        e.employee_id,
        e.name,
        e.manager_id,
        eh.level + 1 AS level
    FROM
        employees e
    INNER JOIN
        employee_hierarchy eh ON e.employee_id = eh.manager_id
)
SELECT
    employee_id,
    name,
    manager_id,
    level
FROM
    employee_hierarchy
ORDER BY
    level;

3. Can Diesel ORM create N+1 problems? - could you please give examples where and how to resolve them (other methods/inline query?)

[weiznich]

Hello. I'm trying to figure out how to allow insertion/retrieval of null values ​​from a table with *RETURNING state. I don't use the "DEFAULT" block in migrations and would like to somehow get "NULL" values ​​in the output - is this possible or should I use "NOT NULL" or "DEFAULT" argument when creating tables?

That error occurs because you use types like &'a Option<f64> in your struct. These are not really meaningful, therefore diesel expects either Option<f64> or Option<&f64> there.

is it possible to use window/aggregate functions directly in queries or should i write the query inline and execute it via driver directly? how can i execute the query via diesel?
For example:

Questions about whether something is "possible" are almost always answerable with: "Yes". In this case yes that's possible, but you need to bring your own DSL extension for window functions.

Can Diesel ORM create N+1 problems? - could you please give examples where and how to resolve them (other methods/inline query?)

Diesel itself does not have a known way to run into N+1 query problems. You can create N+1 problems as with every database driver by executing queries in an explicit loop, but that depends then on your code. It's up to you to resolve that.

[tcpdcp22]

Could you please explain how I could implement this DSL extension for window functions? Maybe there are examples of step-by-step implementations or do I need to know Rust deeply enough and be familiar with the internal implementation of diesel?

Could you please provide an example of using the driver directly through diesel and say execute the query "SELECT * FROM waq WHERE id = 1"

[weiznich]

Could you please explain how I could implement this DSL extension for window functions? Maybe there are examples of step-by-step implementations or do I need to know Rust deeply enough and be familiar with the internal implementation of diesel?

See the documentation

Could you please provide an example of using the driver directly through diesel and say execute the query "SELECT * FROM waq WHERE id = 1"

Again see the documentation

[tcpdcp22]

Thank you