distinct_on() multiple columns support

[Ticki84]

Let's take a pretty basic SQL example to query latest objects from table tab by date with a distinct on two columns:

SELECT DISTINCT ON (field1, field2) *
    FROM tab
    ORDER BY field1, field2, date DESC;

Which is easily translated into this code using the Diesel ORM:

use super::schema::tab;

tab::table
    .distinct_on((tab::field1, tab::field2))
    .order((tab::field1, tab::field2, tab::date.desc()));

But yet, only single value (i.e. one column) are supported as distinct_on() param. It would be great to support multiple columns in distinct_on().

In this simple example, it isn't a big deal because you could simply write the SQL query. But for big queries, you could use these to filter out results in a same request. Another workaround is to create a view of the first SQL select, then create a table for this view using this trick, but the amount of work required is high for such a simple request.

[weiznich]

This is essentially a duplicate of #3020

That written: I would accept a PR for this, as long as it includes tests + compile tests. For me personally this is not a priority issue, so I likely won't work on this in the next time.

[lochetti]

I would like to try to help here with this evolution.
That said, I have never contributed to the diesel project source code and I really don't know if this problem would be a good first problem to solve (and if it is quite difficult or not).
Do you have any opinions or suggestions?

[weiznich]

I did not investigate in depth yet what's required to enable the presented usecase. That remains an open question and that's likely the main work that is required here. If someone is willing to do that work that would be great. Otherwise this is a pretty well contained thing in diesels code. To solve it you likely require a good understanding of how traits work in rust and how to write generic impls.

From a short look at the code I can say that these (

diesel/diesel/src/pg/query_builder/distinct_on.rs

Lines 18 to 52 in fa33e38

	impl<T> ValidOrderingForDistinct<DistinctOnClause<T>> for NoOrderClause {}
	impl<T> ValidOrderingForDistinct<DistinctOnClause<T>> for OrderClause<(T,)> {}
	impl<T> ValidOrderingForDistinct<DistinctOnClause<T>> for OrderClause<T>
	where
	T: Expression,
	T::SqlType: SingleValue,
	{
	}
	impl<T> ValidOrderingForDistinct<DistinctOnClause<T>> for OrderClause<crate::helper_types::Desc<T>>
	where
	T: Expression,
	T::SqlType: SingleValue,
	{
	}
	impl<T> ValidOrderingForDistinct<DistinctOnClause<T>> for OrderClause<crate::helper_types::Asc<T>>
	where
	T: Expression,
	T::SqlType: SingleValue,
	{
	}
	impl<T> ValidOrderingForDistinct<DistinctOnClause<T>>
	for OrderClause<(crate::helper_types::Desc<T>,)>
	where
	T: Expression,
	T::SqlType: SingleValue,
	{
	}
	impl<T> ValidOrderingForDistinct<DistinctOnClause<T>>
	for OrderClause<(crate::helper_types::Asc<T>,)>
	where
	T: Expression,
	T::SqlType: SingleValue,
	{
	}

) impls are not general enough to cover the case presented above. That can be likely fixed by adding additional impls there or by generalizing existing impls. I would likely want to see additional test cases for the newly added cases here (

diesel/diesel_tests/tests/distinct.rs

Lines 1 to 100 in fa33e38

	use super::schema::*;
	use diesel::*;
	#[test]
	fn simple_distinct() {
	use crate::schema::users::dsl::*;
	let connection = &mut connection();
	diesel::sql_query("INSERT INTO users (name) VALUES ('Sean'), ('Sean'), ('Tess')")
	.execute(connection)
	.unwrap();
	let source = users.select(name).distinct().order(name);
	let expected_data = vec!["Sean".to_string(), "Tess".to_string()];
	let data: Vec<String> = source.load(connection).unwrap();
	assert_eq!(expected_data, data);
	}
	#[cfg(feature = "postgres")]
	#[test]
	fn distinct_on() {
	use crate::schema::users::dsl::*;
	let connection = &mut connection();
	diesel::sql_query(
	"INSERT INTO users (name, hair_color) VALUES ('Sean', 'black'), ('Sean', NULL), ('Tess', NULL), ('Tess', NULL)",
	).execute(connection)
	.unwrap();
	let source = users
	.select((name, hair_color))
	.order(name)
	.distinct_on(name);
	let mut expected_data = vec![
	("Sean".to_string(), Some("black".to_string())),
	("Tess".to_string(), None),
	];
	let data: Vec<_> = source.load(connection).unwrap();
	assert_eq!(expected_data, data);
	let source = users
	.select((name, hair_color))
	.order(name.asc())
	.distinct_on(name);
	let data: Vec<_> = source.load(connection).unwrap();
	assert_eq!(expected_data, data);
	let source = users
	.select((name, hair_color))
	.order(name.desc())
	.distinct_on(name);
	let data: Vec<_> = source.load(connection).unwrap();
	expected_data.reverse();
	assert_eq!(expected_data, data);
	}
	#[cfg(feature = "postgres")]
	#[test]
	fn distinct_on_select_by() {
	use crate::schema::users::dsl::*;
	let connection = &mut connection();
	diesel::sql_query(
	"INSERT INTO users (name, hair_color) VALUES ('Sean', 'black'), ('Sean', NULL), ('Tess', NULL), ('Tess', NULL)",
	).execute(connection)
	.unwrap();
	let source = users
	.select(NewUser::as_select())
	.order(name)
	.distinct_on(name);
	let expected_data = vec![
	NewUser::new("Sean", Some("black")),
	NewUser::new("Tess", None),
	];
	let data: Vec<_> = source.load(connection).unwrap();
	assert_eq!(expected_data, data);
	}
	#[cfg(feature = "postgres")]
	#[test]
	fn distinct_on_select_order_by_two_columns() {
	use crate::schema::users::dsl::*;
	let connection = &mut connection();
	diesel::sql_query(
	"INSERT INTO users (name, hair_color) VALUES ('Sean', 'black'), ('Sean', 'aqua'), ('Tess', 'bronze'), ('Tess', 'champagne')",
	).execute(connection)
	.unwrap();
	let source = users
	.select((name, hair_color))
	.order((name, hair_color.desc()))
	.distinct_on(name);
	let expected_data = vec![

) and additional compile fail tests here (

diesel/diesel_compile_tests/tests/fail/distinct_on_requires_matching_order_clause.rs

Lines 1 to 90 in fa33e38

	extern crate diesel;
	use diesel::*;
	table! {
	users {
	id -> Integer,
	name -> Text,
	hair_color -> Nullable<Text>,
	}
	}
	fn main() {
	// verify that we could use distinct on without order clause
	let _ = users::table.distinct_on(users::name);
	// verify that we could use distinct on with an order clause containing the same column
	let _ = users::table.order_by(users::name).distinct_on(users::name);
	// verify that we could use distinct on with an order clause that contains also a different column
	let _ = users::table.order_by((users::name, users::id)).distinct_on(users::name);
	// verify that we could use distinct on with a select expression and an order clause that contains a different column
	let _ = users::table
	.distinct_on(users::id)
	.select(users::id)
	.order_by((users::id, users::name));
	// verify that this works also with `then_order_by`
	let _ = users::table
	.order_by(users::name)
	.then_order_by(users::id)
	.distinct_on(users::name);
	// verify that this all works with boxed queries
	let _ = users::table.distinct_on(users::name).into_boxed();
	let _ = users::table
	.order_by(users::name)
	.distinct_on(users::name)
	.into_boxed();
	let _ = users::table
	.order_by((users::name, users::id))
	.distinct_on(users::name)
	.into_boxed();
	let _ = users::table
	.order_by(users::name)
	.then_order_by(users::id)
	.distinct_on(users::name)
	.into_boxed();
	// compile fail section
	//
	// we do not allow queries with order clauses that does not contain the distinct value
	let _ = users::table.order_by(users::id).distinct_on(users::name);
	// we do not allow queries where the distinct on expression is not the first expression
	// in our order clause
	let _ = users::table.order_by((users::id, users::name)).distinct_on(users::name);
	// we cannot workaround that with `then_order_by`
	let _ = users::table
	.order_by(users::id)
	.then_order_by(users::name)
	.distinct_on(users::name);
	// it's not possible to set a invalid order clause after we set
	// the distinct on clause
	let _ = users::table.distinct_on(users::name).order_by(users::id);
	// we cannot box invalid queries
	let _ = users::table.order_by(users::id).distinct_on(users::name).into_boxed();
	let _ = users::table
	.order_by((users::id, users::name))
	.distinct_on(users::name)
	.into_boxed();
	// we cannot workaround that with `then_order_by`
	let _ = users::table
	.order_by(users::id)
	.then_order_by(users::name)
	.distinct_on(users::name)
	.into_boxed();
	// it's not possible to set a invalid order clause after we set
	// the distinct on clause
	let _ = users::table
	.distinct_on(users::name)
	.order_by(users::id)

) for rejected cases.