Multiple join query

[samsamai]

@tyt2y3 @billy1624 I've got a bit stuck while trying to test a more complicated query in the test suite.
How can I do a query such as this with ORM:

SELECT
	baker.name, sum(lineitem.price * lineitem.quantity)

FROM
	baker
	INNER JOIN cakes_bakers ON cakes_bakers.baker_id = baker.id
	INNER JOIN cake ON cakes_bakers.cake_id = cake.id
	INNER JOIN lineitem ON lineitem.cake_id = cake.id
GROUP BY
	baker.name;

[tyt2y3]

sea-orm/src/query/helper.rs

Line 130 in ca9aa01

fn join(mut self, join: JoinType, rel: RelationDef) -> Self {

The join method on the QuerySelect trait is not bounded by Related trait. RelationDef can be constructed at runtime too.

[tyt2y3]

is this what you are looking for?

sea-orm/src/query/helper.rs

Lines 91 to 104 in ca9aa01

	/// Add an AND HAVING expression
	/// ```
	/// use sea_orm::{entity::*, query::*, tests_cfg::cake, DatabaseBackend};
	///
	/// assert_eq!(
	/// cake::Entity::find()
	/// .having(cake::Column::Id.eq(4))
	/// .having(cake::Column::Id.eq(5))
	/// .build(DatabaseBackend::MySql)
	/// .to_string(),
	/// "SELECT `cake`.`id`, `cake`.`name` FROM `cake` HAVING `cake`.`id` = 4 AND `cake`.`id` = 5"
	/// );
	/// ```
	fn having<F>(mut self, filter: F) -> Self

[samsamai]

Yes similar to that but instead of equal I wanted to do greater than.
Currently PartialEq is implemented for ActiveValue which allows us to do .having(cake::Column::Id.eq(4)) but PartialOrd is not implemented for ActiveValue so we can't do .having(cake::Column::price.gt(dec!(50.0)))

[tyt2y3]

sea-orm/src/entity/column.rs

Lines 83 to 88 in 8097611

	bind_oper!(eq);
	bind_oper!(ne);
	bind_oper!(gt);
	bind_oper!(gte);
	bind_oper!(lt);
	bind_oper!(lte);

I am sorry I did not get the problem. I thought all operators eq, gt, lt, etc are implemented together. Is it a limitation of SeaQuery?

[samsamai]

Sorry my mistake, I just tried .having(order::Column::Total.gt(dec!(100.00))) in one of the tests and it works!
It must have failed for me before because the previous version of the test was trying to do multiple joins or something, anyway it works, so all good!

[tyt2y3]

Thanks, that's great!