Seeking a way to retrieve posts based on category similar to what a CMS does.
For instance, querying posts by Category A should include all posts assigned to Category A as well as those attached to child categories of Category A.
I'm unsure how to construct this query, so any assistance would be greatly appreciated :)
Below are my entities:
@Tree("materialized-path")
export class Category {
@PrimaryGeneratedColumn()
id: number;
@Column()
title: string;
@ManyToMany((type) => Post, (post) => post.categories)
posts: Post[];
@Expose()
@TreeChildren()
children: Category[];
@Expose()
@TreeParent()
parent: Category;
}
export class Post{
@PrimaryGeneratedColumn()
id: number;
@Column()
title: string;
@ManyToMany((type) => Category, (category) => category.posts)
@JoinTable()
categories: Category[];
}
The following SQL Query accomplishes the task (Example with category id 1):
SELECT * FROM post WHERE id IN (
SELECT postId FROM post_categories_category as postCat WHERE postCat.categoryId IN (
SELECT id FROM category WHERE category.mpath LIKE "1.%" OR category.mpath LIKE "%.1.%"
)
)
Therefore, the question remains - how can this SQL query be converted into a typeORM query?