Need help adjusting query

Databases
1

Hi there everyone!

I have built a link/content sharing site. The way it works is that each link can have multiple categories assigned to it. A video of a motorcycle race might have motorcycle, racing, extreme and trending attached to it. The way it gets stored into the database is by a linkcats table in which it stores the link_id and the cat_id. The example above would result in 4 records for that link_id, since it had four categories assigned to it.

The site learns what people like as they browse. As they view, vote on and post content, the site figures out their most and least favorite categories. My intention is to display the results of what the site thinks is their most favorite while cutting out what it thinks is their least favorite. To do that, I’m trying to display links from their 5 most favorite categories while nixing them if any unfave cats are assigned to them.

Here’s what I tried:

SELECT shortenified_urls.* FROM shortenified_urls LEFT JOIN linkcats ON linkcats.link_id = shortenified_urls.id WHERE linkcats.cat_id IN (17,1,35,33,50) AND linkcats.cat_id NOT IN (28) AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1' GROUP BY shortenified_urls

This didn’t work, I think because of the multiple categories. Since the links are being matched more than once due to multiple category matches, even if links matching 28 get passed by, if that same link gets matched on another of it’s categories, it will still get included in the results. At least, I think that’s what’s happening.

Does anyone have an idea on how I might alter the query to achieve the desired result?

Thanks for your time!

2

Like you said, it’ll match up urls if it matches any of the cat_ids in your where clause, regardless of whether there’s a corresponding record in cat_id 28, which you don’t want.

So what you need to do is filter out the urls which are in cat 28 first, then look for the ones that are in your category list from that filtered list. Basically something like this:

SELECT shortenified_urls.*
  FROM shortenified_urls
  JOIN linkcats ON linkcats.link_id = shortenified_urls.id
 WHERE linkcats.cat_id IN (17,1,35,33,50)
   AND shortenified_urls.id NOT IN (SELECT link_id AS id
				      FROM linkcats
				     WHERE cat_id = 28)
   AND shortenified_urls.is_social = '1'
   AND shortenified_urls.active = '1'
3

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.