custom_table.sql

CREATE TABLE `ps_edgeusertut` (
 `ID` int(11) unsigned NOT NULL,
 `ut_userid` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `ut_tutorial` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `ut_faved` tinyint(1) DEFAULT NULL,
 `ut_datefaved` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `ut_unlocked` tinyint(1) DEFAULT NULL,
 `ut_dateunlocked` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 `ut_watched` tinyint(1) DEFAULT NULL,
 `ut_watch_history` text COLLATE utf8mb4_unicode_520_ci NOT NULL,
 PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_520_ci

-- custom table to store unlinked (ie not wordpress meta data) meta data about a user and tutorial
-- if no record exists user has not interacted with the tutorial
-- record created/updated if user Favourites the tutorial (ut_faved, ut_datefaved are set accordingly)
-- record created/updated if user watches tutorial (ut_watched, ut_watch_history are set accordingly)
-- ut_watched is either NULL or TRUE, ut_watched_history is a serialised array of dates the tutorial was watched

pure_sql_test.sql

-- returns only existing records in ut_edgeusertut
SELECT ut.ID,
  wpps_posts.post_title,
  ut.ut_userid,
  ut.ut_tutorial,
  ut.ut_watched 
FROM wpps_posts 
  LEFT OUTER JOIN ps_edgeusertut ut ON wpps_posts.id = ut.ut_tutorial
WHERE wpps_posts.post_type = 'tutorial'
  AND (ut.ut_userid = 13 OR ut.ut_userid IS NULL);
  
-- returns all tutorials (wpps_posts records) with NULL values for any non-existent ps_edgeusertut records
SELECT ut.ID,
  wpps_posts.post_title,
  ut.ut_userid,
  ut.ut_tutorial,
  ut.ut_watched 
FROM wpps_posts 
  LEFT OUTER JOIN ps_edgeusertut ut ON wpps_posts.id = ut.ut_tutorial
    AND (ut.ut_userid = 13 OR ut.ut_userid IS NULL)
WHERE wpps_posts.post_type = 'tutorial';

-- returns same as above but with NULL for all ut_watched - even those that are 1/true
-- I would expect this to return only rows where ut_watched is null
-- tried various versions of this (ut_watched <> 1 etc) to no avail
SELECT ut.ID,
  wpps_posts.post_title,
  ut.ut_userid,
  ut.ut_tutorial,
  ut.ut_watched 
FROM wpps_posts 
  LEFT OUTER JOIN ps_edgeusertut ut ON wpps_posts.id = ut.ut_tutorial
    AND (ut.ut_userid = 13 OR ut.ut_userid IS NULL)
    AND ut.ut_watched IS NULL
WHERE wpps_posts.post_type = 'tutorial';