まずCodeIgniterのActiveRecordクラスですが、これがかなりイイ感じです。何がイイってこんなに緩くて、ってゆーかこれってSQL直書きしてるのと何が違うんでしょうかね〜？まあたぶん素人にはわからない部分で何かすごく高尚なことをやっているんでしょう（たぶんｗ）。
それはともかく前に晒した我流SQLを詰め込んでいきます。
$this->db->select('nid, pref, nc, o.id oid, o.city oc, gdate');
$this->db->from("(SELECT DISTINCT nid, pref, nc, yomi, gdate
FROM cities
WHERE nc like '%".$data['newcity']."%'
LIMIT 0, 10) AS vn");
$this->db->join('newcities_oldcities j', 'vn.nid = j.newcity_id');
$this->db->join('oldcities o', 'o.id = j.oldcity_id');
$data['query'] = $this->db->get();テーブル名が若干変わっていますが、これはCakePHPで試行錯誤した時の名残です。Cakeはテーブル名を原則複数形にしなければならず、さらにHABTM関連の関連付けてるテーブルは両方のテーブル名をアルファベット順に並べたモノにしなければなりません。
なのでnewcities_oldcitiesは前のSQLだとjoinedというテーブルだったものと同じです。citiesも前はcityという名前だったMySQLのVIEWです。
で、問題なのはFROM句ですがここはサブクエリを書かないとならないので、よくわかりませんでしたがとりあえずダラダラと全部書いちゃいました。外側のJOIN部分も以前はWHERE句で書いていたものをせっかくなので（謎）JOINで書いてみました。
最後のdb->get()の部分は前は'cities'テーブルを指定していましたが、ActiveRecordクラスを使ってクエリを組み立てているので、パラメータなしでget()を読んでいます。そして結果…
う〜む、ちゃんとデータ取れてますね。ちなみに前にstdClass Objectと表示されていた部分は、VIEWで
foreach($query->result() as $row)
としていたところを
foreach($query->result_array() as $row)
にすることで普通の配列になりました。
あとデバッグ用に
$this->output->enable_profiler(TRUE);
とControllerの最後に記述しました。実行されたSQL文が見えるので便利です。データが取れているので大丈夫だとおもいましたが、一応これを見てもちゃんと意図どおりのSQLが組み立てられているようです。LIMITも新市町村の件数で取ってこれているし、もちろん新市町村名で検索も可能です。
次回はいよいよ懸案の？ページングを実装してみたいとおもいます。
