Having trouble combining two queries

schwim

Hi there everyone!

I'm trying to write a function that will show only unread links to someone if they choose. The two ways I'm determining if it's been read is a "mark all" unix time if they click the "Mark All Read" link. Newer links will then be checked against a linkviews table, which gets an insertion of the uid and lid when someone views a link. Here's the primary query that actually grabs links:

	$q_mas = "SELECT * FROM shortenified_urls WHERE date_added > '$vu_mark_as_read' ORDER BY date_added DESC";

in this table:
link ID: link_id

But I need it to also filter out any that have a match of the $link and user id. Below, I've written a query to find those matches, but if it finds a match, I don't want to show the result, so actually, I need the reverse to happen. If no match is found and the date_added from above is newer than their mark as read flag, then it needs to show the link.

	$q_unr = "SELECT * FROM linkviews WHERE lid = '$link_id' AND uid = '$viewing_user_id'";

in this table:
link ID: lid
user ID: uid

It's confusing for me to even try to explain. The first query is really what I'm trying to get from the db and the second query would actually be finding records of all the links they read. I just didn't know how to write what I actually want, that being newer than mark as read in primary and no record found for lid <-> uid in the linkviews table.

Any help in figuring this out would be greatly appreciated. Thanks for your time!

schwim

Well, I think I might have figured it out. At least it seems to be working:

if($vu_unread_only == '1'){
	$q_urls = "SELECT * FROM shortenified_urls
	WHERE shortenified_urls.date_added > $vu_mark_as_read
	AND NOT EXISTS (
		SELECT lid FROM linkviews
		WHERE lid = shortenified_urls.id AND uid = $viewing_user_id
	)
	AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
	ORDER BY ".$sortBy." ".$sortOrder
	;
}

I'll do a bit more testing before I declare it a success.

schwim

I knew it was too early to cheer.

I've got two filtering methods, one where only unread, which I seem to have figured out above and then only in a particular category, which also is working.

Unread only:

$q_urls = "SELECT * FROM shortenified_urls
	WHERE shortenified_urls.date_added > $vu_mark_as_read
	AND NOT EXISTS (
		SELECT lid FROM linkviews
		WHERE lid = shortenified_urls.id AND uid = $viewing_user_id
	)
	AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
	ORDER BY date_added DESC"
	;

Only in a particular category:

$q_urls = "
	SELECT * FROM shortenified_urls
	WHERE shortenified_urls.primary_cat = $vu_cat_view
	OR EXISTS (
		SELECT link_id FROM linkcats
		WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view
	)
	AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
	ORDER BY date_added DESC"
	;

But I also need to filter by unread in a particular category, effectively combining the two. I tried doing that with the following:

$q_urls = "
	SELECT * FROM shortenified_urls
	WHERE shortenified_urls.primary_cat = $vu_cat_view AND shortenified_urls.date_added > $vu_mark_as_read
	OR EXISTS (
		SELECT link_id FROM linkcats
		WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view
	)
	AND NOT EXISTS (
		SELECT lid FROM linkviews
		WHERE lid = shortenified_urls.id AND uid = $viewing_user_id
	)
	AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
	ORDER BY date_added DESC"
	;

But this seems to still be giving me all links in a specific category. Unread status doesn't seem to be factoring into the query properly. Here's an echo of the executed query:

SELECT * FROM shortenified_urls
WHERE shortenified_urls.primary_cat = 17 AND shortenified_urls.date_added > 0
OR EXISTS (
SELECT link_id FROM linkcats
WHERE link_id = shortenified_urls.id AND cat_id = 17
)
AND NOT EXISTS (
SELECT lid FROM linkviews
WHERE lid = shortenified_urls.id AND uid = 2
)
AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
ORDER BY date_added DESC

It would seem I need to combine the first two parts together, bracketing them together, but I'm not sure how to do that. Could someone help me with what I'm doing wrong? I can't seem to find the error of my ways.

Thanks for your time!

schwim

Well, my thought of bracketing the first two didn't quite work out as I expected:

	$q_urls = "
	SELECT * FROM shortenified_urls
	WHERE EXISTS (SELECT * FROM shortenified_urls WHERE shortenified_urls.primary_cat = $vu_cat_view AND shortenified_urls.date_added > $vu_mark_as_read
	OR EXISTS (
		SELECT link_id FROM linkcats
		WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view
	))
	AND NOT EXISTS (
		SELECT lid FROM linkviews
		WHERE lid = shortenified_urls.id AND uid = $viewing_user_id
	)
	AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
	ORDER BY date_added DESC"
	;

That seems to give me unread, but in any category. Kind of the opposite of my first attempt.

schwim

I've found a bit more out about my problem.

If the Primary cat is the category matched, it shows regardless of whether it's marked as read. If, however, it's a secondary category that the match was made on, it disappears properly when it's viewed.

    $q_urls = "
    SELECT * FROM shortenified_urls
    WHERE EXISTS (SELECT * FROM shortenified_urls WHERE shortenified_urls.primary_cat = $vu_cat_view AND shortenified_urls.date_added > $vu_mark_as_read // This part is persisting in spite of read status
    OR EXISTS (
        SELECT link_id FROM linkcats
        WHERE link_id = shortenified_urls.id AND cat_id = $vu_cat_view // This part is honoring whether it's been read or not.
    ))
    AND NOT EXISTS (
        SELECT lid FROM linkviews
        WHERE lid = shortenified_urls.id AND uid = $viewing_user_id
    )
    AND shortenified_urls.is_social = '1' AND shortenified_urls.active = '1'
    ORDER BY date_added DESC"
    ;

I would love some insight on what I'm doing wrong, but am plugging away.

Derokorian

Sub-queries are the devil's play thing. You'll want to do a left join and look for null in the where clause probably. Reading your posts I'm not entirely sure of you data structure, but something like this might be closer to what you want:

SELECT shortenified_urls .* 
FROM shortenified_urls 
  JOIN linkcats 
    ON linkcats.link_id = shortenified_urls.id
  LEFT JOIN linkviews 
    ON linkviews.lid = shortenified_urls.id
    AND linkviews.uid = $viewing_user_id
WHERE shortenified_urls.primary_cat = $vu_cat_view 
  AND shortenified_urls.date_added > $vu_mark_as_read
  AND linkcats.cat_id = $vu_cat_view
  AND shortenified_urls.is_social = '1' 
  AND shortenified_urls.active = '1'
  AND linkviews.some_column_other_than_lid_and_uid IS NULL
    ORDER BY date_added DESC

I'm not exactly sure because I don't know your tables and data but generally speaking, if you're using sub-queries you should rethink as it can probably be achieved through joins which are far more performant. If you're having issues, you might try using sqlfiddle.com to set up a sample structure with some sample data.

schwim

Thanks very much for your help Derokorian. I plugged in your query (altering the some column) and got a return of 0 results. I tried to use the site you linked to share the structure and a little bit of data, but it keeps telling me it's too large. I've never used sqlfiddle, so I might just be trying to enter it incorrectly. I'm completely ignorant of joins so all my attempts have been sub-queries, so I won't space on posting them here(none of them worked and most worked worse)

Here's the structure with a few inserts for example:

CREATE TABLE IF NOT EXISTS linkcats (
link_id int(10) NOT NULL,
cat_id int(4) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO linkcats (link_id, cat_id) VALUES
(3, 27),
(5, 9),
(5, 23),
(3, 24),
(3, 15),
(3, 20),
(4, 32),
(4, 36),
(2, 30),
(2, 17),
(6, 3),
(7, 49),
(8, 17),
(9, 5),
(9, 17),
(10, 33),
(10, 31),
(10, 21),
(11, 1),
(11, 2),
(11, 3),
(12, 17),
(14, 20),
(14, 38),
(19, 1),
(14, 34),
(19, 17),
(19, 2),
(17, 15),
(17, 33),
(18, 33),
(18, 31),

CREATE TABLE IF NOT EXISTS linkviews (
id int(20) NOT NULL auto_increment,
lid int(10) NOT NULL,
uid int(10) NOT NULL,
ip varchar(24) NOT NULL,
sid char(32) NOT NULL,
date int(11) NOT NULL,
PRIMARY KEY (id)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=27 ;

INSERT INTO linkviews (id, lid, uid, ip, sid, date) VALUES
(1, 81, 2, '66.119.108.54', 'f14243cf2f28e496f4794dd86c673d4e', 1412904358),
(2, 82, 2, '66.119.108.54', 'f14243cf2f28e496f4794dd86c673d4e', 1412905516),
(3, 14, 7, '180.76.5.187', '2956dd6a395d53544ffb1df97c8e45b9', 1412923056),
(4, 34, 17, '66.249.69.71', '3121eee0f54e42b34088b7944e2b9c7f', 1412928169),
(5, 6, 17, '66.249.69.71', '3121eee0f54e42b34088b7944e2b9c7f', 1412933215),

CREATE TABLE IF NOT EXISTS shortenified_urls (
id int(20) NOT NULL auto_increment,
short_string varchar(24) NOT NULL,
url varchar(400) character set utf8 collate utf8_bin NOT NULL,
via_title varchar(200) character set utf8 collate utf8_bin NOT NULL,
via_url varchar(400) NOT NULL,
embed tinyint(4) NOT NULL default '0',
custom_embed varchar(2048) NOT NULL,
embed_type varchar(25) NOT NULL,
vid_string varchar(25) NOT NULL,
primary_cat smallint(3) NOT NULL,
title varchar(1024) NOT NULL,
short_description varchar(200) NOT NULL,
description varchar(4096) NOT NULL,
keywords varchar(4096) NOT NULL,
owner varchar(25) NOT NULL,
owner_sid char(32) NOT NULL,
owner_ip varchar(25) NOT NULL,
date_added int(11) unsigned NOT NULL,
date_modified int(11) unsigned NOT NULL default '0',
impressions int(40) NOT NULL default '0',
comments int(12) NOT NULL default '0',
is_social tinyint(1) NOT NULL default '0',
votes_up int(40) NOT NULL default '0',
votes_down int(40) NOT NULL default '0',
requires_mod tinyint(1) NOT NULL default '0',
mod_reason tinyint(1) NOT NULL default '0',
spam tinyint(1) NOT NULL default '0',
active tinyint(1) NOT NULL default '1',
temp_id varchar(50) NOT NULL,
PRIMARY KEY (id),
UNIQUE KEY id (id),
KEY short_string (short_string),
KEY short_string_2 (short_string)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=92 ;

INSERT INTO shortenified_urls (id, short_string, url, via_title, via_url, embed, custom_embed, embed_type, vid_string, primary_cat, title, short_description, description, keywords, owner, owner_sid, owner_ip, date_added, date_modified, impressions, comments, is_social, votes_up, votes_down, requires_mod, mod_reason, spam, active, temp_id) VALUES
(1, 'B', 'https://www.youtube.com/watch?v=dQw4w9WgXcQ', '', '', 0, '', '', '', 3, 'The most heartwarming story you\''ll read this year', 'A touching tale of a topless dancer, her autistic child and an anonymous benefactor', '', 'Rick, Astley, Sony, BMG, Music, UK, Pop', '2', '', '66.119.108.54', 1383605268, 0, 49, 0, 1, 2, 0, 0, 0, 0, 1, ''),
(2, 'C', 'https://www.youtube.com/watch?v=HYIagsCaMlY', '', '', 1, '', 'youtube', 'HYIagsCaMlY', 26, 'Street Fighter II - Drunk Russian Edition', 'Two drunk Russians duke it out Street Fighter style. Hang out for the killer finishing move.', '', '', '2', '', '66.119.108.54', 1384766075, 1411946712, 16, 1, 1, 1, 0, 0, 0, 0, 1, ''),
(3, 'D', 'https://www.youtube.com/watch?v=QK8_63_hlhY', '', '', 1, '', 'youtube', 'QK8_63_hlhY', 48, 'Formula E first race', 'The first Formula Electric race in history was held in Beijing, China. They saved the most exciting part for the last turn before the finish line.', '', '', '2', '', '66.119.108.54', 1386071775, 1411946506, 53, 0, 1, 1, 0, 0, 0, 0, 1, ''),
(4, 'F', 'https://www.youtube.com/watch?v=OCKAscHGxfY', '', '', 1, '', 'youtube', 'OCKAscHGxfY', 17, 'Epic Food Fight', 'World War II might have ended differently if French bread were this lethal.', '', '', '2', '', '66.119.108.54', 1388012506, 1411946606, 32, 0, 1, 1, 0, 0, 0, 0, 1, ''),
(5, 'G', 'https://www.youtube.com/watch?v=4nCIzPuLYJA', '', '', 1, '', 'youtube', '4nCIzPuLYJA', 21, 'Incredible Rube Goldberg device', 'A Rube Goldberg device that literally involves smoke and mirrors.', '', '', '2', '', '66.119.108.54', 1407998675, 1411165518, 31, 3, 1, 1, 0, 0, 0, 0, 1, ''),
(6, 'H', 'https://www.youtube.com/watch?v=3Y6YVLXprII', 'Wimp', 'http://www.wimp.com/meaninglife/', 1, '', 'youtube', '3Y6YVLXprII', 18, 'The Meaning of Life, explained by 3rd Graders', 'A motivational speech designed to get you out of your chair and taking on the day, as read by a 3rd grader.', '', '', '2', '', '66.119.108.54', 1412106072, 1412111882, 47, 0, 1, 1, 0, 0, 0, 0, 1, ''),
(7, 'J', 'http://blogs.windows.com/bloggingwindows/2014/09/30/announcing-windows-10/', 'Slashdot', 'http://tech.slashdot.org/story/14/09/30/1745253/microsoft-announces-windows-10', 0, '', 'FALSE', 'FALSE', 20, 'Microsoft Announces Windows 10', 'Windows 10 has been announced, raising quite a stink in the interwebz with statements like, \"Where is Windows 9?\" and \"Unified Experience? I thought that was what we had Windows 8 for!\"', '', '', '1', '', '66.119.108.54', 1412109499, 1412109763, 23, 0, 1, 1, 0, 0, 0, 0, 1, ''),
(8, 'K', 'https://www.youtube.com/watch?v=n9AmvrTeoXU', 'Ebaum\''s World', 'http://www.ebaumsworld.com/video/watch/84306353/', 1, '', 'youtube', 'n9AmvrTeoXU', 25, 'Lifeboat failure or Win(ure)?', 'I\''m not sure if it\''s the best or worst lifeboat launch on record, but I hope the encapsulated crew had their seatbelts on. And aspirin. Lots and lots of aspirin.', '', '', '1', '', '66.119.108.54', 1412111517, 1412559493, 36, 0, 1, 1, 0, 0, 0, 0, 1, ''),
(9, 'L', 'https://www.youtube.com/watch?v=Hjy6ie16z3s', 'Wimp', 'http://www.wimp.com/feelhome/', 1, '', 'youtube', 'Hjy6ie16z3s', 21, 'Parent makes goth child feel more at home', 'A German home improvement ad answers the question \"What can I do to make my goth child\''s life less stressful?\"\r\n\r\nThe answer? Black paint.', '', '', '1', '', '66.119.108.54', 1412112448, 1412129609, 21, 0, 1, 2, 0, 0, 0, 0, 1, ''),
(10, 'M', 'https://www.youtube.com/watch?v=OBvtGr1BfVQ', 'Ebaum', 'http://www.ebaumsworld.com/video/watch/84305533/', 1, '', 'youtube', 'OBvtGr1BfVQ', 32, 'A homemade backpack flamethrower', 'Wife: Why did you build that?\r\n\r\nHusband: For fun.', '', '', '1', '', '66.119.108.54', 1412113015, 1412129600, 32, 0, 1, 0, 0, 0, 0, 0, 1, ''),

schwim

It seems like maybe your query is messing up where the links should show if it's matched as a secondary category via linkcats. I removed the linkcat lines, like so:

	$q_urls = "
	SELECT shortenified_urls .*
	FROM shortenified_urls
		LEFT JOIN linkviews
			ON linkviews.lid = shortenified_urls.id
		AND linkviews.uid = $viewing_user_id
		WHERE shortenified_urls.primary_cat = $vu_cat_view
		AND shortenified_urls.date_added > $vu_mark_as_read
		AND shortenified_urls.is_social = '1'
		AND shortenified_urls.active = '1'
		AND linkviews.id IS NULL
		ORDER BY date_added DESC
	";

And it showed unread with a primary category match. When I viewed that link, it properly disappeared.

I've also tried changing AND to OR here:

OR linkcats.cat_id = $vu_cat_view

since I want the link to show if the primary_cat equals $vu_cat_view OR if linkcats.cat_id = $vu_cat_view

I'm playing with the removed lines, but TBH, I have absolutely no idea what I'm doing w/joins, so I'm just browsing tutorials and trying stuff.

Derokorian

Hey so progress then? Closer than it was before! I would think you just need to group the or for category, something like this maybe (notice the parenthesis)?

SELECT shortenified_urls .* 
    FROM shortenified_urls 
        LEFT JOIN linkcats 
            ON linkcats.link_id = shortenified_urls.id
        LEFT JOIN linkviews 
            ON linkviews.lid = shortenified_urls.id 
        AND linkviews.uid = $viewing_user_id 
        WHERE 
                (shortenified_urls.primary_cat = $vu_cat_view 
                 OR linkcats.cat_id = $vu_cat_view)
        AND shortenified_urls.date_added > $vu_mark_as_read 
        AND shortenified_urls.is_social = '1' 
        AND shortenified_urls.active = '1' 
        AND linkviews.id IS NULL 
        ORDER BY date_added DESC

schwim

I LOVE BRACKETS!!!

I tested it and it deducted the link from count by $mark_as_read, primary cat and secondary cat. Then a beam of light shown down upon me!

I would like to try to make this a little more helpful. I'd like to add a catmar(marked as read) table, with catid, uid and unixtime entry. I'd like to try to allow the user to mark only a particular cat as read. Can I add a third LEFT JOIN to the query, checking for > $catmar where it exists with the cat_id and uid in question?

In other words, to this query, I would like to also check against a unixtime in catmar where uid = $viewing_user_id and catid = $vu_cat_view and if it finds one, it will act just like $vu_mark_as_read and if it doesn't find it, it would then continue on to $mark_as_read and linkview like it does now.

SELECT shortenified_urls .*
FROM shortenified_urls
LEFT JOIN linkcats
ON linkcats.link_id = shortenified_urls.id
LEFT JOIN linkviews
ON linkviews.lid = shortenified_urls.id
AND linkviews.uid = $viewing_user_id
WHERE
(shortenified_urls.primary_cat = $vu_cat_view
OR linkcats.cat_id = $vu_cat_view)
AND shortenified_urls.date_added > $vu_mark_as_read
AND shortenified_urls.is_social = '1'
AND shortenified_urls.active = '1'
AND linkviews.id IS NULL
ORDER BY date_added DESC