mysql - Eloquent Join 的复杂条件

Question

我希望使用 Eloquent 从我们的数据库中输出一些数据，但数据库可能会有所不同。

下面是我的数据库表的说明

网络服务标签:

----------------------------------------------------------
| id | webservice_tag        | webservice_name | blog_id |
==========================================================
| 1  | TEST, TESSST, TES     | Test Service    | 1       |
----------------------------------------------------------
| 2  | OPTION, OPT, EXAMPLE  | Example Service | 1       |
----------------------------------------------------------
| 3  | ANOTHER, ANO, THER    | Another Service | 1       |
----------------------------------------------------------

博文:

----------------------------------------------------------
| id | title                         | blog_id | tag     |
==========================================================
| 1  | Blog Title 1                  | 1       | THER    |
----------------------------------------------------------
| 2  | Blog Title 2                  | 1       | TES     |
----------------------------------------------------------
| 3  | Blog Title 3                  | 1       | ANOTHER |
----------------------------------------------------------

所以在这里，我们有两个表。博客文章和网络服务标签。

我们的博客文章中包含来自许多不同提供商的大量网络服务。例如，测试服务、示例服务和其他服务。但是，这些网络服务非常不一致；他们会发送标签的组合，并且不能保证两个帖子是相同的。

因此，我们创建了一个名为 Webservice Tags 的表，用于记录这些事件中的每一个。通过这种方式，我们可以识别(在示例中)博客标题 3 是由另一个服务发送的，博客标题 2 是由测试服务等发送的。

我正在开发报告以显示我们从每个网络服务中获得了多少帖子。因此，对于每篇博文，我都需要识别 Web 服务并获取与其相关联的 Web 服务名称。我们有多个博客，每个都有自己的网络服务(有些可能共享一个标签)，因此需要将此报告隔离到每个博客。

这是 Eloquent 中的查询:

$query = DB::table('blog_posts')
    ->join('webservice_tags', function($join) use ($blog) {
        $join->on('blog_posts.tag', '=', 'webservice_tags.webservice_tag')
        ->where('webservice_tags.blog_id', '=', $blog->id);
    })
    ->addSelect('webservice_tags.webservice_name AS name')
    ->addSelect(DB::raw("COUNT(blog_posts.id) AS count"))
    ->where('blog_posts.blog_id', '=', $blog->id)
    ->groupBy('webservice_tags.webservice_name')
    ->get();

这个查询很好，而 Web 服务发送了一个一致的标签。然而，现在每个 Web 服务都有不同的标签，此报告需要对它们进行平均计数。

这是我的修改，但它没有按预期工作:

->join('webservice_tags', function($join) use ($blog) {
    $join->on('blog_posts.tag', 'LIKE', DB::raw('CONCAT("%", webservice_tags.webservice_tag, "%")'))
    ->where('webservice_tags.blog_id', '=', $blog->id);
})

我没有通过此方法在查询中获得任何匹配项。

查询旨在将任何字段加入 SELECT，其中 blog_posts.tag 在逗号分隔的 webservice_tags 列表中。

有没有办法更有效地做到这一点？

关于blog_id关系的解释

该系统管理多个博客，以及这些博客中的帖子。网络服务可能适用于一个博客，但不适用于另一个，这就是为什么 blog_id 与网络服务以及各个帖子相关联的原因。有些东西与网络服务相关联，例如佣金百分比，测试网络服务可能会为博客 A 提供 10%，为博客 B 提供 12%，因此它们本质上是分开的。

Answer 1

最快/粗略的解决方案:

您看不到任何结果的原因是:

$join->on('blog_posts.tag', 'LIKE', DB::raw('CONCAT("%", webservice_tags.webservice_tag, "%")'))

转化为:

INNER JOIN `webservice_tags`
   ON `blog_posts`.`tag` LIKE CONCAT("%", webservice_tags.webservice_tag, "%") 

这会尝试匹配 blog_posts。标签 的值类似于 %ANOTHER, ANO, THER%。

假设 blog_posts.tag 是“ANOTHER”，webservice_tags.webservice_tag 是“ANOTHER, ANO, THER”。数据库没有匹配“ANOTHER”，而是尝试匹配如下值:

ANOTHER    !=    FOOANOTHER, ANO, THER
ANOTHER    !=    ANOTHER, ANO, THERBAR
ANOTHER    !=    FOOANOTHER, ANO, THERBAR

这就是您没有得到任何结果的原因。您需要交换列，如下所示:

$join->on('webservice_tags.webservice_tag', 'LIKE', DB::raw('CONCAT("%", blog_posts.tag, "%")'))

---

更长的解决方案:

我只是想在这里抛出我的想法。在考虑这个问题时，我想到了以下几点:

• 标记<-> Web 服务映射实际上是一组数据时存储为字符串。这使得搜索和对其执行查询变得困难。我喜欢将它们分解成两个或更多表。这叫做 Database normalization .别问我是第几级，我一直没看懂他们的技术说明。 :D
• 既然您提到一个网络服务可以有很多标签，但一些标签也可以被不同的网络服务共享，我们将需要一个数据透视表来处理这种多对多关系。
• 聚合时，我喜欢从上到下开始。所以我从网络服务开始写查询，然后一直写到博客文章。这与您的示例相反。
• 我暂时忽略了 webservices 表中的 blog_id 列，因为我还不太了解它们的作用。

所以首先我会尝试将表规范化为 webservices、webservice_tags、tags 和 blog_posts，如下所示:

网络服务:

-------------------------
| id  | webservice_name |
=========================
| 1   | Test Service    |
-------------------------
| 2   | Example Service |
-------------------------
| 3   | Another Service |
-------------------------

网络服务标签:

------------------------------------------
| id | tag_name          | webservice_id |
==========================================
| 1  | TEST              | 1             |
------------------------------------------
| 2  | TESSST            | 1             |
------------------------------------------
| 3  | TES               | 1             |
------------------------------------------
| 4  | OPTION            | 2             |
------------------------------------------
| 5  | OPT               | 2             |
------------------------------------------
| 6  | EXAMPLE           | 2             |
------------------------------------------
| 7  | ANOTHER           | 3             |
------------------------------------------
| 8  | ANO               | 3             |
------------------------------------------
| 9  | THER              | 3             |
------------------------------------------
| 10 | FOO               | NULL          |
------------------------------------------
| 11 | BAR               | NULL          |
------------------------------------------

请注意，为了便于阅读，我在上面的数据透视表中使用了 tag_name。我想最好改用 tag_id。

标签:

--------------------------
| id | tag_name          |
==========================
| 1  | TEST              |
--------------------------
| 2  | TESSST            |
--------------------------
| 3  | TES               |
--------------------------
| 4  | OPTION            |
--------------------------
| 5  | OPT               |
--------------------------
| 6  | EXAMPLE           |
--------------------------
| 7  | ANOTHER           |
--------------------------
| 8  | ANO               |
--------------------------
| 9  | THER              |
--------------------------
| 10 | FOO               |
--------------------------
| 11 | BAR               |
--------------------------

博客帖子:

-----------------------------------------------------------
| id | title                         | blog_id | tag_name |
===========================================================
| 1  | Blog Title 1                  | 1       | THER     |
-----------------------------------------------------------
| 2  | Blog Title 2                  | 1       | TES      |
-----------------------------------------------------------
| 3  | Blog Title 3                  | 1       | ANOTHER  |
-----------------------------------------------------------

现在要获取每个 Web 服务创建了多少博客文章的报告，我们可以使用连接查询来完成此操作。在这种情况下，QueryBuilder 查询会提供更好的性能，因为我们只想知道聚合，而不是实际的数据库模型:

$report = DB::table('webservices')
    ->leftJoin('webservice_tags', 'webservice_tags.webservice_id', '=', 'webservices.id')
    ->leftJoin('tags', 'tags.tag_name', '=', 'webservices_tags.tag_name')
    ->leftJoin('blog_posts', 'blog_posts.tag_name', '=', 'tags.tag_name')
    ->where('blog_posts.blog_id', '=', $blog->id)
    ->select(['webservices.webservice_name', DB::raw('COUNT(`blog_posts.id`) as `num_posts`')])
    ->groupBy('webservices.id')
    ->get();

对于所有网络服务，您现在会得到一份关于每个网络服务创建了多少博文的报告。

需要注意的是，由于您提到网络服务可能会共享标签，并且您正在通过标签识别网络服务 <-> 博客文章，您无法 100% 准确地确定源 Web 服务，因为您无法确定哪个 Web 服务为该帖子设置了该标签。

---

额外:您可能会注意到我还在 Web 服务标签中添加了 FOO 和 BAR。这会有所帮助，就像您所说的 Web 服务“非常不一致”，新标签将添加到您的数据库中。您还可以查询它们并轻松生成报告，以找出尚未映射的标签。