CODEWITHSUNDEEP
sqlmysqlruby-on-railstagstagging
tybro0103
tybro0103

Reputation: 49693

Sql query to find things with most specified tags

Let's say I have the following tables:

TAGS

id: integer
name: string

POSTS

id: integer
body: text

TAGGINGS

id: integer
tag_id: integer
post_id: integer

How would I go about writing a query that selects all posts in order of the post containing the highest number of the following tags (name attribute of tags table): "Cheese", "Wine", "Paris", "Frace", "City", "Scenic", "Art"

See also: Sql query to find things tagged with all specified tags (note: similar, but not a duplicate!)

Upvotes: 1

Views: 618

Answers (2)

Joe Stefanelli
Joe Stefanelli

Reputation: 135729

Unlike your linked question, you did not specify here that you needed to match ALL tags. This query works for ANY.

SELECT p.id, p.text, count(tg.id) as TagCount
    FROM Posts p 
        INNER JOIN Taggings tg 
            ON p.id = tg.post_id
        INNER JOIN Tags t 
            ON tg.tag_id = t.id
    WHERE t.name in ('Cheese', 'Wine', 'Paris', 'Frace', 'City', 'Scenic', 'Art')
    GROUP BY p.id, p.text
    ORDER BY TagCount DESC

Upvotes: 4

Charles Bretana
Charles Bretana

Reputation: 146409

Try This:

   Select p.Id, p.Text, Count(*)
   From Posts p 
      Left Join (Taggings tg Join Tags t 
                    On t.Tag_Id = tg.Tag_Id
                       And t.name in     
                        ('Cheese', 'Wine', 'Paris', 
                         'Frace', 'City', 'Scenic', 'Art'))
         On tg.Post_Id = p.Post_Id
   Group By p.Id, p.text  
   Order By Count(*) Desc

Upvotes: 0

Related Questions