Look for help about raw sql

[samrayleung]

Hey, I have asked this question in gitter, you have given me some
suggestions. I followed your suggestion, but I still could not figure this issue
out by myself. So I just look for help here again.
Say, this is my Rust code, I want to count daily page-view in a month:

    pub fn count_daily_page_view(conn: &PgConnection) -> Vec<(NaiveDateTime, i32)> {
        sql::<(Date, Integer)>("SELECT date_trunc('day', access_time) ,
    count(*) FROM visitor_log\
            WHERE access_time> now() - interval '30 days'  GROUP BY 1  ORDER BY 1")
                .get_result(conn)
                .expect("Error executing raw SQL")
    }

I get such error, I have followed your suggestion to check Diesel doc, but I still could not
figure out how to implement the trait diesel::Queryable<(diesel::types::Date, diesel::types::Integer), diesel::pg::Pg>
for Vec<(chrono::NaiveDateTime,i32)>

the trait bound `std::vec::Vec<(chrono::NaiveDateTime, i32)>: diesel::Queryable<(diesel::types::Date, diesel::types::Integer), diesel::pg::Pg>` is not satisfied
  --> src/dal/models/visitor_log.rs:33:18
   |
33 |                 .get_result(conn)
   |                  ^^^^^^^^^^ the trait `diesel::Queryable<(diesel::types::Date, diesel::types::Integer), diesel::pg::Pg>` is not implemented for `std::vec::Vec<(chrono::NaiveDateTime, i32)>`

Could you help me out?

[weiznich]

There is a type miss match between the returned SQL types and the return types of your function.
In detail your sql statement seems to return Date whereas the function is returning NaiveDateTime. Date could be converted to NaiveDate but not to NaiveDateTime. The corresponding sql type for the second is Timestamp

[samrayleung]

Even though I have changed the return type from Date to Timestamp, I still get error as below:

error[E0277]: the trait bound `std::vec::Vec<(chrono::NaiveDateTime, i32)>: diesel::Queryable<(diesel::types::Timestamp, diesel::types::Integer), diesel::pg::Pg>` is not satisfied
  --> src/dal/models/visitor_log.rs:34:18
   |
34 |                 .get_result(conn)
   |                  ^^^^^^^^^^ the trait `diesel::Queryable<(diesel::types::Timestamp, diesel::types::Integer), diesel::pg::Pg>` is not implemented for `std::vec::Vec<(chrono::NaiveDateTime, i32)>`
   |
   = help: the following implementations were found:
             <std::vec::Vec<T> as diesel::Queryable<diesel::types::Array<ST>, diesel::pg::Pg>>
             <std::vec::Vec<u8> as diesel::Queryable<diesel::types::Binary, DB>>
   = note: required because of the requirements on the impl of `diesel::LoadQuery<diesel::pg::PgConnection, std::vec::Vec<(chrono::NaiveDateTime, i32)>>` for `diesel::expression::SqlLiteral<(diesel::types::Timestamp, diesel::types::Integer)>`

error: aborting due to previous error

error: Could not compile `blog`.

[weiznich]

get_result will only load one row, so the return type must be (NaiveDateTime, i32) instead of Vec<(NaiveDateTime, i32)>. If you want to return all values use load or get_results instead.

[samrayleung]

I get another error as below:

thread '<unnamed>' panicked at 'Received more than 4 bytes decoding i32. Was a BigInteger expression accidentally identified as Integer?', /home/samray/.cargo/registry/src/mirrors.ustc.edu.cn-61ef6e0cd06fb9b8/diesel-0.14.1/src/types/impls/integers.rs:31:8
note: Run with `RUST_BACKTRACE=1` for a backtrace.

and after I change Integer to BigInt, everything works fine. Thanks soooo much for help me out, you save my day !

    pub fn count_daily_page_view(conn: &PgConnection) -> Vec<(NaiveDateTime, i64)> {
        sql::<(Timestamp, BigInt)>("SELECT date_trunc('day', access_time) ,
    count(*) FROM visitor_log
            WHERE access_time> now() - interval '30 days'  GROUP BY 1  ORDER BY 1")
                .get_results(conn)
                .expect("Error executing raw SQL")
    }