Feature request: @Filter on a relation

[mikeconley12]

Is your feature request related to a problem? Please describe.
I think it would be useful to be able to add filters to the join conditions.

Describe the solution you'd like

@Entity()
export class Post {
  @OneToMany(() => Comment, comment => comment.post)
  @Filter({ name: "enabledComments", cond: { enabledComments: true } })
  @FilterJoinTable({ name: "notDeletedComments", cond: { isDeleted: false } })
  comments = new Collection<Comment>(this);
}

const posts = await orm.em.find(Post, { title: "Example" }, { 
  populate: ["comments"]
});

It should generate a query like this:

select * from "Post" as p
  join "Comment" as c on (p.id = c."postId")
    and (p."enabledComments" = true)
    and (c."isDeleted" = false);

See Hibernate docs (Example 19.2. Using @FilterJoinTable for filterting on the association table)

Describe alternatives you've considered
I've read about filters and LoadStrategy.SELECT_IN, but I think LoadStrategy.SELECT_IN is bad for performance

[B4nan]

Calling this:

const posts = await orm.em.find(Post, { title: "Example" }, { 
  populate: ["comments"],
  strategy: LoadStratetgy.JOINED, // you need to set it explicitly
});

will currently do a left join query - without inner join it does not make much sense, right? Also note you need to enable filters or set them as default: true to make them applied.

Afaik @FilterJoinTable is for actual join tables (we call it pivot tables here), not for appending to the on clause of the relation as you do. But in general I like the idea to filter the on clause somehow. And we could also allow to set the join type to inner join both from FindOptions and from @Filter().

I am not sure about the @Filter({ name: "enabledComments", cond: { enabledComments: true } }), it looks weird that it would filter on the entity, but it would be defined on relation. It took me quite long time till I realised what you mean by that :]

[mikeconley12]

Thanks for the response!

I've updated my example:

@Entity()
export class Post {
  @OneToMany(() => Comment, comment => comment.post)
  @FilterParent({ name: "enabledComments", cond: { enabledComments: true }, default: true })
  @FilterChild({ name: "notDeletedComments", cond: { isDeleted: false }, default: true })
  comments = new Collection<Comment>(this);
}

const posts = await orm.em.find(Post, { title: "Example" }, { 
  populate: ["comments"],
  strategy: LoadStrategy.JOINED
});

select * from "Post" as p
  left join "Comment" as c on (p.id = c."postId")
    and (p."enabledComments" = true)
    and (c."isDeleted" = false);

will currently do a left join query - without inner join it does not make much sense, right?

It was my mistake, it should be a left join.

Also note you need to enable filters or set them as default: true to make them applied.

It's fixed in the example.

I am not sure about the @Filter({ name: "enabledComments", cond: { enabledComments: true } }), it looks weird that it would filter on the entity, but it would be defined on relation.

I've replaced Filter with FilterParent and FilterJoinTable with FilterChild.

[henqx]

Hi, just wanted to bump this, specifically in regards to adding support for extending the left joins in a LoadStrategy.JOINED with a specified filter - essentially so that it would produce the same result as if querying with LoadStrategy.SELECT_IN. So, given this code:

@Filter("exclude-soft-deletes", { isDeleted:false }, { default: true })
class Post {

	@OneToMany({
		entity: () => Comment,
		mappedBy: (c) => c.post,
	})
	comments = new Collection<Comment>(this);
	
	@Property()
	isDeleted = false;
}

@Filter("exclude-soft-deletes", { isDeleted:false }, { default: true })
class Comment {
	@ManyToOne({
		entity: () => Post,
	})
	post: Post;
	
	@Property()
	isDeleted = false;
}


const items = await orm.em.findAll(Item, {populate: ["comments"])

It would generate this:

select * from post
left join comment 
on (post.id = comment.post_id and comment.is_deleted = false)
where post.is_deleted = false

This would also align the strategies so that they would produce the same result in both cases.

[YegorMy]

I might be a bit late or missed a critical feature, but this one is essential for what we do in the company since almost all relations are normalised.
Is there a way to query only active relations without implementing a default filter the top level of said relation?

[robbyemmert]

This is also critical for my team. Are there any good workarounds in the meantime? Is it possible to get the same functionality with an @filter on the entity? Obviously this wouldn't be ideal, but for the short-term this would be great.

[YegorMy]

@robbyemmert we managed to get around that by writing proper finders with leftJoinAndSelect and adding an andWhere clause.

[maximseshuk]

+1