Raw SQL statement crashes / trait bound FromSqlRow not satisfied

[Boscop]

This query works in my postgres console but crashes with diesel:

pub fn search(search_terms: &str) -> Result<Vec<Filter>, error::MyError> {
	use diesel::prelude::*;
	use models::schema::filters::dsl::*;

	use diesel::expression::sql_literal::sql;
	let query = sql(&format!(r#"
WITH ranking AS (
    SELECT score, f
    FROM (
        SELECT
            ts_rank(
                to_tsvector('english', filters.name) ||
                to_tsvector('english', filters.description) ||
                to_tsvector('english', array_to_string(filters.tags, ' '))
            , plainto_tsquery('{}'), 32) AS score,
            filters AS f
        FROM filters
    ) s
    WHERE score > 0
    ORDER BY score DESC
)
SELECT f FROM ranking;
	"#, search_terms));
	query.get_results::<models::filter::Filter>(&*database::connection().get().unwrap()).map_err(|e| e.into())
}

thread '<unnamed>' panicked at 'Received more than 8 bytes decoding i64. Was an
expression of a different type misidentified as BigInteger?', C:\Users\me\.cargo
\registry\src\github.com-1ecc6299db9ec823\diesel-0.8.2\src\types\impls\integers.
rs:47
stack backtrace:
   0:     0x7ff7798ccde8 - std::panicking::default_hook::{{closure}}
                        at C:\bot\slave\nightly-dist-rustc-win-msvc-64\build\src
\libstd\panicking.rs:356
   1:     0x7ff7798cc2f4 - std::panicking::default_hook
                        at C:\bot\slave\nightly-dist-rustc-win-msvc-64\build\src
\libstd\panicking.rs:367
   2:     0x7ff7798cfcad - std::panicking::rust_panic_with_hook
                        at C:\bot\slave\nightly-dist-rustc-win-msvc-64\build\src
\libstd\panicking.rs:555
   3:     0x7ff778d0ce17 - std::panicking::begin_panic<&str>
                        at C:\bot\slave\nightly-dist-rustc-win-msvc-64\build\src
\libstd\panicking.rs:517
   4:     0x7ff778ec933a - diesel::types::impls::integers::{{impl}}::from_sql<di
esel::pg::backend::Pg>

When specifying the type with let query = sql::<Filter>(... it doesn't compile:

error[E0277]: the trait bound `(i64, i64, chrono::NaiveDateTime, chrono::NaiveDateTime, std::string::String, std::string::String, std::string::String, std::string::String, std::vec::Vec<std::string::String>): diesel::types::FromSqlRow<models::filter::Filter, _>` is not satisfied
   --> D:\projects\yakshaver\src\models\filter.rs:165:8
    |
165 | 	query.get_results::<models::filter::Filter>(&*database::connection().get().unwrap()).map_err(|e| e.into())
    | 	      ^^^^^^^^^^^ the trait `diesel::types::FromSqlRow<models::filter::Filter, _>` is not implemented for `(i64, i64, chrono::NaiveDateTime, chrono::NaiveDateTime, std::string::String, std::string::String, std::string::String, std::string::String, std::vec::Vec<std::string::String>)`
    |
    = help: the following implementations were found:
    = help:   <(A, B, C, D, E, F, G, H, I) as diesel::types::FromSqlRow<(SA, SB, SC, SD, SE, SF, SG, SH, SI), DB>>
    = note: required because of the requirements on the impl of `diesel::Queryable<models::filter::Filter, _>` for `models::filter::Filter`

error[E0277]: the trait bound `diesel::pg::Pg: diesel::types::HasSqlType<models::filter::Filter>` is not satisfied
   --> D:\projects\yakshaver\src\models\filter.rs:165:8
    |
165 | 	query.get_results::<models::filter::Filter>(&*database::connection().get().unwrap()).map_err(|e| e.into())
    | 	      ^^^^^^^^^^^ the trait `diesel::types::HasSqlType<models::filter::Filter>` is not implemented for `diesel::pg::Pg`
    |
    = help: the following implementations were found:
    = help:   <diesel::pg::Pg as diesel::types::HasSqlType<diesel::types::Array<T>>>
    = help:   <diesel::pg::Pg as diesel::types::HasSqlType<diesel::types::Date>>
    = help:   <diesel::pg::Pg as diesel::types::HasSqlType<diesel::types::Time>>
    = help:   <diesel::pg::Pg as diesel::types::HasSqlType<diesel::types::Timestamp>>
    = help: and 12 others

error: aborting due to 2 previous errors

There seems to be a missing parameter? But if I write sql::<Filter, Pg> it complains about 2 parameters when 1 is required.

---

I have another question: How to prevent SQL injection with the search_terms?

[Boscop]

With the help of @killercup I figured out what the problem was (thanks a lot!):
At the end of the query, there is only one column 'f' with all the columns as tuple members. In the postgres cmd line I thought it looked like a normal row -.-
Btw, this is my ORM/schema:

#[derive(Queryable, Identifiable, Clone, Associations, Debug)]
#[belongs_to(User)]
pub struct Filter {
	pub id: i64,
	pub user_id: i64,
	pub created_at: NaiveDateTime,
	pub updated_at: NaiveDateTime,
	pub name: String,
	pub description: String
	pub code: String,
	pub params: String,
	pub tags: Vec<String>,
}

@killercup suggested I name all the rows explicitly to pull them through to the outer scope instead of using the catchall '*' (I also tried filters.* AS f, but it's a syntax error). This works:

	let query = sql(&format!(r#"
WITH ranking AS (
    SELECT *
    FROM (
        SELECT
            ts_rank(
                to_tsvector('english', filters.name) ||
                to_tsvector('english', filters.description) ||
                to_tsvector('english', array_to_string(filters.tags, ' '))
            , plainto_tsquery('{}'), 32) AS score,
            id,
            user_id,
            created_at,
            updated_at,
            name,
            description,
            code,
            params,
            tags
        FROM filters
    ) s
    WHERE score > 0
    ORDER BY score DESC
)
SELECT 
            id,
            user_id,
            created_at,
            updated_at,
            name,
            description,
            code,
            params,
            tags
FROM ranking;
	"#, search_terms));
	query.get_results::<models::filter::Filter>(&*database::connection().get().unwrap()).map_err(|e| e.into())

But it's unelegant because:

1. column names are duplicated
2. query has to stay in sync with the schema and this is not enforced at compile time

So my remaining questions are:

• Is there a way to avoid this and write it in a way such that I don't have to change the query when adding new columns to filter?
• I could get the original to work if there is a way to splat/unpack a tuple into columns. Is there a function for this in postgresql?
• Still the SQL injection issue..

[sgrif]

Is there a way to avoid this and write it in a way such that I don't have to change the query when adding new columns to filter?

Well if you want to select a subset of the fields in the table, you'll need a custom select clause. So not really.

I could get the original to work if there is a way to splat/unpack a tuple into columns. Is there a function for this in postgresql?

I don't think so, but I'm also not sure I fully understand the goal of doing that.

Still the SQL injection issue..

The solution there is to use the query builder instead of a SQL string. Unless I'm missing something, your query is (other than the select clause) identical to:

SELECT
    ts_rank(
        to_tsvector('english', filters.name) ||
        to_tsvector('english', filters.description) ||
        to_tsvector('english', array_to_string(filters.tags, ' '))
    , plainto_tsquery('{}'), 32) AS score,
    id,
    user_id,
    created_at,
    updated_at,
    name,
    description,
    code,
    params,
    tags
FROM filters
WHERE score > 0
ORDER BY score DESC

If that's the case, it's also identical to:

SELECT
    id,
    user_id,
    created_at,
    updated_at,
    name,
    description,
    code,
    params,
    tags
FROM filters
WHERE ts_rank(
        to_tsvector('english', filters.name) ||
        to_tsvector('english', filters.description) ||
        to_tsvector('english', array_to_string(filters.tags, ' '))
    , plainto_tsquery('{}'), 32) > 0
ORDER BY ts_rank(
        to_tsvector('english', filters.name) ||
        to_tsvector('english', filters.description) ||
        to_tsvector('english', array_to_string(filters.tags, ' '))
    , plainto_tsquery('{}'), 32) DESC

Which can be written in the query builder as:

sql_function!(array_to_string, array_to_string_t, (a: Array<Text>, b: Text) -> Text);
sql_function!(ts_rank, ts_rank_t, (a: TsVector, b: TsQuery, c: Integer) -> Float);

let search = ts_rank(
    to_tsvector("english", filters::name).concat(
    to_tsvector("english", filters::description)).concat(
    to_tsvector("english", array_to_string(filters::tags, " "))),
    plainto_tsquery(your_variable), 32).aliased("search");
filters.with(query).with(search)
  .filter(search.gt(0))
  .order(search.desc())
  .select(your_columns_here)

This exact use case is actually in the documentation here

[Boscop]

Thanks!