유사한 태그를 가진 SQL 게시물 선택

유사한 태그를 가진 SQL 게시물 선택

My MariaDB에는 다음과 같은 테이블이 있습니다.

투고 내용

id, title, username_id, text, image_url, url

와의 '대화'라고 불리는 것

id, tag

그리고 post_first 라고 불리는 것도 있습니다.

id, post_id, tag_id.

달성하고 싶은 것은, 현재 페이지에 표시되고 있는 투고와 공통되는 태그가 가장 많은 투고를 「Posts」테이블로부터 3개 취득하는 것입니다.

어디서부터 시작해야 할지 몰라 꼼짝 못하고 있어요.

편집

Posts

id | username_id |   title   |    text     |       image_url      |           url

 1        1         example    example_text  localhost/image.jpg     localhost/first-post
 2        1         example1   example_text  localhost/image1.jpg    localhost/second-post
 3        1         example2   example_text  localhost/image2.jpg    localhost/third-post
 4        1         example4   example_text  localhost/image4.jpg    localhost/fourth-post
...      ...          ...          ...                ...                     ...
...      ...          ...          ...                ...                     ...


Tags

id | tag

 1   herbs
 2   flower
 3   rose

Post_tags

id | post_id | tag_id

 1      1        1
 2      1        2
 3      1        3
 4      2        1
 5      3        1
 6      3        2
 7      4        1
 8      4        2
 9      4        3        

다음을 포함한 어레이를 반환하고 싶습니다.posts.title그리고.posts.image_url가장 많은 게시물이 있는 게시물 선택post_tags.tag_id현재의 것과 공통으로.

보시다시피, 1번 포스트를 선택한 포스트로 하면, 4번 포스트는 태그가 가장 많고, 3번 포스트는 2번, 2번 포스트는 3번입니다.

example4 | localhost/image4.jpg
example3 | localhost/image3.jpg
example2 | localhost/image2.jpg

제가 더 명확하게 말했길 바랍니다.감사해요.

SELECT p.id, p.title, p.image_url, COUNT(*) as how_many_shared_tags
FROM posts p
JOIN post_tags pt ON pt.post_id = p.id
                 AND pt.tag_id IN(SELECT tag_id FROM post_tags WHERE post_id = 1)
WHERE p.id != 1
GROUP BY p.id, p.title, p.image_url
order by COUNT(*) DESC
LIMIT 3

요청에 따라 쿼리에 대한 설명:

1. "부모" 게시물과 대부분의 태그를 공유하는 상위 3개의 게시물을 찾으려면 먼저 "부모" 게시물이 가진 태그 목록을 가져와야 합니다. =>SELECT tag_id FROM post_tags WHERE post_id = 1
2. 그런 다음 조건을 추가하여 게시물에 대한 ID와 태그가 모두 포함된 테이블을 검색하여 이러한 태그 중 하나 이상을 가진 게시물을 찾습니다.tag_id IN(LIST_OF_tag_id_FROM_SUB_SELECT_SHOWN_ABOVE).
3. 이제 어떤 게시물이 적어도 하나의 태그를 "부모"와 공유하고 있는지 알 수 있으므로 실제로 공통 태그 수를 계산하여 그 기준으로 정렬할 수 있습니다 =>order by COUNT(*) DESC
4. "부모" 게시물도 이러한 태그를 "해당"하여 결과에 그를 포함하지 않기 때문에 "부모"의 ID를 제외한 추가 조건을 부여합니다 =>WHERE p.id != 1
5. 마지막으로 상위 3개만 원하기 때문에 결과 세트를 3행으로 제한합니다. LIMIT 3
6. 카운트를 선택할 필요는 없습니다.카운트의 의미를 나타내는 것 뿐입니다.COUNT(*) as how_many_common_tags

원하는 것을 얻을 수 있다면 이것을 체크하세요.그런 다음 쿼리를 최적화해야 할 수 있습니다.

SELECT
t1.*
FROM posts t1,
(
    SELECT
    post_id
    FROM post_tags t2
    WHERE
    tag_id IN (SELECT tag_id FROM post_tags WHERE post_id = $CURRENT_POST_ID)
    AND NOT post_id = $CURRENT_POST_ID
    GROUP BY post_id
    ORDER BY COUNT(tag_id) DESC
    LIMIT 3
) t2
WHERE
t1.id = t2.post_id

이렇게 하면 바로post_id값:

SELECT  x.post_id
    FROM  
    (
        SELECT  b.post_id
            FROM  Post_tags a
            JOIN  Post_tags b USING(tag_id)
            WHERE  a.post_id = 1234
              AND  b.post_id != a.post_id
    ) x
    GROUP BY  x.post_id
    ORDER BY  COUNT(*) DESC
    LIMIT  3;

보다 나은 매핑테이블(Post_tags) 설계에 관한 힌트.그러면 해당 테이블에 대한 최적의 인덱스를 얻을 수 있습니다.

3개의 투고에 대한 자세한 내용은 다음을 참조하십시오.

SELECT  p.*
    FROM  
    (
        SELECT  x.post_id
            FROM  
            (
                SELECT  b.post_id
                    FROM  Post_tag a
                    JOIN  Post_tag b USING(tag_id)
                    WHERE  a.post_id = 1234
                      AND  b.post_id != a.post_id 
            ) AS x
            GROUP BY  x.post_id
            ORDER BY  COUNT(*) DESC
            LIMIT  3 
    ) AS y
    JOIN  Posts AS p  ON p.id = y.post_id;

언급URL : https://stackoverflow.com/questions/42137685/sql-select-posts-that-have-similar-tags