Querying if a user has a log matching the most recent version of a document using MySQL

问题: I am trying to implement a version control system (SOPs) where those documents are revised regularly and track whether users have read the most recent version of an SOP....

问题:

I am trying to implement a version control system (SOPs) where those documents are revised regularly and track whether users have read the most recent version of an SOP.

I can list all SOPs where the user has read the most current version, but I can't list all the SOPs that they are out of date on e.g. for notifications that they need to read them.

The SOP table may contain multiples e.g.

SOP Index   SOP Code    SOP Version
1             AAA               1
2             AAA               2
3             AAA               3
4             BBB               1
5             CCC               1
6             CCC               2

The SOP log looks like this:

SOP Index    User ID     SOP Version
    1           1            1
    2           1            2
    4           1            1

So this shows user 1 has read the latest version of BBB, but has only read v2 of AAA although v3 is available.

I have managed to run a query that displays only the most recent SOP based on its code, e.g. from above 3, AAA, 3 | 4, BBB, 1 | 6, CCC, 2 but I am struggling to work out how to compare this to a separate table which logs the SOP index, user and version number and determine if they have read the most recent version or not.

This selects the most recent SOP:

SELECT a.sop_group, a.sop_code, a.sop_version, a.sop_title, a.sop_issued, 
c.log_sop_version
FROM data_sops a
INNER JOIN (
    SELECT sop_code, MAX(sop_version) sop_version, sop_index
    FROM data_sops
    GROUP BY sop_code) b ON a.sop_code = b.sop_code AND a.sop_version = b.sop_version

I've managed to extend this to select the SOPs that are the latest version the user has read with:

SELECT a.sop_group, a.sop_code, a.sop_version, a.sop_title, a.sop_issued, c.log_sop_version
    FROM data_sops a
    INNER JOIN (
        SELECT sop_code, MAX(sop_version) sop_version, sop_index
        FROM data_sops
    GROUP BY sop_code) b ON a.sop_code = b.sop_code AND a.sop_version = b.sop_version
    LEFT JOIN log_sops c ON a.sop_index = c.log_sop_sop_index
    WHERE c.log_sop_user_index = '$userid' AND c.log_sop_version = a.sop_version

I thought the line WHERE c.log_sop_user_index = '$userid' AND c.log_sop_version = a.sop_version could change operator from = to <> to give the opposite effect, but it doesn't seem to be that simple and 3 tables worth of joins in, I'm struggling to figure it out!


回答1:

Your query should be like this to figure out which version your user has not read yet.

select data_sops.sop_index,data_sops.sop_code,data_sops.sop_version
from data_sops 
join (SELECT sop_code, MAX(sop_version) sop_version 
          FROM data_sops GROUP BY sop_code) a 
on data_sops.sop_code = a.sop_code 
and data_sops.sop_version = a.sop_version 
where not exists 
    (select log_sop_sop_index from log_sops 
     where data_sops.sop_index = log_sop_sop_index 
     and log_sop_user_index = '$userid')
  • 发表于 2019-01-22 00:25
  • 阅读 ( 192 )
  • 分类:网络文章

条评论

请先 登录 后评论
不写代码的码农
小编

篇文章

作家榜 »

  1. 小编 文章
返回顶部
部分文章转自于网络,若有侵权请联系我们删除