323

MySQL query to retrieve items that have an exact match in a many-to-many relationship

Question:

I have the following tables:

Media: - mediaid - mediatitle MediaTag: - mediaid - mediatag Tag: - tagid - tagname

The following tags:

Tag: (1) - Public Tag: (2) - Premium Tag: (3) - Restricted

The following media with tags:

Media: (1) - Public Media: (2) - Premium & Restricted Media: (3) - Premium Media: (4) - Restricted Media: (5) - No tags

Assume that a user has permissions to see only Public (1) and Restricted (3) tags. If a media has a combination of tags that the user doesn't have explicit access to, he's not allowed to see it. If a media has no tag it's not visible at all. So the user in this case would be able to see only Media 1 and Media 4.

Is it possible to write a MySQL query to retrieve the media assuming I have the user permissions in the form of an array with tag IDs (1,3) ?

Answer1:

JOIN the three tables, and pass the array, that contain the tag Id's that the user has permissions to access them, to the IN predicate in the WHERE clause.

For instance for the array [1, 3], you can do this:

SELECT m.mediatitle, t.tagname FROM media m INNER JOIN MediaTag mt ON m.mediaid = mt.mediaid INNER JOIN Tag t ON mt.mediatag = t.tagid WHERE t.id IN (1, 3);

<strong>Update:</strong> To get only the mediatitles that had both tag ids (1, 3) for example, use a GROUP BY mediatitle with HAVING COUNT(tagname) = 2 like so:

SELECT m.mediatitle FROM media m INNER JOIN mediaTag mt ON m.mediaid = mt.mediaid INNER JOIN tag t ON mt.mediatag = t.tagid WHERE t.tagid IN (1, 3) GROUP BY m.mediatitle HAVING COUNT(t.tagname) = 2; <h2><a href="http://sqlfiddle.com/#!2/adf00/2" rel="nofollow">SQL Fiddle Demo</a></h2>

Recommend

  • line and string position of grep match
  • Aurelia validation: applying some rule on change and some on blur on same property
  • Set default application configs for Elixir packages
  • If statements in .htaccess files, to enable password protection when in specific directory only
  • mysql user row level access
  • Intellij Idea Terminal shortcut not working
  • How read between delimiters in php DOM of a XML file?
  • Granting permissions to Azure Active Directory Web Application automatically
  • Xmonad multiple submap key combos
  • redirect_to root_url and return unless @user.activated
  • MySQL performance when updating row with FK
  • VBA Excel, loop through variables
  • How to explicitly/implicitly implemented interface members in C++/CLI?
  • uniform generation of points on 3D box
  • chrome.tabs.executeScript only fires when the Developer Console is open
  • Rails Find when some params will be blank
  • MongoError: Incorrect arguments
  • MailKit: The IMAP server replied to the 'EXAMINE' command with a 'BAD' response
  • Dialing with Intent.ACTION_CALL stopps at # in phone number
  • Highlight one bar in a series in highcharts?
  • Lost migrations and Azure database is now out of sync
  • Is there any way to access browser form field suggestions from JavaScript?
  • Android screen density dpi vs ppi
  • Dynamically accessing properties of knockoutjs observable array
  • Why ng-show works with ng-repeat but ng-if doesn't? [duplicate]
  • script to move all files from one location to another location
  • WinForms: two way TextBox problem
  • Can I make an Android app that runs a web view in Chrome 39?
  • Cannot Parse HTML Data Using Android / JSOUP
  • python regex in pyparsing
  • JTable with a ScrollPane misbehaving
  • Turn off referential integrity in Derby? is it possible?
  • Authorize attributes not working in MVC 4
  • unknown Exception android
  • Easiest way to encapsulate a HTML5 webpage into an android app?
  • Busy indicator not showing up in wpf window [duplicate]
  • failed to connect to specific WiFi in android programmatically
  • Python/Django TangoWithDjango Models and Databases
  • Net Present Value in Excel for Grouped Recurring CF
  • How can I use threading to 'tick' a timer to be accessed by other threads?