Selecting dynamic values w/o `FROM` for later insertion


#1

I’m attempting to express this SQL using DSL:

SELECT $1, $2
WHERE EXISTS(
  SELECT * FROM projects
  WHERE id = $2 AND user_id = $3
)

My naive implementation gives me error:

diesel::select((&title, &project_id)).filter(diesel::select(exists(
  projects::table
    .filter(projects::id.eq(&project_id))
    .filter(projects::user_id.eq(&user_id)),
)));
# table names are a bit different from simplified example
the trait bound `diesel::query_builder::SelectStatement<(), diesel::query_builder::select_clause::SelectClause<diesel::expression::exists::Exists<diesel::query_builder::SelectStatement<db::schema::personal_projects::table, diesel::query_builder::select_clause::DefaultSelectClause, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::Eq<db::schema::personal_projects::columns::id, diesel::expression::bound::Bound<diesel::sql_types::Integer, &types::id::Id>>, diesel::expression::operators::Eq<db::schema::personal_projects::columns::user_id, diesel::expression::bound::Bound<diesel::sql_types::Integer, &types::id::Id>>>>>>>>: diesel::Expression` is not satisfied

the trait `diesel::Expression` is not implemented for `diesel::query_builder::SelectStatement<(), diesel::query_builder::select_clause::SelectClause<diesel::expression::exists::Exists<diesel::query_builder::SelectStatement<db::schema::personal_projects::table, diesel::query_builder::select_clause::DefaultSelectClause, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::Eq<db::schema::personal_projects::columns::id, diesel::expression::bound::Bound<diesel::sql_types::Integer, &types::id::Id>>, diesel::expression::operators::Eq<db::schema::personal_projects::columns::user_id, diesel::expression::bound::Bound<diesel::sql_types::Integer, &types::id::Id>>>>>>>>`

note: required because of the requirements on the impl of `diesel::query_dsl::filter_dsl::FilterDsl<diesel::query_builder::SelectStatement<(), diesel::query_builder::select_clause::SelectClause<diesel::expression::exists::Exists<diesel::query_builder::SelectStatement<db::schema::personal_projects::table, diesel::query_builder::select_clause::DefaultSelectClause, diesel::query_builder::distinct_clause::NoDistinctClause, diesel::query_builder::where_clause::WhereClause<diesel::expression::operators::And<diesel::expression::operators::Eq<db::schema::personal_projects::columns::id, diesel::expression::bound::Bound<diesel::sql_types::Integer, &types::id::Id>>, diesel::expression::operators::Eq<db::schema::personal_projects::columns::user_id, diesel::expression::bound::Bound<diesel::sql_types::Integer, &types::id::Id>>>>>>>>>` for `diesel::query_builder::SelectStatement<(), diesel::query_builder::select_clause::SelectClause<(&_, &_)>>`

Full context why I need this:

INSERT INTO data (title, project_id)
SELECT $1, $2
WHERE EXISTS(
  SELECT * FROM projects
  WHERE id = $2 AND user_id = $3
)
RETURNING *

By this query, I want to make sure provided project_id reference belongs to current user.

And here is DSL that “I wish it would work but it doesn’t”:

diesel::insert_into(data::table)
  .values(
    diesel::select((&title, &project_id)).filter(diesel::select(exists(
      projects::table
        .filter(projects::id.eq(&project_id))
        .filter(projects::user_id.eq(&user_id)),
    )))
  )
  .into_columns((data::title, data::project_id))
  .get_result::<Data>(&*self.pool.get());

#2

Writing queries without from clause is currently not possible using the DSL.
If you really need to do this you will need to use sql_query and write the raw sql query string there.


#3

Yeah, already fellback to sql_query, thanks!