基于多多关系的typeform查询

Question

我试图查询表中的产品以查找，某些产品具有"至少";类别"食品"、"冷饮">

我希望有人能给我一个手，能够查询2个或更多的类别我可以使它动态，但我在queryBuilder上挣扎

@Entity()
export class Product{
@Property()
@ManyToMany(() => Category, category=> category.products)
@JoinTable({
name: "product_category",
joinColumn: {
name: "product",
referencedColumnName: "Id"
},
inverseJoinColumn: {
name: "category",
referencedColumnName: "Id"
}
})
categories: Category[];
}

@Entity()
export class Category {
@Property()
@PrimaryGeneratedColumn("uuid")
Id: string;
@Column()
@Property()
name: string;
@Property()
@ManyToMany(() => LibraryItems, libraryItem => libraryItem.categories)
products: Product[];
}

我试过使用这个:query.where("categories.name IN(:...name1) AND categories.name IN(:...name2)", { name1: "Food", name2: "Cold" })

Answer 1

我们讨论过。这是一个查询的例子，将是:

...select(["count(categories.name) as count","o.*"]) 
// count(categories.name) here to get total of categories
.where('categories.name in (:...categories)',{categories})
// filter by user ids we need
.groupBy('product.id')
.having("count = :count", {count:categories.length})
// here we check if the products has the categories we want 
.getRawMany();

PS:正如你在评论中提到的，这个问题将返回至少包含所需类别的行

FOR GLOBAL EXAMPLE WITH MYSQL QUERYusing W3SCHOOL DATABASEEditor

SELECT o.*,count(o.OrderID) as total FROM OrderDetails as o join Products as p  on o.ProductId = p.ProductID where o.ProductID in (11) group by o.OrderID having total = 3;