How can I use join to find many-to-many relationships?

For example, I have three tables .. article (id, title, author, content)-article table tags (id, name)-label table rationship (article_id, tags_id)-article tag Association table to get a result set, the result set must contain tags. original practice: $ articleListDB: table... for example, I have three tables ..
Article (id, title, author, content)-article table
Tags (id, name)-label table
Rationship (article_id, tags_id)-document label Association Table

The purpose is to obtain a result set that contains tags.

Original practice:
$ ArticleList = DB: table ('Article')-> get (); // get all articles
// Document Information sorting

For ($ I = 0; $ I <count ($ articleList); $ I ++) {$ artagslist = $ articletagsModel-> getRelateTags ($ articleList [$ I]-> id ); // obtain all the tag IDs of the corresponding document IDs in the relational table. if (isset ($ artagslist) {$ tagsArray = array (); foreach ($ artagslist as $ values) {// traverse the tag id to find the result from the tag table .. $ singletags = $ tagsModel-> getTagsById ($ values-> tags_id); // append the result to the array $ tagsArray .. array_push ($ tagsArray, $ singletags);} $ articleList [$ I]-> tags = $ tagsArray; // Add a field in the initial data to save the tag array }}

The final array is as follows:
Array: 1 [Partition
0 => {#176 bytes

+ "Id": "1" + "title": "This is the title" + "author": "This is the author" + "content ": "This is the body of the article" + "tags": array: 2 [bytes 0 => {#182 bytes + "id": 5 + "name ": "nodejs" + "create_time": null + "update_time": null + "user_id": null + "num": 1} 1 =>{# 183 seconds + "id ": 4 + "name": "python" + "create_time": null + "update_time": null + "user_id": null + "num": 1}]

}
]

Although my original practice can also achieve the goal, I always feel that this practice is a bit redundant, so I want to ask if there is a better way? Or you can use SQL to solve this problem in one sentence. Thank you!

Reply content:

For example, I have three tables ..
Article (id, title, author, content)-article table
Tags (id, name)-label table
Rationship (article_id, tags_id)-document label Association Table

The purpose is to obtain a result set that contains tags.

Original practice:
$ ArticleList = DB: table ('Article')-> get (); // get all articles
// Document Information sorting

For ($ I = 0; $ I <count ($ articleList); $ I ++) {$ artagslist = $ articletagsModel-> getRelateTags ($ articleList [$ I]-> id ); // obtain all the tag IDs of the corresponding document IDs in the relational table. if (isset ($ artagslist) {$ tagsArray = array (); foreach ($ artagslist as $ values) {// traverse the tag id to find the result from the tag table .. $ singletags = $ tagsModel-> getTagsById ($ values-> tags_id); // append the result to the array $ tagsArray .. array_push ($ tagsArray, $ singletags);} $ articleList [$ I]-> tags = $ tagsArray; // Add a field in the initial data to save the tag array }}

The final array is as follows:
Array: 1 [Partition
0 => {#176 bytes

+ "Id": "1" + "title": "This is the title" + "author": "This is the author" + "content ": "This is the body of the article" + "tags": array: 2 [bytes 0 => {#182 bytes + "id": 5 + "name ": "nodejs" + "create_time": null + "update_time": null + "user_id": null + "num": 1} 1 =>{# 183 seconds + "id ": 4 + "name": "python" + "create_time": null + "update_time": null + "user_id": null + "num": 1}]

}
]

Although my original practice can also achieve the goal, I always feel that this practice is a bit redundant, so I want to ask if there is a better way? Or you can use SQL to solve this problem in one sentence. Thank you!

I am afraid that SQL cannot be nested multiple times. If you only want the tag of the article to be okay, and the list of articles associated with the tag will not work.

But is there usually no such requirement? Listing all articles on the page is very common, and the brief content of articles is very common. Listing the tags of articles is also very common, but I have never seen listing the articles related to tags. You should consider SQL Optimization Based on the final display needs, rather than trying to read all the data.

In addition, from the data you listed, the tag has enough names. It is useless to create a time to modify the creator and so on. If some frameworks do not have mandatory requirements for id, the table id is useless.

If you need tag-related articles, we recommend that you use ajax to obtain them again. You can use mysql group_conact for the tags used in this article (it seems like you are, too lazy to check them ), the function is to concatenate the content of the group by group into strings. For example
Select articles. *, group_conact (tag.name, ',') From articles left join tags...
Check the mysql manual for details.

select ar.*,t.* from article as ar left join relationship as rs on ar.id = rs.article_id left join tags as t on rs.tags_id =t.id This is an SQL statement. It can be spliced when the model is called to operate the database.