如何使用TypeORM QueryBuilder查询包含指定slug的分类的Listing数据？

Great catch on identifying the core issue with your original query—you were applying the filter incorrectly to the related Category array. Let's walk through the correct approaches depending on whether you need Listings that match any of the target slugs, or all of them:

1. Retrieve Listings with Any Matching Category Slug #

If you want all Listings that have at least one Category with a slug in your ['mens-shirts', 'clearance'] array, use this query:

const targetSlugs = ['mens-shirts', 'clearance'];

const listings = await connection.getRepository(Listing)
  .createQueryBuilder('listing')
  .innerJoinAndSelect('listing.categories', 'category')
  .where('category.slug IN (:...slugs)', { slugs: targetSlugs })
  .distinct(true) // Prevents duplicate Listing entries if multiple Categories match
  .getMany();

Why Your Original Query Failed #

Your first attempt added the category.slug IN (...) condition to the leftJoinAndSelect call. While this filters the Categories attached to each Listing, leftJoin retains all Listings—even those with no matching Categories (resulting in empty categories arrays). The above approach uses innerJoinAndSelect, which only keeps Listings that have at least one matching Category, and the distinct(true) ensures you don't get duplicate Listing objects for multiple matching Categories.

2. Retrieve Listings with All Matching Category Slugs #

If you need Listings that are associated with both "mens-shirts" and "clearance" (i.e., every slug in your target array), use grouping and a having clause to validate the match count:

const targetSlugs = ['mens-shirts', 'clearance'];

const listings = await connection.getRepository(Listing)
  .createQueryBuilder('listing')
  .innerJoin('listing.categories', 'category')
  .where('category.slug IN (:...slugs)', { slugs: targetSlugs })
  .groupBy('listing.id')
  .having('COUNT(DISTINCT category.slug) = :slugCount', { slugCount: targetSlugs.length })
  .leftJoinAndSelect('listing.categories', 'allCategories') // Optional: Fetch all related Categories, not just matches
  .getMany();

This works by:

• Joining with Categories and filtering to only your target slugs
• Grouping results by Listing ID to aggregate matches per Listing
• Using having to check that the number of distinct matching slugs equals the total number of slugs you're searching for (guaranteeing the Listing has all required Categories)

Quick Tips #

• Use innerJoinAndSelect when you only care about Listings with matching Categories (it eliminates empty results automatically).
• COUNT(DISTINCT category.slug) is a safe practice here, even though your Category slugs are unique—it prevents edge cases if duplicates ever exist.
• If you want to keep Listings with no matches but still filter their attached Categories, you can use leftJoinAndSelect with the condition, but you'll need an additional where clause to exclude empty matches if needed.

内容的提问来源于stack exchange，提问作者Jared