Perform a raw sql query using sqlx query_as!()

[crushingCodes]

I am enjoying using loco rs. Any help with the following would be greatly appreciated.
My goal is to do this:

use sea_orm::DbConn;
use serde::{Deserialize, Serialize};
use loco_rs::model::ModelResult;
use loco_rs::prelude::*;

#[derive(Debug, Serialize, Deserialize, sqlx::FromRow)]
struct MyRow {
    id: i32,
    name: String,
}
pub async fn get_sqlx_data(c: &DbConn) -> ModelResult<Vec<MyData>> {
    let db: DatabaseConnection = c.into();
    let results = sqlx::query_as!(
        MyRow,
        r#"
        select id, name from my_table
        "#
    )
    .fetch_all(c)
    .await
    .map_err(|e| ModelError::Any(e.into()))?;
    Ok(results)
}

This line here seems to be the issue:

    let db: DatabaseConnection = c.into();

I get the following:

rustc: the trait bound `&sea_orm::DatabaseConnection: Into<sea_orm::DatabaseConnection>` is not satisfied required for `&sea_orm::DatabaseConnection` to implement `Into<sea_orm::DatabaseConnection>`

It looks like seaorm 1.1 has this new functionality:

Construct DatabaseConnection directly from sqlx::PgPool, sqlx::SqlitePool and sqlx::MySqlPool #2348

found here:
https://www.sea-ql.org/blog/#enhancements

But I am unable to upgrade seaorm due to a dependency conflict:

error: failed to select a version for `sea-orm`.
the package `libsqlite3-sys` links to the native library `sqlite3`, but it conflicts with a previous package which links to `sqlite3` as well:
package `libsqlite3-sys v0.30.1`
    ... which satisfies dependency `libsqlite3-sys = "^0.30.1"` of package `sqlx-sqlite v0.8.3`
    ... which satisfies dependency `sqlx-sqlite = "=0.8.3"` of package `sqlx v0.8.3`
    ... which satisfies dependency `sqlx = "^0.8.3"` of package `locoapi v0.1.0

[kaplanelad]

Why are you using sqlx directly and not using the ORM entities?

[crushingCodes]

thanks for replying, for the example above I am happy to use the orm, but i was hoping to use the postges function generate_series with a right join, so the query is a lot easier to write as raw sql. It might be possible to do it in seaorm, but I am not familar enough with it to write more complicated queries with

[crushingCodes]

the actual thing i want to achieve

let results = sqlx::query_as!(
        DailyCount,
        r#"
        WITH daily AS (
            SELECT
                created_at::date            AS date,
                COUNT(dictionary_item_id)   AS daily_count
            FROM attempt_trackers
            WHERE created_at >= $1::date
              AND created_at < $2::date
              AND user_id = $3
            GROUP BY 1
        ), calendar AS (
            SELECT gs::date AS date
            FROM   generate_series(
                       $1::timestamp,
                       $2::timestamp - INTERVAL '1 day',
                       INTERVAL  '1 day'
                   ) AS gs
        )
        SELECT
            c.date,
            COALESCE(d.daily_count, 0) AS daily_count
        FROM   calendar c
        LEFT   JOIN daily d USING (date)
        ORDER  BY c.date;
        "#,
        start_date,
        end_date,
        user_id
    )
    .fetch_all(c.into())
    .await
    .map_err(|e| ModelError::Any(e.into()))?;
    ```

[crushingCodes]

If anyone else stumbles across this, I found a workaround. It looks like Seaorm passes around a reference to the underlying pool, so I can just grab that, here is a minimal example:

    let db: &DatabaseConnection = db;
    let pool: PgPool = db.get_postgres_connection_pool().clone();