駅JPのデータを利用して何か開発する（Express/sequelizeでJsonデータを返す） -STEP3-

はじめに

• 駅JPデータを利用して何か作成できないか試行錯誤している中で駅JPのデータをJOSN形式で返すAPIを作成する

前回までの調査の内容

px-wing.hatenablog.com px-wing.hatenablog.com

sequelizeのassociationの設定

企業情報取得

'use strict';
module.exports = (sequelize, DataTypes) => {
  const companies = sequelize.define('companies', {
    company_cd: {
      type: DataTypes.INTEGER,
      allowNull: false,
      primaryKey: true
    },
    // 一部省略
    e_status: DataTypes.INTEGER,
    e_sort: DataTypes.INTEGER
  }, {
    underscored: true,
  });
  companies.associate = function(models) {
    companies.hasMany(models.lines, {foreignKey: 'company_cd'});
  };
  return companies;
};

路線情報取得

'use strict';
module.exports = (sequelize, DataTypes) => {
  const lines = sequelize.define('lines', {
    line_cd: {
      type: DataTypes.INTEGER,
      primaryKey: true
    },
    company_cd: {
      type: DataTypes.INTEGER,
      foreignKey: true,
      allowNull: false,
    },
    // 一部省略
    line_name: DataTypes.STRING,
    e_status: DataTypes.INTEGER,
    e_sort: DataTypes.INTEGER
  }, {
    underscored: true,
  });
  lines.associate = function(models) {
    lines.belongsTo(models.companies, {primaryKey: 'company_cd'});
    lines.hasMany(models.stations, {foreignKey: 'station_cd'});
  };
  return lines;
};

駅情報

'use strict';
module.exports = (sequelize, DataTypes) => {
  const stations = sequelize.define('stations', {
    station_cd: {
      type: DataTypes.INTEGER,
      primaryKey: true
    },
    line_cd: {
      type: DataTypes.INTEGER,
      foreignKey: true
    },
    // 一部省略
    e_status: DataTypes.INTEGER,
    e_sort: DataTypes.INTEGER
  }, {
    underscored: true,
  });
  stations.associate = function(models) {
    stations.belongsTo(models.lines, {primaryKey: 'line_cd'});
  };
  return stations;
};

Express側のサンプルコード

企業情報取得

router.get('/companies', async (req, res, next) =>{
  db.companies.findAll({
    attributes: ['company_cd', 'company_name'],
  }).then((companies)=>{
    res.json(JSON.stringify(companies))
  });
})

路線情報取得

router.get('/lines', async (req, res, next) =>{
  db.companies.findAll({
    where: {
      company_cd: 18
    },    
    include: [{
        model: db.lines,
        attributes: ['line_cd', 'line_name'],
        required: true
    }]
  }).then((lines)=>{
    res.json(lines)
  });
})

• 出力結果

[{"company_cd":18,"rr_cd":28,"company_name":"東京メトロ","company_name_k":"トウキョウメトロ","company_name_h":"東京地下鉄株式会社","company_name_r":"東京メトロ","company_url":null,"company_type":2,"e_status":0,"e_sort":18,"createdAt":"2020-07-03T15:00:36.000Z","updatedAt":"2020-07-03T15:00:36.000Z","lines":[{"line_cd":28001,"line_name":"東京メトロ銀座線"},{"line_cd":28002,"line_name":"東京メトロ丸ノ内線"},{"line_cd":28003,"line_name":"東京メトロ日比谷線"},{"line_cd":28004,"line_name":"東京メトロ東西線"},{"line_cd":28005,"line_name":"東京メトロ千代田線"},{"line_cd":28006,"line_name":"東京メトロ有楽町線"},{"line_cd":28008,"line_name":"東京メトロ半蔵門線"},{"line_cd":28009,"line_name":"東京メトロ南北線"},{"line_cd":28010,"line_name":"東京メトロ副都心線"}]}]

駅情報取得

router.get('/stations', async (req, res, next) =>{
  db.stations.findAll({
    attributes: ['station_cd', 'station_name'],
    where: {
      line_cd: [28001,28002,28003]
    }
  }).then((stations)=>{
    logger.app.debug(JSON.stringify(stations));
    res.json(stations)
  });
})

• 出力結果

[{"station_cd":2800101,"station_name":"浅草"},{"station_cd":2800102,"station_name":"田原町"},{"station_cd":2800103,"station_name":"稲荷町"},{"station_cd":2800104,"station_name":"上野"},{"station_cd":2800105,"station_name":"上野広小路"},{"station_cd":2800106,"station_name":"末広町"},{"station_cd":2800107,"station_name":"神田"},{"station_cd":2800108,"station_name":"三越前"},{"station_cd":2800109,"station_name":"日本橋"},{"station_cd":2800110,"station_name":"京橋"},{"station_cd":2800111,"station_name":"銀座"},{"station_cd":2800112,"station_name":"新橋"},{"station_cd":2800113,"station_name":"虎ノ門"},{"station_cd":2800114,"station_name":"溜池山王"},{"station_cd":2800115,"station_name":"赤坂見附"},{"station_cd":2800116,"station_name":"青山一丁目"},{"station_cd":2800117,"station_name":"外苑前"},{"station_cd":2800118,"station_name":"表参道"},{"station_cd":2800119,"station_name":"渋谷"},{"station_cd":2800201,"station_name":"池袋"},{"station_cd":2800202,"station_name":"新大塚"},{"station_cd":2800203,"station_name":"茗荷谷"},{"station_cd":2800204,"station_name":"後楽園"},{"station_cd":2800205,"station_name":"本郷三丁目"},{"station_cd":2800206,"station_name":"御茶ノ水"},{"station_cd":2800207,"station_name":"淡路町"},{"station_cd":2800208,"station_name":"大手町"},{"station_cd":2800209,"station_name":"東京"},{"station_cd":2800210,"station_name":"銀座"},{"station_cd":2800211,"station_name":"霞ケ関"},{"station_cd":2800212,"station_name":"国会議事堂前"},{"station_cd":2800213,"station_name":"赤坂見附"},{"station_cd":2800214,"station_name":"四ツ谷"},{"station_cd":2800215,"station_name":"四谷三丁目"},{"station_cd":2800216,"station_name":"新宿御苑前"},{"station_cd":2800217,"station_name":"新宿三丁目"},{"station_cd":2800218,"station_name":"新宿"},{"station_cd":2800219,"station_name":"西新宿"},{"station_cd":2800220,"station_name":"中野坂上"},{"station_cd":2800221,"station_name":"新中野"},{"station_cd":2800222,"station_name":"東高円寺"},{"station_cd":2800223,"station_name":"新高円寺"},{"station_cd":2800224,"station_name":"南阿佐ケ谷"},{"station_cd":2800225,"station_name":"荻窪"},{"station_cd":2800226,"station_name":"中野新橋"},{"station_cd":2800227,"station_name":"中野富士見町"},{"station_cd":2800228,"station_name":"方南町"},{"station_cd":2800301,"station_name":"北千住"},{"station_cd":2800302,"station_name":"南千住"},{"station_cd":2800303,"station_name":"三ノ輪"},{"station_cd":2800304,"station_name":"入谷"},{"station_cd":2800305,"station_name":"上野"},{"station_cd":2800306,"station_name":"仲御徒町"},{"station_cd":2800307,"station_name":"秋葉原"},{"station_cd":2800308,"station_name":"小伝馬町"},{"station_cd":2800309,"station_name":"人形町"},{"station_cd":2800310,"station_name":"茅場町"},{"station_cd":2800311,"station_name":"八丁堀"},{"station_cd":2800312,"station_name":"築地"},{"station_cd":2800313,"station_name":"東銀座"},{"station_cd":2800314,"station_name":"銀座"},{"station_cd":2800315,"station_name":"日比谷"},{"station_cd":2800316,"station_name":"霞ケ関"},{"station_cd":2800322,"station_name":"虎ノ門ヒルズ"},{"station_cd":2800317,"station_name":"神谷町"},{"station_cd":2800318,"station_name":"六本木"},{"station_cd":2800319,"station_name":"広尾"},{"station_cd":2800320,"station_name":"恵比寿"},{"station_cd":2800321,"station_name":"中目黒"}]

実際は下記のように実装したかった

• stationsテーブルとlinesテーブルを結合した状態でデータを取得したかった。

  db.stations.findAll({
    attributes: ['station_cd', 'station_name'],
    where: {
      line_cd: [28001,28002,28003]
    },
    include: [{
        model: db.lines,
        attributes: ['line_cd', 'line_name'],
        required: true
    }]
  }).then((stations)=>{
    res.json(stations)
  });

• 上記のプログラムを実行するとなぜか下記のエラーが発生してしまい、解決することができなかった。カラム名によけいな文字列がついてしまい、テーブルに、そんなカラムが存在しないといわれてエラーになっている。時間があるときに調査してみたいと思います。

Unhandled rejection SequelizeDatabaseError: Unknown column 'stations.line_line_cd' in 'on clause'