php - 从 MySQL 表中选择，按匹配的类别数排序

Question

我有一张 table ，如下所示:

id:int | name:String | categories:String

示例行:

1 | "Lorem1" | "A, B, C" 
2 | "Lorem2" | "A, B" 
3 | "Lorem3" | "A, C" 
4 | "Lorem4" | "B" 

我还有一个表格，您可以在其中检查您感兴趣的类别。这应该是选择顺序的指南。

首先，您获取包含所有选定类别的行，然后获取包含较少匹配项的行。 (如果该行没有任何类别，则不会显示)

例如，如果有人检查:

• A 和 B，他们应该按以下顺序返回行:Lorem1、Lorem2、Lorem3、Lorem 4

• A 和 C，他们应该按以下顺序返回行:Lorem1、Lorem3、Lorem2

这就是我想要做的。我对编程很陌生，这个问题出现了。

我也知道，也许我应该为类别和对象之间的连接创建一个新表。

Answer 1

数据的标准化版本可能是:

create table items (
  id int,
  name varchar(50),
  primary key (id),
  index (name)
);

create table categories (
  id int,
  name varchar(50),
  primary key (id),
  index (name)
);

create table items_categories (
  item_id int,
  category_id int,
  primary key (item_id, category_id),
  index (category_id, item_id),
  foreign key (item_id) references items(id),
  foreign key (category_id) references categories(id)
);

insert into items (id, name) values
  (1, 'Lorem1'),
  (2, 'Lorem2'),
  (3, 'Lorem3'),
  (4, 'Lorem4');

insert into categories (id, name) values
  (1, 'A'),
  (2, 'B'),
  (3, 'C'),
  (4, 'D');

insert into items_categories (item_id, category_id) values
  (1, 1),
  (1, 2),
  (1, 3),
  (2, 1),
  (2, 2),
  (3, 1),
  (3, 3),
  (4, 2);

现在 - 当您搜索类别“A”和“B”中的项目时，SELECT 查询将是:

select i.*, count(*) as matches
from items i
join items_categories ic on ic.item_id = i.id
join categories c on c.id = ic.category_id
where c.name in ('A', 'B')
group by i.id
order by matches desc, i.name;

结果:

| id  | name   | matches |
| --- | ------ | ------- |
| 1   | Lorem1 | 2       |
| 2   | Lorem2 | 2       |
| 3   | Lorem3 | 1       |
| 4   | Lorem4 | 1       |

如果要在类别“A”和“C”中搜索，请将 WHERE 子句更改为

where c.name in ('A', 'C')

结果将是:

| id  | name   | matches |
| --- | ------ | ------- |
| 1   | Lorem1 | 2       |
| 3   | Lorem3 | 2       |
| 2   | Lorem2 | 1       |

View on DB Fiddle

您甚至可以使用“模拟”您的原始架构

select i.*, group_concat(c.name separator ', ') as categories
from items i
join items_categories ic on ic.item_id = i.id
join categories c on c.id = ic.category_id
group by i.id

结果:

| id  | name   | categories |
| --- | ------ | ---------- |
| 1   | Lorem1 | A, B, C    |
| 2   | Lorem2 | A, B       |
| 3   | Lorem3 | A, C       |
| 4   | Lorem4 | B          |

反过来的话会困难得多。这是(对我而言)使用规范化模式的主要原因。

值得一读:Is storing a delimited list in a database column really that bad?