blocks|key|2470413|text|您可以尝试这样做(它依次使用具有标签和类别的连接，并通过列table_type来区分它们)：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2470414|select+*,+'CAT'+as+table_type
from+products+p+left+join+products_categories+pc+left+join+categories+c++
ON+p.product_id+=+pc.product_id+and+pc.category_id+=+c.category_id
UNION+ALL
select+*,+'TAG'+as+table_type
from+products+p+left+join+products_tags+pt+left+join+tags+t++
ON+p.product_id+=+pt.product_id+and+pt.tag_id+=+t.tag_id
order+by+p.product_id+|code-block|syntax|javascript|2470415|entityMap^0|0|0^^$0|@$1|2|3|4|5|6|7|I|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|J|8|@]|9|@]|A|$E|F]]|$1|G|3|-4|5|6|7|K|8|@]|9|@]|A|$]]]|H|$]]

You could try this (it uses a join with tags and categories in turn and differentiates them by a column table_type):

<pre><code>select *, 'CAT' as table_type
from products p left join products_categories pc left join categories c 
ON p.product_id = pc.product_id and pc.category_id = c.category_id
UNION ALL
select *, 'TAG' as table_type
from products p left join products_tags pt left join tags t 
ON p.product_id = pt.product_id and pt.tag_id = t.tag_id
order by p.product_id 
</code></pre>

blocks|key|2470427|text|您可以使用单个查询从数据库中获取所有信息，并在应用程序级别根据您的需要构造数据，下面的查询将多次返回每个产品，但具有不同的标签/类别。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|2470428|select+p.product_id,+p.name,+t.tag_id,+t.name+tag_name,+c.category_id,+c.cat_name+
from+product+p
join+products_tags+as+pt+on+pt.product_id+=+p.product_id
join+tags+t+on+pt.tag_id+=+t.tag_id
join+products_categories+as+pc+on+pc.product_id+=+p.product_id
join+categories+c+on+pc.category+=+c.category_id
order+by+p.product_id|code-block|syntax|javascript|2470429|伪码|2470430|2470431|products+=+[]
tags+=+[]
categories+=+[]
var+productObj=+{};
var+product_id+=false;
for+each+product
++++/**
++++*+If+product_id+!=+product.id+from+loop+iteration+it+means+this+is+different+product+from+previous+iteration,+Push+this+product+object+in+`products[]`
++++*/
++++if(product.id+!=+product_id){
++++++++products[product.id]+=+product;
++++++++product_id+=+product.id;
++++}
++++/**+you+can+put+additional+check+here+before+insert+in+array+if+value+is+not+present++`products[product.id][tags]`+*/
++++products[product.id][tags][]+=+tag_name;

++++/**+you+can+put+additional+check+here+before+insert+in+array+if+value+is+not+present++`products[product.id][categories]`+*/+
++++products[product.id][categories][]=+cat_name;
end+for+each|2470432|产品的示例对象/数据结构|2470433|[+
++++1+=>+[
++++name+=>+A+good+smartphone,
++++categories+=>+[Tech,+Smartphone],
++++tags+=>+[smartphone,+some-smartphone-brand,+4g]
++++],++
++++2+=>+[
++++name:+Another+product,
++++categories+=>+[Cat1,+Cat2],
++++tags+=>+[Tag1,+Tag2,+.....]
++++],
.....
]|2470434|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|R|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|S|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|T|8|@]|9|@]|A|$]]|$1|I|3|-4|5|6|7|U|8|@]|9|@]|A|$]]|$1|J|3|K|5|D|7|V|8|@]|9|@]|A|$E|F]]|$1|L|3|M|5|6|7|W|8|@]|9|@]|A|$]]|$1|N|3|O|5|D|7|X|8|@]|9|@]|A|$E|F]]|$1|P|3|-4|5|6|7|Y|8|@]|9|@]|A|$]]]|Q|$]]

You could use a single query to get all information from database and at your application level structure your data as per your needs, Below query will return each product multiple times but with different tag/ category.

<pre><code>select p.product_id, p.name, t.tag_id, t.name tag_name, c.category_id, c.cat_name 
from product p
join products_tags as pt on pt.product_id = p.product_id
join tags t on pt.tag_id = t.tag_id
join products_categories as pc on pc.product_id = p.product_id
join categories c on pc.category = c.category_id
order by p.product_id
</code></pre>

Pseudo code

<hr>

<pre><code>products = []
tags = []
categories = []
var productObj= {};
var product_id =false;
for each product
 /**
 * If product_id != product.id from loop iteration it means this is different product from previous iteration, Push this product object in `products[]`
 */
 if(product.id != product_id){
 products[product.id] = product;
 product_id = product.id;
 }
 /** you can put additional check here before insert in array if value is not present `products[product.id][tags]` */
 products[product.id][tags][] = tag_name;

 /** you can put additional check here before insert in array if value is not present `products[product.id][categories]` */ 
 products[product.id][categories][]= cat_name;
end for each
</code></pre>

Sample object/ data structure for products

<pre><code>[ 
 1 =&gt; [
 name =&gt; A good smartphone,
 categories =&gt; [Tech, Smartphone],
 tags =&gt; [smartphone, some-smartphone-brand, 4g]
 ], 
 2 =&gt; [
 name: Another product,
 categories =&gt; [Cat1, Cat2],
 tags =&gt; [Tag1, Tag2, .....]
 ],
.....
]
</code></pre>

I have an entity which has 2 many-to-many relationships: 
There is <code>Product</code>, which can have many categories and many tags. A <code>Tag</code> or a <code>Category</code>can also have many products. 
The schema right now is: 

<pre><code>products
-------------
product_id
name

categories
-------------
category_id
name

tags
-------------
tag_id
name

products_categories
-------------
product_id
category_id

products_tags
-------------
product_id
tag_id
</code></pre>

 
I need to display in a single page a list of products with all their tags and categories. 
The problem is that the number of queries I'm executing is <code>2n + 1</code> where n is the number of products listed in the page. 
Basically first I'm doing:

<pre><code>select product_id, name from products
</code></pre>

Then in a loop in my application I do these queries (in pseudo code):

<pre><code>tags = []
categories = []
for each product
 tags[product_id] = select t.tag_id, t.name 
 from products_tags as pt 
 join tags t 
 on pt.tag_id = t.tag_id
 where pt.product_id = {product_id}

 categories[product_id] = select c.category_id, c.name 
 from products_categories as pc 
 join categories c 
 on pc.category = c.category_id
 where pc.product_id = {product_id}
end for each
</code></pre>

 
Is there a good approach to make the number of executed queries independent of the number of records queried? 
 
Edit 
For each product I need to display data in this format:

<pre><code>-------------------------------------------------------
| Product name: A good smartphone |
| Categories: Tech, Smartphone |
| Tags: smartphone, some-smartphone-brand, 4g |
| |
------------------------------------------------------- 
</code></pre>

Make the amount of queries constant with many-to-many relationship

翻译质量差，导致语言生硬或混乱。

没有提供实际的解决方法或示例。

解答不清晰，无法理解或解决问题。

页面排版不美观，阅读体验差。

文章

问答

视频

学习中心

腾讯云实验室

直播

竞赛

腾讯云代码分析专区

腾讯iOA零信任安全管理系统专区

腾讯云架构师技术同盟交流圈

腾讯云数据库专区

腾讯云顾问专区

腾讯云原生专区

腾讯混元专区

腾讯云TCE专区

腾讯云Lighthouse专区

腾讯云HAI专区

腾讯云Edgeone专区

腾讯云存储专区

腾讯云智能专区

腾讯轻联专区 

腾讯云开发专区

TAPD专区

腾讯轻量云游戏服专区

腾讯云最具价值专家

腾讯云架构师技术同盟

腾讯云创作之星

腾讯云开发者先锋

腾讯云代码助手

云原生构建

TAPD 敏捷项目管理

Cloud Studio

SDK中心

API中心

命令行工具

涵盖代码开发、场景应用、自动测试全流程，助你从零构建专属AI助手

一站式MCP教程库，解锁AI应用新玩法

我有一个实体，它有两个多对多关系：还有Product，它可以有很多类别和标签。一个Tag或一个Category也可以有许多产品。现在的模式是：products-------------product_idnamecategories-------------category_idnametags------------...

问使用多对多关系使查询量保持恒定
EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用多对多关系使查询量保持恒定EN

社区

活动

圈层

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问使用多对多关系使查询量保持恒定
EN