產生有向性圖狀關連（ MySQL + Graphviz ）

DB schema

user_id , from_user_id , kind

過程：先定義 user_id < from_user_id + way ( 1 = 正向 , 2 = 反向 )，進行 GROUP 來做 clear 重複關連的動作，外面的 SELECT 再 sum 一次，如果 1 & 2 都有時變成 3 雙向，即為所求

SELECT kind , user_id , from_user_id , SUM(way) AS way FROM (
  SELECT
    kind ,
    IF(user_id < from_user_id , user_id , from_user_id) AS user_id ,
    IF(user_id < from_user_id , from_user_id , user_id) AS from_user_id ,
    IF(user_id < from_user_id , 1 , 2) AS way
  FROM tracks
  WHERE user_id IS NOT NULL AND from_user_id IS NOT NULL
  GROUP BY
    kind ,
    IF(user_id < from_user_id , user_id , from_user_id) ,
    IF(user_id < from_user_id , from_user_id , user_id) ,
    IF(user_id < from_user_id , 1 , 2)
) AS t GROUP BY kind , user_id , from_user_id

之後 Graphviz 就很簡單，圖要用 digraph(有箭頭)

way(1) : A -> B
way(2) : B -> A
way(3) : A -> B [dir="both"]

最後，類似這種解法一定要 1+ 開頭，因為 0 + 1 = 1 ([0,1] = 2 status) , 1 + 2 = 3 ([1,2,3] = 3 status)