Your opinion on the best way to go about this?

schwim

Heya everyone!

I'm building a social content sharing site. People can share videos, text and images. Anyone can submit a link and as long as it's legitimate content, a moderator will clean up the submission, adding the proper categories and flip the switch to live. There will be a lot of content and I don't imagine anyone would want to click through every single link on the site so I have set it up to where they can view all, by category or content considered to fall into their favorite categories. This is the part I've not figured out and need some input on.

A link can have as many categories that are applicable. If you view a video of a cat playing with a dog, it could have the categories cute, funny, cat, dog, animals assigned to it. When the user views votes on, or submits a link the cat IDs belonging to that link get inserted with the user_id to build a list of that user's favorite categories. If they view, vote up or submit a lot of links with the cat category assigned, it's going to be the highest rated cat in their favecats list.

I've tried to value some things over others.

Viewing a link: +1 to favecats, since clicking the link doesn't necessarily mean they like it.
Voting a link up or down: + or -5 points.
Submitting a link: +10 points

so after using the site for a while, they could end up with a table that looks like this:

uid catid count
2 4 172
2 31 97
2 71 86
2 56 62
2 34 23
2 73 01
2 234 -42
2 25 -163

Now we know their top 5 favorite cats and we also know their least favorite cats, thanks to their down-voting of content.

The code up to this point is in place. I know what the user's most and least favorite cats are. The issue I'm having a problem deciding on how to handle is displaying links determined to fall into the user's favorite categories.

Should I just do SQL query "WHERE (cat_id = 'favorite1' OR cat_id = 'favorite2' OR cat_id = 'favorite3') AND (cat_id != 'leastfave1' OR cat_id != 'leastfave2' OR cat_id != 'leastfave3')"? This is truly the only way I've figured out how to handle it but would love your thoughts on how I might be able to improve on this system before I implement it.

More info available if needed.

Thanks for your time!

NogDog

... WHERE cat_id IN(2, 5, 7, 21) ...

You can use PHP's implode() to build the comma-separated list.

sneakyimp

Firstly, COUNT is a reserved word in SQL. You might want to have a different name for this column.

First, you'd want to find the top categories for some person. Assuming you want uid=2 user's top 5 categories, something like this:

SELECT cat_id FROM user_categories WHERE uid=2 ORDER BY `count` DESC limit 5

Then, you'd want to take those results and create an IN statement as NogDog suggested.

Or -- I'm not sure if this will work -- you might consider trying a subquery:

SELECT * FROM category_table WHERE cat_id IN (SELECT cat_id FROM user_categories WHERE uid=2 ORDER BY `count` DESC limit 5)

This causes an error on my machine because my slightly older version of MySQL doesn't support LIMIT or ORDER BY in a subquery.

Weedpacket

sneakyimp wrote:
my slightly older version of MySQL doesn't support LIMIT or ORDER BY in a subquery.

Wow, when does it date from? The 1990s?!

Derokorian

Weedpacket;11045655 wrote:
Wow, when does it date from? The 1990s?!

5.7 does not support this feature. See http://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html

Weedpacket

Derokorian;11045661 wrote:
5.7 does not support this feature. See http://dev.mysql.com/doc/refman/5.7/en/subquery-restrictions.html

From the Changelog for PostgreSQL 7.1, under "Bug Fixes":

Tom wrote:
Allow ORDER BY, LIMIT in subqueries

Release date: 2001-04-13

I need a :smug: emoticon. ����

Derokorian

Weedpacket;11045665 wrote:
From the Changelog for PostgreSQL 7.1, under "Bug Fixes":

Release date: 2001-04-13

I need a :smug: emoticon. ����

Oh its allowed, just not for all types of operations on said sub-queries. 🙂

sneakyimp

Weedpacket;11045665 wrote:
From the Changelog for PostgreSQL 7.1, under "Bug Fixes":

Release date: 2001-04-13

I need a :smug: emoticon. ����

You pedantic curmudgeon!

This was my query:

SELECT * FROM car_data WHERE vid IN (SELECT vid FROM `car_data` ORDER BY price DESC LIMIT 5)

This is the error:

#1235 - This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

phpMyAdmin reports this version for MySQL:

Server version: 5.5.41-0ubuntu0.12.04.1

schwim

I got the dreaded

This version of MySQL doesn't yet support 'LIMIT & IN/ALL/ANY/SOME subquery'

( Server version: 5.5.42-cll - MySQL Community Server (GPL) ) so I have to go the second query route.

So I have made some progress but have outsmarted myself and messed up somewhere.

Here is my code:

	/* Schwim's automatically generated favorite content!  HOLY COW, THIS IS EXCITING! */

$q_fc = "SELECT cat_id FROM favecats WHERE user_id='$viewing_user_id' ORDER BY score DESC LIMIT 5";
$r_fc = mysqli_query ($link, $q_fc) or die('Catastrophic failure [Super secret code 837]');
$n_fc = mysqli_num_rows($r_fc);
if($n_fc == 0){
	/* This guy has no favorites.  He therefore can't view his favorites. */
	$no_faves = '1';
	include("./includes/page-my-filter.php");
	exit;
}else{
	$favecat_id = array();
	while ($row_fc = mysqli_fetch_assoc ($r_fc)) {
		$favecat_id[] = $row_fc['cat_id'];
	}
	$favecat_ids = implode(",", $favecat_id);
	/* Most hated categories.  If they bothered to downvote **** enough to get to a negative score, we're going to prune links in these cats */
	$q_lfc = "SELECT cat_id FROM favecats WHERE user_id='$viewing_user_id' AND score < '0' ORDER BY score ASC";
	$r_lfc = mysqli_query ($link, $q_lfc) or die('Catastrophic failure [Super secret code 348]');
	$n_lfc = mysqli_num_rows($r_lfc);
	if($n_lfc == 0){
		$unfaves = '';
	}else{
		$unfavecat_id = array();
		while ($row_lfc = mysqli_fetch_assoc ($r_lfc)) {
			$unfavecat_id[] = $row_lfc['cat_id'];
		}
		$unfavecat_ids = implode(",", $unfavecat_id);
		$unfaves = " AND linkcats.cat_id NOT IN ($unfavecat_ids)";
	}

	$q_urls = "
	SELECT shortenified_urls.*
	FROM shortenified_urls 
		LEFT JOIN linkcats 
			ON linkcats.link_id = shortenified_urls.id
		WHERE
			linkcats.cat_id	IN ($favecat_ids)
		".$unfaves."
		AND shortenified_urls.is_social = '1'
		AND shortenified_urls.active = '1'
		GROUP BY shortenified_urls.id
		ORDER BY date_added DESC
	";
}

And here's a resulting query:

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.id
ORDER BY date_added DESC

Here's how the links work. They can have more than one category assigned to them, so a cat playing a kazoo could have animals, cat, cute and trending for categories. I have set up the system to not only figure out their favorite categories, but also their most hated categories. If you look in the query, my intention was to have the query show their favorites (IN) but only if they don't show up in their unfaves(NOT IN).

This query, however shows the link that has cat_id of 28 assigned to it.

In my head, I can see a fuzzy shape of the problem. If the link has four categories assigned to it, it matches 3 times, but not the fourth(28), but since it's GROUP BY, it's still showing up. Is this correct? If it is correct, is there a way for me to stop this from happening? A subquery perhaps? WHERE cats <> 28 ( WHERE cats = these)? Can I get around the error for limits and such with the extra code I've written?

sneakyimp

Might that particular link be also associated with one of the other categories (17, 1, 35, 33, 50) ? Right now the way the query works is that gets EVERYTHING from the shortenified_urls table so long as those urls somehow connect to a cat_id of 17, 1, 35, 33. or 50.

I'm not really sure how to explain this...your LEFT JOIN query will fetch multiple shortenified_urls records for each url-linkcat association that exists. If a single url corresponds to categories 17, 1, 34, 33, 50, AND 28 then your WHERE clause will exclude the one record in your join that connects to cat_id 28, but the url will still surive the WHERE clause thanks to its association with the five categories to which it is connected. Keep in mind that a LEFT JOIN will a) grab everything from the first table whether something matches in the second table or not and b) will grab multiple instances of a given record in the first table if it matches more than one record in the second table.

Perhaps you should start your query with the link cats table and then join the shortenified_urls table to that instead?

I'd also recommend using table aliases so you don't have to type out the whole table name every time.

Maybe something like this:

SELECT DISTINCT u.* FROM
links cats lc
LEFT JOIN shortenified_urls u ON (u.id=lc.link_id AND u.is_social='1' AND u.active='1')
WHERE lc.cat_id IN (17,1,35,33,50) AND lc.cat_id NOT IN (28)
ORDER BY u.date_added DESC

No idea if that'll work. The basic approach I took is to work with the table we hope to constrain first and then only JOIN urls that match the categories which make it through the screen.

If it doesn't, consider trying to reconcile the faves and unfaves arrays in PHP before formulating your sql statement. You'll probably find the [man]array_diff[/man] function helpful.

schwim

Hi there sneakyimp,

I tried the query:

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

And it looks like it supplied an identical set of records from the query before it, including those with the unfave category. Should I be somehow moving that NOT IN forward in the query so it will cull those numbers before it gets to the part where it pulls the resulting links?

I don't know if it makes sense, but the query looks to me like it's giving the NOT IN's after it's grabbed the other data.

sneakyimp

schwim;11045695 wrote:
And it looks like it supplied an identical set of records from the query before it, including those with the unfave category. Should I be somehow moving that NOT IN forward in the query so it will cull those numbers before it gets to the part where it pulls the resulting links?

Oh dang I see today that the query I suggested doesn't solve the problem I'm trying to describe. The NOT IN stuff's exact place in the query is not critically important. Both of us are properly excluding categories. The problem is that certain links are getting included because they are connected to categories that ARE included. It's sort of like trying to ban anyone from your party who has any association with your enemy AL. ANNE shows up at your party with AL and your bouncer tells them both to get lost. But then your bouncer forgets ANNE's name and ANNE comes back later with BILL and CHARLIE who are both on the faves list. ANNE still gets in even though she is good friends with AL.

I think you'll need to add yet another LEFT JOIN to the linkcat table for the unfaves and exclude any records for which this JOIN IS NOT NULL. Basically, you'll need to remove ANY link where there's a connection between that link and any banned cat_id.

SELECT DISTINCT u.* FROM
linkcats lc
LEFT JOIN shortenified_urls u ON (u.id=lc.link_id AND u.is_social='1' AND u.active='1')
LEFT JOIN linkcats lc2 ON  (u.id=lc2.link_id AND lc2.cat_id IN (28))
WHERE
  lc.cat_id IN (17,1,35,33,50)
  AND lc.cat_id NOT IN (28)
  AND lc2.link_id IS NULL
ORDER BY u.date_added DESC

Or something like that...

schwim

Hi there sneaky,

I was unable to get your last suggestion to work, but a couple guys had offered up suggestions while I got my beauty sleep. Two queries that result in a slightly different count. I'm trying to manipulate the linkcats records to find out which one is showing closer to the true result, but wanted to ask you what you thought of them and which you would prefer if you were dealing with 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'

and

SELECT u.*
FROM shortenified_urls AS u
WHERE u.is_social = '1'
  AND u.active = '1'
  AND EXISTS
      ( SELECT 1
        FROM linkcats AS lc
        WHERE lc.link_id = u.id
          AND lc.cat_id IN (17,1,35,33,50)
      ) 
  AND NOT EXISTS
      ( SELECT 1                                  

        FROM linkcats AS lc 
        WHERE lc.link_id = u.id
          AND lc.cat_id IN (28)
      )  ;

Right now, I've got the latter running so I can work on the function, but am trying to figure out why they're showing different results( the second shows less than the first).

sneakyimp

Oh man I don't know. It's tricky to decide when one doesn't have data. If we use aliases in the first, they look even more similar:

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

To me, this approach looks slightly more correct and efficient than the other one, but it really comes down to which one reflects a more accurate result set. When I say "correct" I refer to the fact that we at least attempt to limit the number of shortenified_urls to the right categories using a JOIN before trying to eliminate some of those urls via a subquery. I'm a bit suspicious of subqueries. I've found them to be easier to understand in the past, but less efficient in practice. That second query you posted just grabs ALL Your urls (which could be a vast number) and then tries to limit these with a few ANDed conditions.

As to why the results are different, I'm not really sure. I do not know myself whether the tables referred to in a subquery have any actual relation to the tables referred to in the primary query. As I mentioned before, I'm a bit suspicious of subqueries. I wonder how that second query is any different than this one?

SELECT u.*
FROM shortenified_urls AS u
WHERE u.is_social = '1'
  AND u.active = '1'
  AND EXISTS
      (
        SELECT ROUND(RAND())
      ) 
  AND NOT EXISTS
      ( SELECT 1
        SELECT ROUND(RAND())
      )  ;

I apologize if I'm not more helpful. I usually solve this kind of problem through trial and error and semi-clever data construction rather than any great mastery of SQL concepts.

You may want to consider trying to get my last JOIN to work. I think it would be both more concise and more efficient.

schwim

Hi there sneakyimp,

I took your advice and continued working with your last suggested query and think I figured out what I had done wrong. It was an issue I created in modifying the query for when someone didn't have any negatively scored categories. I tested all three queries. The singlejoin query was bringing in duplicate records. The sub query looked like it was doing ok until I went to the oldest record. For some reason it was including a link showing completely null values.

So, your query, modified as I needed. Let me know if you see anything amiss.

If all they have is favorites, it will look like this:

SELECT DISTINCT u.* FROM
linkcats lc
LEFT JOIN shortenified_urls u ON (u.id=lc.link_id AND u.is_social='1' AND u.active='1')

WHERE
  lc.cat_id IN (17,1,35,33,50)

ORDER BY u.date_added DESC

and when they have some unfavorites, it looks like this:

SELECT DISTINCT u.* FROM
linkcats lc
LEFT JOIN shortenified_urls u ON (u.id=lc.link_id AND u.is_social='1' AND u.active='1')

		LEFT JOIN linkcats lc2 ON  (u.id=lc2.link_id AND lc2.cat_id IN (28))

WHERE
  lc.cat_id IN (17,1,35,33,50)

		AND lc.cat_id NOT IN (28)
		AND lc2.link_id IS NULL

ORDER BY u.date_added DESC

These are for viewing only unread content. There's two ways that the system determines this. They can mark all as read, creating a timestamp. Anything older than the timestamp is hidden. The second method is in the linkviews table. In my other queries it's a left join, so I'm trying to attach a third to your code. Here's what I've done:

With no unfaves and viewing unread only:

SELECT DISTINCT u.* FROM
	linkcats lc
	LEFT JOIN shortenified_urls u ON (u.id=lc.link_id AND u.is_social='1' AND u.active='1')


	LEFT JOIN linkviews lv
	ON lv.lid = u.id 
	AND lv.uid = 2

WHERE
	lc.cat_id IN (17,1,35,33,50)


	AND u.date_added > 0
	AND lv.id IS NULL

ORDER BY u.date_added DESC

With unfaves and unread only:

SELECT DISTINCT u.* FROM
	linkcats lc
	LEFT JOIN shortenified_urls u ON (u.id=lc.link_id AND u.is_social='1' AND u.active='1')

		LEFT JOIN linkcats lc2 ON  (u.id=lc2.link_id AND lc2.cat_id IN (17))


	LEFT JOIN linkviews lv
	ON lv.lid = u.id 
	AND lv.uid = 2

WHERE
	lc.cat_id IN (1,35,33,50,2)

		AND lc.cat_id NOT IN (17)
		AND lc2.link_id IS NULL


	AND u.date_added > 1424630215
	AND lv.id IS NULL

ORDER BY u.date_added DESC

I've tested as much as I am able and they all seem to be working. Unless you have a suggestion on handling my additions better, it looks like these may be what I run with.

Thanks so much for all your help!

sneakyimp

I can't comment much on the addition of the linkviews table as I don't know what a value of 2 means, but the JOIN looks about right and checking for NULL is certainly the type of check you'd want. Required lv.id IS NULL means that there are no matching linkview records with the various other criteria you have specified.

If it's working, that's what really matters. I try as often as I can to check things with proof-of-concept data that makes sure it does what I want. It's also important to check boundary conditions (e.g., no favorites at all, one favorite, many favorites, no unfaves, one unfave, many unfaves, etc).

As for efficiency, it looks pretty tidy to me, but you'll need to make sure you have appropriate indexes on all the necessary fields. Maybe write a script to generate a huge number of links, categories, and views and see how it responds when you have a sizeable amount of data.

schwim

sneakyimp;11045783 wrote:
I can't comment much on the addition of the linkviews table as I don't know what a value of 2 means, but the JOIN looks about right and checking for NULL is certainly the type of check you'd want. Required lv.id IS NULL means that there are no matching linkview records with the various other criteria you have specified.

Linkviews is another very simple table in which a record gets inserted when a member views a link, inserting the user's id and link id. That way I have a way to keep a record of all links a particular user has viewed, so I can hide previously viewed if they haven't chosen to mark all as read at a certain timestamp.

sneakyimp;11045783 wrote:
If it's working, that's what really matters. I try as often as I can to check things with proof-of-concept data that makes sure it does what I want. It's also important to check boundary conditions (e.g., no favorites at all, one favorite, many favorites, no unfaves, one unfave, many unfaves, etc).

As for efficiency, it looks pretty tidy to me, but you'll need to make sure you have appropriate indexes on all the necessary fields. Maybe write a script to generate a huge number of links, categories, and views and see how it responds when you have a sizeable amount of data.

If I posted the structure, would you be able to help me determine what should be uniques, indexes and the like? I've read quite a bit on how it works, but I still don't have a good grasp on how it works.

sneakyimp

I might be of some assistance, but I am not exactly the SQL-index-creation master either. I typically make all my indexes using phpMyAdmin. I also am somewhat baffled by multi-column indexes so all my indexes tend to be on a single column only.