株式会社エス・スリー・フォー

はじめてのDBTools.h++ (part-4)

JOIN と WHERE

SQLによるテーブルの検索の際、多くの場合検索条件をWHERE節に記述します。

たとえば2つのテーブル:

person
name string 名前
addr_id integer 住所ID
address
pref string 都道府県
addr_id integer 住所ID

があり、'大阪府'に住む人を探し出すSQLは

  SELECT person.name
  FROM   person, address
  WHERE  person.addr_id = address.addr_id
         AND
         address.pref = '大阪府'

となるでしょう。

ここで、WHERE節に指定したふたつの条件文はそれぞれ別の意味を持っています。
すなわち、

person.addr_id =address.addr_id
は、ふたつのテーブルpersonとaddressをひとつのテーブルに結合する条件であり、

address.pref = '大阪府'
は、結合されたテーブルから列を得るための検索条件です。

結合条件と検索条件をひとつのWHERE節に混在させるより、JOINによる結合をFROM節に記述することで、WHERE節には検索条件だけが残り、すっきりしたSQL文となります。

  SELECT person.name
  FROM   person INNER JOIN address
         ON person.addr_id = address.addr_id
  WHERE  address.pref = '大阪府'

こう書けば、"addr_idで結合されたテーブルから'大阪府'を見つけ出す"という検索条件がはっきりします。DBTools.h++で書けば以下のようになります。

  RWDBDatabase db = ...;
  RWDBTable person = db.table("person");
  RWDBTable address = db.table("address");
  RWDBSelector selector = db.selector();
  selector << person["name"];
  selector.from(rwdbInner(person, address));
  selector.on(person["addr_id"] == adress["addr_id"]);
  selector.where(address["pref"] == "大阪府");
  ...

DBTools.h++ v4.1 でのJOIN

上述のように、テーブルの結合をFROMJOINONで行なうことでWHERE節をすっきり記述できるのですが、DBTools.h++ v4.0まではこのJOINONに大きな制限がありました。

t_emp(従業員)
id integer 従業員ID
name string 名前
t_job(部署)
id integer 部署ID
name string 部署名
t_map(従業員-部署対応)
emp_id integer 従業員ID
job_id integer 部署ID

上のようなテーブルから、全従業員の所属する部署の一覧を作ることを考えてみましょう。

従業員と所属部署との対応はt_mapによって対応づけられています(ただし従業員の中には所属部署の定まらない新入社員が存在するものとします)。

各テーブルの内容は、たとえば次のようになります('江口'さんと'大木'さんは新入社員であり、まだ所属部署が決まっていません)。

t_emp
id name
1 '相川'
2 '井上'
3 '上田'
4 '江口'
5 '大木'
t_job
id name
1 '総務'
2 '営業'
3 '広報'
4 '開発'
5 '保守'
t_map
emp_id job_i
1 1
2 2
3 3

さてこのとき、この3つのテーブルを t_map.job_id = t.job.id かつ t_map.id = t_map.emp_idという条件で結合するには、JOINの入れ子を作ることになります。すなわち、"t_mapt_job を条件 t_map.job_id = t_job.id で結合したもの" と t_emp を条件t_emp.id = t_map.emp_idで結合したものが求めるテーブルとなります。SQL文では

  SELECT t_emp.name, t_job.name
  FROM t_emp LEFT JOIN
    ( t_map INNER JOIN t_job
      ON t_map.job_id = t_job.id)
    ON t_emp.id = t_map.emp_id

ここでDBTools.h++の問題が露呈します。つまり、JOINの結合条件であるONはRWDBSelectorのメソッドon()で指定するため、JOINの入れ子が表現できないのです。

DBTools.h++ v4.1ではこの(JOINが入れ子にできない)が解消されました。JOINの結合条件を指定するメソッドon()がRWDBSelectorからRWDBJoinExprに移されました(RWDBSelector::on()も残してありますが、そのうちなくなってしまうと思われます)。

DBTools.h++ v4.1でJOINの入れ子が可能になり、以下のようなコードで記述できるようになりました。

  /*
   * SELECT t_emp.name, t_job.name
   * FROM t_emp LEFT JOIN
   *   ( t_map INNER JOIN t_job
   *     ON t_map.job_id = t_job.id)
   *   ON t_emp.id = t_map.emp_id
   */

  RWDBTable emp = db.table("T_EMP");
  RWDBTable job = db.table("T_JOB");
  RWDBTable map = db.table("T_MAP");

  RWDBSelector selector = db.selector();
  selector << emp["name"] << job["name"];
  RWDBJoinExpr inner_join =
    rwdbInner(map, job).on(map["job_id"] == job["id"]);
  RWDBJoinExpr left_join =
    rwdbLeftOuter(emp, inner_join).on(emp["id"] == map["emp_id"]);
  selector.from(left_join);
  ...

sample code (only for DBTools.h++ v4.1 or later)


#include <iostream>
#include <locale>
#include <rw/db/db.h>

using namespace std;

#ifdef _DEBUG
#define SERVER_TYPE "msq15d.dll"
#else
#define SERVER_TYPE "msq12d.dll"
#endif

int trial() {

  // データベースに接続
  RWDBDatabase db = RWDBManager::database(
                       SERVER_TYPE,    // serverType
                       "MSSQL_TRIAL",  // serverName
                       "sa",           // userName
                       "",             // password
                       "TRIAL"         // databaseName
                       );

  if ( !db.isValid() ) return 1;

  RWDBConnection connection = db.connection();

// 3つのテーブル 'T_EMP', 'T_JOB', 'T_EMP' を作成
  RWDBTable emp = db.table("T_EMP");
  RWDBTable job = db.table("T_JOB");
  RWDBTable map = db.table("T_MAP");

  RWDBOSql sql;
  if ( !emp.exists(connection, TRUE) ) {
    const char* statements[] = {
      "create table T_EMP(ID int, NAME char(10))",
      "insert into T_EMP values(1, '相川')",
      "insert into T_EMP values(2, '井上')",
      "insert into T_EMP values(3, '上田')",
      "insert into T_EMP values(4, '江口')",
      "insert into T_EMP values(5, '大木')",
      0 };
    for ( int i = 0; statements[i]; ++i ) {
      sql.statement(statements[i]);
      sql.execute(connection);
    }
  }

  if ( !job.exists(connection, TRUE) ) {
    const char* statements[] = {
      "create table T_JOB(ID int, NAME char(10))",
      "insert into T_JOB values(1, '総務')",
      "insert into T_JOB values(2, '営業')",
      "insert into T_JOB values(3, '広報')",
      "insert into T_JOB values(4, '開発')",
      "insert into T_JOB values(5, '保守')",
      0 };
    for ( int i = 0; statements[i]; ++i ) {
      sql.statement(statements[i]);
      sql.execute(connection);
    }
  }

  if ( !map.exists(connection, TRUE) ) {
    const char* statements[] = {
      "create table T_MAP(EMP_ID int, JOB_ID int)",
      "insert into T_MAP values(1, 1)",
      "insert into T_MAP values(2, 2)",
      "insert into T_MAP values(3, 3)",
      0 };
    for ( int i = 0; statements[i]; ++i ) {
      sql.statement(statements[i]);
      sql.execute(connection);
    }
  }

  /*
   * SELECT t_emp.name, t_job.name
   * FROM t_emp LEFT JOIN
   *   ( t_map INNER JOIN t_job
   *     ON t_map.job_id = t_job.id)
   *   ON t_emp.id = t_map.emp_id
   */

  RWDBSelector selector = db.selector();
  selector << emp["name"] << job["name"];
  RWDBJoinExpr inner_join =
    rwdbInner(map, job).on(map["job_id"] == job["id"]);
  RWDBJoinExpr left_join =
    rwdbLeftOuter(emp, inner_join).on(emp["id"] == map["emp_id"]);
  selector.from(left_join);
  cout << selector.asString() << endl;
  RWDBReader reader = selector.reader(connection);

  while ( reader() ) {
    RWCString emp_name, job_name;
    reader >> emp_name >> job_name;
    if ( job_name.isNull() ) {
      job_name = "(NULL)";
    }
    cout << emp_name << '\t' << job_name << endl;
  }

  cout << db.version() << endl;
  emp.drop();
  job.drop();
  map.drop();
  return 0;
}

void onError(const RWDBStatus& aStatus) {
  if ( aStatus.errorCode() == RWDBStatus::ok )
    return;
  cerr << aStatus.vendorMessage1() << " / "
       << aStatus.vendorMessage2() << endl;
  aStatus.raise();
}

int main() {
  locale::global(locale("japanese"));
  RWDBManager::setErrorHandler(onError);
  int ret = 0;
  try {
    ret = trial();
  } catch ( RWExternalErr& er) {
    cerr << er.why() << endl;
  } catch ( exception& ex ) {
    cerr << ex.what() << endl;
  }
  return ret;
}