Multiple JOIN aggregation

[Marahin]

Hi,

I'm looking for support when it comes to doing multiple JOIN aggregation. Currently I gave up and wrote raw sql:

		rows, err := db.Client.Debug().QueryContext(context.Background(),
			`
			select
				players.name,
				players_with_profits.profit as "profit",
				players_with_experience.experience as "experience",
				players.level,
				players.vocation,
				players.world,
				g.name as "guild"
			from
							players
			left join (
				select
								players.id as id,
								sum(pdp.profit) as profit
				from
								players
				join player_daily_profits pdp on
								pdp.player_id = players.id
				where
								pdp.created_at >= $1
				group by
								players.id) players_with_profits on
							players_with_profits.id = players.id
			left join (
				select
								players.id as id,
								sum(er.relative) as experience
				from
								players
				join experience_records er on
								er.player_id = players.id
				where
								er.created_at >= $1
				group by
								players.id) players_with_experience on
							players_with_experience.id = players.id
			left join guilds g on
				g.id = players.guild_players
			where
				experience is not null and profit is not null
			order by
				experience desc,
				profit desc
			LIMIT $2`, beginningDay, limit,
		)

But I would prefer to stick to ent's built in queries interface. Is it currently possible to break down this query so it contains as little raw sql as possible?

[yonidavidson]

Hey @Marahin , have you had a look at these examples https://entgo.io/docs/predicates/#get-all-users-with-a-tesla-car ?

[Marahin]

Hey @Marahin , have you had a look at these examples https://entgo.io/docs/predicates/#get-all-users-with-a-tesla-car ?

Hi. I just did, but I do not see where are you going with it?

My issue specifically is about joining a subquery (how do I actually do that in ent?). Doing simple joins works in ent, and I've been using that in multiple places, but in case of this query I just need them to be a bit more complex (coalesce, filter the results, and then join).