Diesel : how to implement generic functions for CRUD operations on any table type?

I'm writing generic functions for CRUD operations on the Sqlite as the db using DIESEL as the ORM.

Have implemented generic functions for CREATE, INSERT OR UPDATE but for 'find_all_records' for any table passed to the function is creating a lot of problem in terms of traits bounds.

see below the code snippet:

pub fn find_all_by_query(&self, query: use crate::db::schema::poll_question::BoxedQuery<diesel::sqlite::Sqlite>) -> Vec<PollQuestion>
  {
     let db_connection = self.pool.get().unwrap();
     let collection = query.load::<PollQuestion>(&db_connection).expect("Error in getting all records");
     collection
  }

PollQuestion is a struct mapped to a poll_question table in Sqlite

I want to make this function generic to take other table as the input

but struggling a lot to resove the trait bounds needed for this, it is asking more trait bounds:

pub fn find_all_by_query<T>(&self, query: T)->Vec<T>
     where
           T: diesel::Table +
           diesel::query_builder::AsQuery,<T as diesel::query_builder::AsQuery>::Query: QueryId
  {
     let db_connection = self.pool.get().unwrap();
     let collection = query.load::<T>(&db_connection).expect("Error in getting all records");
     collection
  } 

Please help.

Please format your code according to this guide lines as this makes it much easy to understand your question.

Additionally the following information are required to answer your question:

  • Your database schema
  • An example showing how you want to use that code
  • Definitions of all relevant types
  • Imports for everything in scope
  • Potential error messages you've hit while trying to implement this
2 Likes

Hi,

Please find the schema and the generic functions I'm implementing for read, have done successfully generic functions for create, insert and update but facing problem for read generic functions:

#[derive(Debug, Deserialize, Serialize, Queryable, Insertable, AsChangeset, Clone)]
#[table_name = "poll_question"]
pub struct PollQuestion{
   id: i32,
   topic: Option<String>,
   question: Option<String>,
   created_on: Option<NaiveDateTime>,
   created_by: Option<String>,
   status: Option<String>,
   option_type: Option<String>,
   options: Option<String>,
   published_on: Option<NaiveDateTime>,
   closed_on: Option<NaiveDateTime>,
}


table! {
    poll_question(id) {
      id -> Integer,
      topic -> Nullable<Text>,
      question -> Nullable<Text>,
      created_on -> Nullable<Timestamp>,
      created_by -> Nullable<Text>,
      status -> Nullable<Text>,
      option_type -> Nullable<Text>,
      options -> Nullable<Text>,
      published_on -> Nullable<Timestamp>,
      closed_on -> Nullable<Timestamp>,
    }
}


pub fn all_poll_questions(){
	use super::super::db::schema::poll_question::dsl::*;
	
	let mut query = poll_question::table().into_boxed();

	if let Some(id_val) = id_param {
		query = query.filter(id.eq(id_val.parse::<i32>().unwrap()));
	}

	if let Some(topic_val) = topic_param {
		query = query.filter(topic.like(topic_val));
	}

	if let Some(question_val) = question_param {
		query = query.filter(question.like(question_val));
	}
	
	let results:Vec<PollQuestion> = find_all_by_query::<PollQuestion, poll_question::table>(query);
}

I want to implement a generic function to get all records from the table 'PollQuestion'

pub fn find_all_by_query<'a, C, T>(&self, query: T)->Vec<C>
      where
            C: HasTable<Table=T>,
            T: diesel::Table + diesel::query_dsl::methods::LoadQuery<diesel::sqlite::SqliteConnection,C>,
   {
      let db_connection = self.pool.get().unwrap();
      let collection = query.load::<C>(&db_connection).expect("Error in getting all records");
      //println!("collection from function, find_all_by_query-> {:?}",collection);
      collection
   }
}

While compiling it ask too many trait bounds for the above functions.

Please find the schema and the generic functions I'm implementing for read, have done successfully generic functions for create, insert and update but facing problem for read generic functions:

It seems like that you try to implement a model based strategy on top of diesel. Please be aware that diesel is explicitly designed in a different way, which means you will run into "problems" again and again.

While compiling it ask too many trait bounds for the above functions.

If the compiler requests that trait bounds are missing there is really no simple way to just skip them. They are required, otherwise it will not work. Generally speaking it seems for me that you just try to replace a single function call (.load()) with another function call. While this is possible it does require a relatively large number of trait bounds to be written down amount. I wouldn't advice anyone to choose this route.

Now back to answering this question:

  • Your function takes an argument of the type T where T should implement Table. From that trait name alone it should be clear that this won't be the case for complex queries.
  • Essentially you want to call .load. Therefore it's a good idea to see where it's coming from. Diesel documentation gives you RunQueryDsl::load as matching trait function. For completeness let's just inline the signature here:
fn load<U>(self, conn: &Conn) -> QueryResult<Vec<U>> where
    Self: LoadQuery<Conn, U>, 

Now let's identify the different arguments there. self corresponds to your query, Conn is a generic type coming from the trait definition itself. It represents the connection type. Additionally there is the generic type U which represents the return type. Now there is a single where predicate: Self: LoadQuery<Conn, U>, which means that your query type needs to implement LoadQuery<YourConnectionType, YourResultType>. That can be directly used to adjust your function signature:

pub fn find_all_by_query<C, T>(&self, query: T)->Vec<C>
      where
            T: diesel::query_dsl::methods::LoadQuery<diesel::sqlite::SqliteConnection,C>,
   {
      let db_connection = self.pool.get().unwrap();
      let collection = query.load::<C>(&db_connection).expect("Error in getting all records");
      // uncommenting this line additionally requires a `C: Debug` bound, but that's
      // unrelated to diesel
      //println!("collection from function, find_all_by_query-> {:?}",collection);
      collection
   }
}

It didn't solve the problem I'm facing.

I'm trying passing a boxedquery developed on some table to a generic function to run the query. That generic function I have to develop.

for example:

       let mut query = poll_question::table().into_boxed();

        if let Some(id_val) = id_param {
            query = query.filter(id.eq(id_val.parse::<i32>().unwrap()));
        }

        let results = find_all_by_query::<PollQuestion, poll_question::table>(query);
pub fn find_all_by_query<C, T>(&self, query: T)->Vec<C>
      where
          T: diesel::query_dsl::methods::LoadQuery<diesel::sqlite::SqliteConnection,C> + diesel::query_builder::QueryId
          + diesel::query_builder::Query,
      C: diesel::Queryable<<T as diesel::query_builder::AsQuery>::SqlType, diesel::sqlite::Sqlite>,<T as diesel::query_builder::AsQuery>::Query: QueryId,
   {

      let db_connection = self.pool.get().unwrap();
      let collection = query.load::<C>(&db_connection).expect("Error in getting all records");
      collection
   }

Again: Please always add all error messages as emitted by the compiler. Otherwise it's not possible to help you any further here.

Please find below the errors genererated by the compiler:

query = query.filter(id.eq(id_val.parse::<i32>().unwrap()));
    |                        ^^^^^^ multiple `filter` found
    |
    = note: candidate #1 is defined in an impl of the trait `FilterDsl` for the type `diesel::query_builder::BoxedSelectStatement<'a, ST, QS, DB>`
    = note: candidate #2 is defined in an impl of the trait `QueryDsl` for the type `diesel::query_builder::BoxedSelectStatement<'a, ST, QS, DB>`
note: candidate #3 is defined in the trait `Iterator`
   --> C:\Users\Anjali\.rustup\toolchains\stable-x86_64-pc-windows-msvc\lib/rustlib/src/rust\library\core\src\iter\traits\iterator.rs:841:5
    |
841 | /     fn filter<P>(self, predicate: P) -> Filter<Self, P>
842 | |     where
843 | |         Self: Sized,
844 | |         P: FnMut(&Self::Item) -> bool,
    | |______________________________________^
help: disambiguate the associated function for candidate #1
    |
146 |          query = FilterDsl::filter(query, id.eq(id_val.parse::<i32>().unwrap()));
    |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
help: disambiguate the associated function for candidate #2
    |
146 |          query = QueryDsl::filter(query, id.eq(id_val.parse::<i32>().unwrap()));
    |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
help: disambiguate the associated function for candidate #3
    |
146 |          query = Iterator::filter(query, id.eq(id_val.parse::<i32>().unwrap()));
    |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

error[E0034]: multiple applicable items in scope
    |
150 |          query = query.filter(topic.like(topic_val));
    |                        ^^^^^^ multiple `filter` found
    |
    = note: candidate #1 is defined in an impl of the trait `FilterDsl` for the type `diesel::query_builder::BoxedSelectStatement<'a, ST, QS, DB>`
    = note: candidate #2 is defined in an impl of the trait `QueryDsl` for the type `diesel::query_builder::BoxedSelectStatement<'a, ST, QS, DB>`
note: candidate #3 is defined in the trait `Iterator`
   --> C:\Users\Anjali\.rustup\toolchains\stable-x86_64-pc-windows-msvc\lib/rustlib/src/rust\library\core\src\iter\traits\iterator.rs:841:5
    |
841 | /     fn filter<P>(self, predicate: P) -> Filter<Self, P>
842 | |     where
843 | |         Self: Sized,
844 | |         P: FnMut(&Self::Item) -> bool,
    | |______________________________________^
help: disambiguate the associated function for candidate #1
    |
150 |          query = FilterDsl::filter(query, topic.like(topic_val));
    |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
help: disambiguate the associated function for candidate #2
    |
150 |          query = QueryDsl::filter(query, topic.like(topic_val));
    |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
help: disambiguate the associated function for candidate #3
    |
150 |          query = Iterator::filter(query, topic.like(topic_val));
    |                  ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

error[E0223]: ambiguous associated type
 
    |
136 |         let results = DBMANAGER.find_all_by_query::<PollQuestion,poll_question::table>(query);
    |                                                                  ^^^^^^^^^^^^^^^^^^^^ help: use fully-qualified syntax: `<schema::poll_question::table as Tra
it>::table

This error is not related to the generic function at all, but to imports that are currently in scope. None of your code snippets contain any imports, therefore it's really hard to tell you what's wrong there. Generally speaking you likely want to remove an import to FilterDsl.
That all written: This is likely my last comment here, at least as long as you don't provide a complete minimal example that includes your complete current state. I don't want to start each answer with: Now this information is missing.

I have 2 files, the contents of the files pasted at the end:

1). poll.rs
2). dbmanager.rs

function 'all_poll_question' from the poll.rs calls a generic function 'find_all_by_query' in the file dbmanager.rs

poll.rs

use std::collections::HashMap;

use super::super::DBMANAGER;
use super::super::db::models::PollQuestion;
use super::super::db::schema::poll_question;

use diesel::QueryDsl;

pub struct PollQuestionService{}
impl PollQuestionService{
    pub fn new()->Self{PollQuestionService{}}

    pub fn create_poll_question(&self){
    }

    pub fn update_poll_question(&self, poll_question: PollQuestion)->bool{
        DBMANAGER.insert_or_update::<PollQuestion, poll_question::table>(poll_question)
    }


    pub fn all_poll_question(&self, pagination:HashMap<&str, &str>, id_param:Option<String>, topic_param:Option<String>,
                             question_param:Option<String>, status_param:Option<String>, created_by_param:Option<String>,
                             date_from_param:Option<String>, date_to_param:Option<String>){

        use super::super::db::schema::poll_question::dsl::*;
        use crate::diesel::associations::HasTable;
        use crate::diesel::ExpressionMethods;
        use crate::diesel::TextExpressionMethods;

        let mut query = poll_question::table().into_boxed::<diesel::sqlite::Sqlite>();

        if let Some(id_val) = id_param {
            query = query.filter(id.eq(id_val.parse::<i32>().unwrap()));
        }


        if let Some(topic_val) = topic_param {
            query = query.filter(topic.like(topic_val));
        }

        if let Some(question_val) = question_param {
            query = query.filter(question.like(question_val));
        }

        if let Some(created_by_val) = created_by_param{
            query = query.filter(created_by.like(created_by_val));
        }

        if let Some(status_val) = status_param{
            query = query.filter(status.eq(status_val));
        }

        if let Some(from_dt_val) = date_from_param{
            query = query.filter(created_on.ge(from_dt_val));
        }

        if let Some(to_dt_val) = date_to_param{
            query = query.filter(created_on.le(to_dt_val));
        }

        let rows:i64 = if let Some(rows) = pagination.get("rows"){
            let r:i64 = rows.parse::<i32>().unwrap().into();
            query = query.limit(r);
            r
        }else{
            1
        };

        if let Some(page) = pagination.get("page"){
            let page:i64 = page.parse::<i32>().unwrap().into();
            let offset = (page-1) * rows;
            query = query.offset(offset);
        }

        let sord = if let Some(sort) = pagination.get("sord"){
            sort
        }else{
            "asc"
        };

        if let Some(order) = pagination.get("sidx"){
            match order{
                &"id" =>{
                    if sord == "desc"{
                        query = query.order(id.desc());
                    }else{
                        query = query.order(id.asc());
                    }
                }
                &"topic" =>{
                    if sord == "desc"{
                        query = query.order(topic.desc());
                    }else{
                        query = query.order(topic.asc());
                    }
                }
                &"question" =>{
                    if sord == "desc"{
                        query = query.order(question.desc());
                    }else{
                        query = query.order(question.asc());
                    }
                }
                &"status" =>{
                    if sord == "desc"{
                        query = query.order(status.desc());
                    }else{
                        query = query.order(status.asc());
                    }
                }
                &"created_by" =>{
                    if sord == "desc"{
                        query = query.order(created_by.desc());
                    }else{
                        query = query.order(created_by.asc());
                    }
                }
                &"created_on" =>{
                    if sord == "desc"{
                        query = query.order(created_on.desc());
                    }else{
                        query = query.order(created_on.asc());
                    }
                }
                _ =>{
                    if sord == "desc"{
                        query = query.order(id.desc());
                    }else{
                        query = query.order(id.asc());
                    }
                }
            };
        }
        let results = DBMANAGER.find_all_by_query::<PollQuestion,poll_question::table>(query);
    }

}

dbmanager.rs

use diesel::{prelude::*,sql_query,};
use diesel::sql_types::Integer;
use diesel::RunQueryDsl;
use diesel::r2d2::{self, ConnectionManager};
use diesel::SqliteConnection;
use diesel::associations::HasTable;
use diesel::query_dsl::{QueryDsl, LoadQuery};
use std::collections::HashMap;
use diesel::result::Error;
use diesel::table;
use super::models::*;
use serde_json::Value;
use super::super::util::utility::PollCache;

use serde::Serialize;
use diesel::sqlite::{Sqlite, SqliteType};

use diesel::query_builder::{InsertStatement, Query, QueryFragment, QueryBuilder};
use diesel::query_dsl::methods::{ExecuteDsl, FilterDsl, BoxedDsl};

use super::super::SERVER_CONFIG;
use diesel::helper_types::IntoBoxed;

use diesel::types::HasSqlType;
use super::schema::poll_question::dsl::poll_question;
use diesel::query_builder::QueryId;
use crate::db::schema::poll_question::BoxedQuery;

//use crate::db::schema::user::BoxedQuery;
//use crate::db::schema::poll_question::BoxedQuery;

pub type Pool = r2d2::Pool<ConnectionManager<SqliteConnection>>;

pub struct DbManager{
   pool: Pool,
}

impl DbManager {
   pub fn new() -> DbManager {
      let dbpath = SERVER_CONFIG.get_ConfigValue("database.url");
      let database_pool: Pool = Pool::builder().build(ConnectionManager::new(dbpath)).unwrap();
      DbManager {
         pool: database_pool,
      }
   }

  

   pub fn all_users(&self) -> Vec<User> {
      use super::schema::user::dsl::*;
      let db_connection = self.pool.get().unwrap();
      let users_collection = user.load::<User>(&db_connection).expect("Error loading users");
      users_collection
   }

   pub fn count_all_users(&self) -> i64 {
      use super::schema::user::dsl::*;
      let db_connection = self.pool.get().unwrap();
      let users_count: i64 = user.count().get_result(&db_connection).expect("Error while count on users");
      users_count
   }


   pub fn create<C, T>(&self, model: C) -> bool
      where
          T: diesel::associations::HasTable,
          <T::Table as diesel::QuerySource>::FromClause:
          diesel::query_builder::QueryFragment<diesel::sqlite::Sqlite>,
          C: diesel::Insertable<T::Table>,
          C::Values: diesel::insertable::CanInsertInSingleQuery<diesel::sqlite::Sqlite>
          + diesel::query_builder::QueryFragment<diesel::sqlite::Sqlite>,
   {
      let db_connection = self.pool.get().unwrap();
      match diesel::insert_into(T::table()).values(model).execute(&db_connection) {
         Ok(count) => {
            println!("entity {:?} created", count);
            true
         }
         Err(error) => {
            println!("error encountered while creating {:?} entity", error);
            false
         }
      }
   }


   pub fn insert_or_update<C, T>(&self, model: C) -> bool
      where
          T: diesel::associations::HasTable,
          <T::Table as diesel::QuerySource>::FromClause:
          diesel::query_builder::QueryFragment<diesel::sqlite::Sqlite>,
          C: diesel::Insertable<T::Table>,
          C::Values: diesel::insertable::CanInsertInSingleQuery<diesel::sqlite::Sqlite>
          + diesel::query_builder::QueryFragment<diesel::sqlite::Sqlite>,
   {
      let db_connection = self.pool.get().unwrap();
      match db_connection.transaction::<_, Error, _>(|| {
         diesel::replace_into(T::table()).values(model).execute(&db_connection)
      }) {
         Ok(_) => {
            println!("saved !!!");
            true
         }
         Err(error) => {
            println!("not saved: {:?} ", error);
            false
         }
      }
   }
  
   pub fn find_all_by_query<C, T>(&self, query: T)->Vec<C>
      where
          T: diesel::query_dsl::methods::LoadQuery<diesel::sqlite::SqliteConnection,C>,
   {
      let db_connection = self.pool.get().unwrap();
      let collection = query.load::<C>(&db_connection).expect("Error in getting all records");

      collection
   }

}

I'm unable to implement the generic function ' find_all_by_query' accepting a boxed query as the argument for any kind of table.

Using your code I cannot reproduce the error message given above.
That said it seems like your call to DbManager::find_all_by_query has a wrong generic argument.

 let results = DBMANAGER.find_all_by_query::<PollQuestion,poll_question::table>(query);

The second generic type is not a table (as repeatably stated above), but a query. The probably easiest thing to do here is to just use _ there and let the compiler figure out the correct type on it's own.

This made working without any compilation error.

Thanks a lot for your help. I have no words how to appreciate your patience with such a long Q/A trail.

Again, thanks a lot. I really appreciate your patience and efforts.

Hi Weiznich,

Can you help me with implementation of a generic delete functions for records of a table:

delete(entity)

OR

delete(table_name, pk_column_name, pk_value)

This topic was automatically closed 90 days after the last reply. We invite you to open a new topic if you have further questions or comments.