javascript - NodeJS My SQL run multiple query in one session -
i have tree relation table in database. category room reservation_detail
first want select category table it's filter.
second want select room table pass parameter returned first query.
third want select reservation_detail table pass parameter returned second query.
select id,name,price,currency category branch_id=84; select id,room_no,name,price,currency room branch_id=84 , category_id=64; select d.id, d.create_date, d.update_date, d.room_id, d.status_id, d.start_date, d.end_date, s.name status_name, a.id reservation_id, a.person_no person_no, p.first_name, p.last_name, p.email reservation_detail d inner join reservation_status s on d.status_id=s.id inner join reservation on d.reservation_id=a.id inner join person p on a.person_no=p.personal_no d.room_id=44 , d.start_date >'2017-07-11' , d.start_date<'2017-09-20'; one category contains more 1 room wand run second query in cycle , here have async problem, how solve problem idea?
this node js code
getcategory = function (branch_id, callback) { var categorydata; var query = 'select id,name,price,currency category branch_id=?'; pool.getconnection(function (err, connection) { connection.query(query, [branch_id], function (error, row, fields) { if (error) { callback(error, null); } else { categorydata = row; callback(null, categorydata); } connection.release(); }); }); } getroom = function (branch_id, categoryid, callback) { var roomdata; var roomsql = 'select id,room_no,name,price,currency room branch_id=? , category_id=?'; pool.getconnection(function (err, connection) { connection.query(roomsql, [branch_id, categoryid], function (error, row, fields) { if (err) { callback(err, null); } else { roomdata = row; callback(null, roomdata); } connection.release(); }); }); } getreservationl = function (room_id, start_date, end_date, callback) { var reservationdata; var reservationsql = 'select d.id,d.create_date,d.update_date,d.room_id,d.status_id,d.start_date,d.end_date, ' + ' s.name status_name,a.id reservation_id,a.person_no person_no, p.first_name,p.last_name,p.email ' + ' reservation_detail d ' + ' inner join reservation_status s on d.status_id=s.id ' + ' inner join reservation on d.reservation_id=a.id ' + ' inner join person p on a.person_no=p.personal_no ' + ' d.room_id=? , d.start_date >? , d.start_date<?'; pool.getconnection(function (err, connection) { connection.query(reservationsql, [room_id, start_date, end_date], function (error, row, fields) { if (err) { callback(err, null); } else { reservationdata = row; callback(null, reservationdata); } connection.release(); }); }); } pool.getreservation = function (branch_id, start_date, end_date, callback) { var categorydata; var roomdata; var reservationdata; getcategory(branch_id, function (err, data) { if (err) { callback(err, categorydata); } else { categorydata = data; console.log('c', categorydata); (var = 0; < categorydata.length; i++) { console.log('c', categorydata[i].name); getroom(branch_id, categorydata[0].id, function (err, data) { if (err) { callback(err, null); } else { console.log('r', data); categorydata[0].room = data; (var = 0; < categorydata.length; i++) { getreservationl(data[0].id, start_date, end_date, function (err, data) { if (err) { callback(err, null); } else { console.log('res', data); // callback(null, data); } }); } } }); } console.log('call', categorydata); callback(null, categorydata); } }); } result after each step should this:
1) after first query
[ { "id": 64, "name": "vip", "price": 116.5, "currency": "usd" }, { "id": 74, "name": "super vip", "price": 110, "currency": "eur" }, { "id": 84, "name": "double", "price": 110, "currency": "gel" } ] 2) after second query
[ { "id": 64, "name": "vip", "price": 116.5, "currency": "usd" room: [ { id:1, name:'test' }, { id:2, name:'test2' } ] }, { "id": 74, "name": "super vip", "price": 110, "currency": "eur" room: [ { id:3, name:'test' }, { id:4, name:'test2' } ] }, { "id": 84, "name": "double", "price": 110, "currency": "gel" room: [ { id:5, name:'test' }, { id:6, name:'test2' } ] } ] 3) after third , final query
[ { "id": 64, "name": "vip", "price": 116.5, "currency": "usd" room: [ { id:1, name:'test', reservation:[ { id:1, name:'aaa' }, { id:1, name:'aaa' } ] }, { id:2, name:'test2', reservation:[ { id:1, name:'aaa' }, { id:1, name:'aaa' } ] } ] }, { "id": 74, "name": "super vip", "price": 110, "currency": "eur" room: [ { id:3, name:'test', reservation:[ { id:1, name:'aaa' }, { id:1, name:'aaa' } ] }, { id:4, name:'test2', reservation:[ { id:1, name:'aaa' }, { id:1, name:'aaa' } ] } ] }, { "id": 84, "name": "double", "price": 110, "currency": "gel" room: [ { id:5, name:'test', reservation:[ { id:1, name:'aaa' }, { id:1, name:'aaa' } ] }, { id:6, name:'test2', reservation:[ { id:1, name:'aaa' }, { id:1, name:'aaa' } ] } ] } ] this structure
wiki
Comments
Post a Comment