如何不使用JOIN实现关联搜索：匹配Order及可选关联的Company、Product

哈哈，这个坑我之前踩过！你遇到的问题本质是INNER JOIN会自动过滤掉没有关联实体的Order记录，哪怕它的title完全匹配搜索词也拿不到。解决的核心思路是改用LEFT JOIN，再调整查询条件的位置，避免NULL值被误过滤。下面给你详细的解决方案：

首先把所有关联查询换成LEFT JOIN，这样哪怕Order没有关联的Company或Product，这条Order本身的记录也会被保留（关联字段为NULL）。然后要注意搜索条件的写法，不能直接把关联实体的搜索条件丢到WHERE里，否则会把NULL的记录过滤掉。

1. 原生SQL示例 #

直接写SQL的话，你可以这么写：

SELECT DISTINCT o.*
FROM orders o
-- LEFT JOIN Company，同时把Company的搜索条件放在ON里
LEFT JOIN companies c 
  ON o.company_id = c.id 
  AND c.name LIKE CONCAT('%', :searchTerm, '%')
-- 关联LineItem和Product，同样把Product的搜索条件放在ON里
LEFT JOIN line_items li 
  ON o.id = li.order_id
LEFT JOIN products p 
  ON li.product_id = p.id 
  AND p.name LIKE CONCAT('%', :searchTerm, '%')
-- WHERE条件：匹配title，或者有符合条件的Company，或者有符合条件的Product
WHERE o.title LIKE CONCAT('%', :searchTerm, '%')
   OR c.id IS NOT NULL
   OR p.id IS NOT NULL;

关键细节解释： #

• LEFT JOIN + ON子句加条件：这样只有符合搜索条件的Company/Product才会被关联进来，不符合或者没有关联的就保持NULL，不会过滤掉Order本身。
• DISTINCT去重：一个Order可能有多个LineItem匹配Product的搜索词，会返回重复的Order记录，所以必须加DISTINCT。
• WHERE里的OR判断：要么Order.title匹配，要么存在匹配的Company（c.id不为空说明ON条件匹配上了），要么存在匹配的Product（同理），这样就覆盖了所有你需要的场景。

2. JPA/Hibernate Criteria API示例（如果用ORM的话） #

如果是用Java的JPA或者Hibernate，用Criteria API可以这么写：

// 先获取CriteriaBuilder和查询对象
CriteriaBuilder cb = entityManager.getCriteriaBuilder();
CriteriaQuery<Order> query = cb.createQuery(Order.class);
Root<Order> orderRoot = query.from(Order.class);

// LEFT JOIN Company，同时添加Company.name的搜索条件到ON子句
Join<Order, Company> companyJoin = orderRoot.join("company", JoinType.LEFT);
companyJoin.on(cb.like(companyJoin.get("name"), "%" + searchTerm + "%"));

// 链式LEFT JOIN LineItem和Product，同样把Product.name的条件放ON里
Join<Order, LineItem> lineItemJoin = orderRoot.join("lineItems", JoinType.LEFT);
Join<LineItem, Product> productJoin = lineItemJoin.join("product", JoinType.LEFT);
productJoin.on(cb.like(productJoin.get("name"), "%" + searchTerm + "%"));

// 构造三个匹配条件：title匹配、有符合条件的Company、有符合条件的Product
Predicate titleMatch = cb.like(orderRoot.get("title"), "%" + searchTerm + "%");
Predicate companyMatch = cb.isNotNull(companyJoin.get("id"));
Predicate productMatch = cb.isNotNull(productJoin.get("id"));

// 用OR组合条件，并且去重
query.where(cb.or(titleMatch, companyMatch, productMatch));
query.distinct(true);

// 执行查询
List<Order> results = entityManager.createQuery(query).getResultList();

3. 额外注意事项 #

• 性能优化：如果数据量比较大，%xxx%这种模糊查询的性能会很差，建议给这些字段创建全文索引，或者考虑用Elasticsearch这类全文搜索引擎来做搜索，效率会高很多。
• 空值判断：不要直接写c.name LIKE '%xxx%'在WHERE里，因为没有Company的话c.name是NULL，NULL和任何值做LIKE都会返回false，会把这些符合title的Order过滤掉，所以要用c.id IS NOT NULL来判断是否有匹配的Company。

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