Select All Projects That Have Matching Tags

Select all projects that have matching tags

In any of the following cases, if you don't know the PROJECT.num/PROJECT_TO_TAGS.project_id, you'll have to join to the PROJECTS table to get the id value for finding out what tags it has associated.

Using IN

SELECT p.*
  FROM PROJECTS p
  JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num
 WHERE pt.tag_id IN (SELECT x.tag_id
                       FROM PROJECTS_TO_TAGS x
                      WHERE x.project_id = 4)

Using EXISTS

SELECT p.*
  FROM PROJECTS p
  JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num
 WHERE EXISTS (SELECT NULL
                 FROM PROJECTS_TO_TAGS x
                WHERE x.project_id = 4
                  AND x.tag_id = pt.tag_id)

Using JOINS (this the most efficient one!)

The DISTINCT is necessary because JOINs risk duplicated data turning up in the resultset...

SELECT DISTINCT p.*
  FROM PROJECTS p
  JOIN PROJECTS_TO_TAGS pt ON pt.project_id = p.num
  JOIN PROJECTS_TO_TAGS x ON x.tag_id = pt.tag_id
                         AND x.project_id = 4

Retrieve items matching tags, and simultaneously retrieve all tags that go along with each item

The sub query is non correlated (it can be taken out and executed on its own without errors) so should be executed once.

Might be more efficient to have a subquery to exclude the non matching projects first, and join back the other tables against that. Something like this:-

SELECT project.*, GROUP_CONCAT( DISTINCT tagName ORDER BY tagName SEPARATOR ' ' ) as tags
FROM (SELECT projectId, COUNT( DISTINCT tagName ) AS TagCount
        FROM tag
        INNER JOIN project_tag USING (tagId)
        WHERE tagName IN ( 'the', 'tags' )
        GROUP BY projectId
        HAVING TagCount = 2) Sub1
INNER JOIN project ON Sub1.projectId = project.projectId
INNER JOIN project_tag USING (projectId)
INNER JOIN tag USING (tagId)
GROUP BY projectId

I assume you have an index on tagName.

Get all posts that have a specific tag and keep all other tags on results with SQL

I assume you are happy to send two requests to the database.

First, get all the posts for a given tag:

SELECT * FROM blog_posts bp 
WHERE EXISTS (SELECT * FROM blog_tags bt INNER JOIN
               tags t ON t.id = bt.tag_id
              WHERE bp.id = bt.post_id
               AND t.tag = @SearchTag)

Second, you want to tags, I guess, linked to the one you are looking for via posts:

SELECT * FROM tags t
WHERE EXISTS ( -- Here we link two tags via blog_tags
               SELECT * FROM blog_tags bt1 INNER JOIN
               blog_tags bt2 ON bt1.post_id = bt2.post_id
                     AND bt1.tag_id != bt2.tag_id INNER JOIN
               tags t ON t.id = bt1.tag_id
               WHERE t.tag = @SearchTag
                  AND t.id = bt2.tag_id
)

Fetch fields from a table that has the same relation to another table

SELECT DISTINCT `tags`.`tag`
FROM `tags`
LEFT JOIN `tags_topics` ON `tags`.`id` = `tags_topics`.`tag_id`
LEFT JOIN `topics` ON `tags_topics`.`topic_id` = `topics`.`id`
LEFT JOIN `tags_topics` AS `tt1` ON `tt1`.`topic_id` = `topics`.`id`
LEFT JOIN `tags` AS `t1` ON `t1`.`id` = `tt1`.`tag_id`
LEFT JOIN `tags_topics` AS `tt2` ON `tt2`.`topic_id` = `topics`.`id`
LEFT JOIN `tags` AS `t2` ON `t2`.`id` = `tt2`.`tag_id`
LEFT JOIN `tags_topics` AS `tt3` ON `tt3`.`topic_id` = `topics`.`id`
LEFT JOIN `tags` AS `t3` ON `t3`.`id` = `tt3`.`tag_id`
WHERE `t1`.`tag` = 'tag1'
AND `t2`.`tag` = 'tag2'
AND `t3`.`tag` = 'tag3'
AND `tags`.`tag` NOT IN ('tag1', 'tag2', 'tag3')

SQL query to count all the Projects that contain 2 or more specific topics

SELECT COUNT(DISTINCT p1.project_id)
  FROM project p1 JOIN
       project p2 ON (p1.project_id = p2.project_id)
 WHERE p1.project_topic = 'topic1'
   AND p2.project_topic = 'topic2';

If you wanted topic1 and topic3, then change topic2 to topic3.

How to find records, whose has_many through objects include all objects of some list?

To do this in one query you'd want to take advantage of the common double not exists SQL query, which essentially does find X for all Y.

In your instance, you might do:

class Project < ActiveRecord::Base
  def with_tags(tag_ids)
    where("NOT EXISTS (SELECT * FROM tags
      WHERE NOT EXISTS (SELECT * FROM tagazations
        WHERE tagazations.tag_id = tags.id
        AND tagazations.project_id = projects.id)
      AND tags.id IN (?))", tag_ids)
  end
end

Alternatively, you can use count, group and having, although I suspect the first version is quicker but feel free to benchmark:

def with_tags(tag_ids)
  joins(:tags).select('projects.*, count(tags.id) as tag_count')
    .where(tags: { id: tag_ids }).group('projects.id')
    .having('tag_count = ?', tag_ids.size)
end

How to get similar projects based on tags in Django

It is possible to pack it all in one line:

Project.objects.filter(tags__in=current_project.tags.all()).annotate(Count('name')).order_by('-name__count')[:3]

Or, broken down in steps:

tags = current_project.tags.all()
matches = Project.objects.filter(tags__in=tags).annotate(Count('name'))
results = matches.order_by('-name__count')[:3]

The logic goes as follows:

1. current_project is the instance of the project you want the relations for.
2. The filter selects all projects that have tags that are the same as the current project.
3. The annotate adds a variable to the return values that counts the number of similar names. As projects that match multiple tags are returned multiple times, this value in indicative for the number of matches.
4. The results are sorted on the annotated name__count variable. To get the top 3 results, the list is capped using [:3].

How to select all content between two tags in jQuery

Two methods in particular would be very useful solving this problem: .nextUntil, and .andSelf. The first will grab all of the siblings following your selector, and the latter will lump in whatever is matched by your selector as well, giving you one jQuery object that includes them all:

$("#heading2")
    .nextUntil("#heading3").andSelf()
        .css("background", "red");

This results in the following:

mysql query for related articles

Try

select a.* from Article a
inner join ArticleTag at
  on at.idArticle = a.idArticle
where at.idTag in (select idTag from ArticleTag where idArticle =5)

or

select a.* from Article a
inner join ArticleTag at on at.idArticle= a.idArticle
inner join ArticleTag at2 on at2.idTag = a.idTag and at2.IdArticle! = at.idArticle
where at2.idArticle = 5

Selecting matching subset in many-to-many relation

Select project_ID 
from user_projects
where user_ID in (1,2)
group by project_ID
Having count(*) = 2

You know that you have 2 users, you know that they will be unique (primary key)
so you know that if there are 2 records, for the same project then it's one you want.

Your question indicated you have a GIVEN sent of users therefor you know what users and how many there are. the above SQL could be updated to accept parameters for these known and thus remains dynamic, not limited to just 2 users.

where user_ID in (userlist)
having count(*) = (cntuserList)

-----------To handle situation when set of users is empty-----

Select P.project_ID 
from Projects P
LEFT JOIN user_projects UP
where (UP.user_ID in (1,2) OR UP.USER_ID is null)
group by project_ID
Having count(*) = 2

So here's what this does. It returns all projects and if there's a user affiliated to that project it identifies them.
If you set contains users, the list of projects returned is filtered by that set ensuring that the entire set is in the project through the having clause.

If the set is empty, the LEFT join along with the userID is null statement will keep the projects with no users listed regardless of if the set is empty or not. The having clause will further reduce the set to the # of users you defined in the set, OR 0 indicating return all projects with no users assigned.

One additional edge case we didn't discuss yet is what should happen if a project contains more users than what you defined in the set. Presently this project would be returned; but i'm not positive that's what you wanted.

on a side note thanks for making me think. I don't get to get into the code as much anymore; which is why I troll here from time to time to see if I can help!

---

Related Topics