recommend.sql

BEGIN

  DECLARE cur CURSOR FOR 
    SELECT Y.job_id as job_id
    FROM (
      SELECT job_id, similar_factor
      FROM (SELECT js2.user_id as user_id, COUNT(*) as similar_factor
            FROM JOB_STATUS as js JOIN JOB_STATUS as js2 ON (js.job_id = js2.job_id AND js.user_id <> js2.user_id)
            WHERE js.user_id = my_user_id
            GROUP BY js2.user_id
            ORDER BY COUNT(*) DESC) as X 
            JOIN 
            JOB_STATUS as js ON X.user_id = js.user_id
      GROUP BY job_id, similar_factor
      ORDER BY similar_factor
    ) as Y JOIN JOB as j ON (Y.job_id = j.id) JOIN COMPANY as c ON (j.company_id = c.id)
    WHERE Y.job_id NOT IN (
      SELECT DISTINCT myJs.job_id FROM JOB_STATUS as myJs WHERE user_id = my_user_id
    );


IF my_job_id > 0 THEN
  SELECT jt.job_id as job_id, c.name as company_name, j.title as job_title
  FROM JOB_TAG as jt, JOB as j, COMPANY as c
  WHERE jt.tag_id IN (
    SELECT tag_id
    FROM JOB_TAG
    WHERE job_id = my_job_id
  ) AND jt.job_id != my_job_id
  AND jt.job_id = j.id AND j.company_id = c.id
  GROUP BY jt.job_id
  HAVING COUNT(jt.tag_id) >= 1
  ORDER BY COUNT(jt.tag_id) DESC
  limit 10;
ELSEIF my_user_id <= 0 THEN
  (SELECT jt.job_id as job_id, c.name as company_name, j.title as job_title FROM `JOB_STATUS` as jt, `JOB` as j, `COMPANY` as c WHERE jt.job_id = j.id AND c.id = j.company_id GROUP BY jt.job_id ORDER BY count(1) DESC LIMIT 5)
  UNION
  (SELECT jt.job_id as job_id, c.name as company_name, j.title as job_title FROM `JOB_STATUS` as jt, `JOB` as j, `COMPANY` as c WHERE jt.job_id = j.id AND jt.job_id AND c.id = j.company_id GROUP BY jt.job_id ORDER BY count(jt.create_at) DESC LIMIT 5);
ELSE
  
  OPEN cur;
  BEGIN
      DECLARE curr_job_id INT;
      DECLARE exist_history BOOLEAN DEFAULT FALSE;
      DECLARE exit_flag BOOLEAN DEFAULT FALSE;
      DECLARE CONTINUE HANDLER FOR NOT FOUND SET exit_flag=True;

      cloop: LOOP    
      IF exit_flag THEN
          LEAVE cloop;
      END IF;
      FETCH cur INTO curr_job_id;

      SELECT COUNT(*) INTO exist_history FROM RECOMMEND_HISTORY as rh WHERE rh.job_id = curr_job_id;
    	IF exist_history <= 0 THEN
        INSERT IGNORE INTO RECOMMEND_HISTORY (job_id, recommend_count) VALUES (curr_job_id, 0);
      END IF;
      UPDATE RECOMMEND_HISTORY as rh SET recommend_count = recommend_count + 1 WHERE rh.job_id = curr_job_id;
      END LOOP cloop;
  END;
  CLOSE cur;

  SELECT Y.job_id as job_id, c.name as company_name, j.title as job_title
  FROM (
    SELECT job_id, similar_factor
    FROM (SELECT js2.user_id as user_id, COUNT(*) as similar_factor
          FROM JOB_STATUS as js JOIN JOB_STATUS as js2 ON (js.job_id = js2.job_id AND js.user_id <> js2.user_id)
          WHERE js.user_id = my_user_id
          GROUP BY js2.user_id
          ORDER BY COUNT(*) DESC) as X 
          JOIN 
          JOB_STATUS as js ON X.user_id = js.user_id
    GROUP BY job_id, similar_factor
    ORDER BY similar_factor
  ) as Y JOIN JOB as j ON (Y.job_id = j.id) JOIN COMPANY as c ON (j.company_id = c.id)
  WHERE Y.job_id NOT IN (
    SELECT DISTINCT myJs.job_id FROM JOB_STATUS as myJs WHERE user_id = my_user_id
  );
  
  
END IF;
END