sinkog
commented
5 years ago I solved the question.
const parser = require('pg-query-parser'); z = parser.parse('SELECT * FROM test_table t JOIN XXX a ON t.x=a.x LEFT JOIN apple y ON y.b=a.b').query; z[0].SelectStmt.fromClause[0].JoinExpr=z[0].SelectStmt.fromClause[0].JoinExpr.larg.JoinExpr parser.deparse(z);
I'm planning a postgresql query maker. The goal is to create an interface where I specify the data to be queryed, the filter conditions, and it uses the known query to query the appropriate data from the postgresql server. I got there right now that the current data structure "fromClaus" is quite interesting. As my understanding larg / rarg bind the individual JOIN clusters. However, in the case of a complicated query, the result will be "interesting". Practically, the above-mentioned goal is not or very difficult to use. My idea was the following: I knew the tables I needed with the knowledge I needed and I rebuild it without unnecessary tables and run the querry. In fact, and in the present data structure, solving the problem is quite cumbersome and only possible under certain conditions, which I think is not very good for my goals I've looked at this querry better:
const parser = require('pg-query-native'); test=parser.parse('SELECT cv.void::oid , cv.dbid::oid, cv.cvoid::oid, cv.vonum::character(20), cv.ovnum::text, cv.pvnum::text, cv.genvoid::oid, i.itemid::oid, i.itp::character(4), i.pitemid::oid, i.citemid::oid, i.spitemid::oid, i.citem_type::character(4), i.ocsid::oid, i.dhid_ocs::oid, i.dstdbid::oid, i.pid::oid, i.prqty::numeric(16,6), i.qty::numeric(16,6), i.fwdqty::numeric(16,6), il.objid::oid, il.status::character(1), il.dstdbid::oid, il.dhid_obj::oid, il.dhid_maker::oid, il.prqty::numeric(16,6), il.value::numeric(20,6), il.qty::numeric(16,6), il.fwdqty::numeric(16,6), il.descr::text, il.xflds::text[], ss.svid::oid, ss.parsvid::oid, ss.stid::oid, ss.dstid::oid, ss.nstid::oid, ss.svord::oid, ss.status::character(1) as ss_status, ss.dstdbid::oid, ss.srcdbid::oid, ss.stflag::character(1) , ss.ststat::character(1), ss.stqid::character(4), ss.plctid::oid, ss.sttid::oid, ss.closed::boolean, ss.prqty::numeric(16,6), ss.qty::numeric(16,6), ss.resqty::numeric(16,6), ss.vfpath::text, ss.from_path::text, ss.to_path::text, ss.verify::character(1), ss.xflds::text[], ssi.qty::numeric as ssi_qty FROM voucher cv JOIN item i ON i.dbid=cv.dbid AND i.cvoid=cv.void AND i.deleted=0 JOIN cikktorzs ct ON ct.ocsid=i.ocsid JOIN stockv_item si ON si.itemid=i.itemid AND i.dbid=si.dbid AND si.deleted=0 JOIN stockv stc ON stc.void=si.void AND stc.dbid=si.dbid AND stc.deleted=0 JOIN item_lot il ON i.dbid=il.dbid AND i.itemid=il.itemid and il.deleted=0 JOIN stockv_stock_item ssi ON ssi.itemid=i.itemid AND ssi.dbid=i.dbid AND ssi.deleted=0 JOIN stockv_stock ss ON ss.dbid=cv.dbid AND ssi.svid=ss.svid AND il.objid=ss.objid AND ss.deleted=0 JOIN stockv_stock sso ON sso.dbid=ss.dbid AND sso.svid=(string_to_array(substr(ss.vfpath, 2), \'/\'))[1]::oid AND sso.deleted=0 JOIN lot l ON il.dbid=l.dbid AND il.objid=l.objid JOIN item li ON li.itemid=l.itemid_inl AND l.dbid=li.dbid JOIN voucher lv ON li.dbid=lv.dbid AND li.cvoid=lv.void LEFT JOIN invoice_item ii ON ii.itemid=i.itemid AND i.dbid=ii.dbid AND ii.deleted=0 LEFT JOIN invoice inv ON inv.void=ii.void AND inv.dbid=cv.dbid AND inv.deleted=0 LEFT JOIN szolgtorzs szt ON szt.ocsid=i.ocsid WHERE cv.deleted=0 AND cv.status in (\'c\',\'l\',\'z\') AND cv.invaddrid= ANY (string_to_array(\'6635\',\'|\')) AND coalesce(ct.kcikkcs,szt.kcikkcs)= ANY (string_to_array(\'65\',\'|\')::int4[]) AND ct.pszichotrop=\'t\' AND \'{"002"}\' <@ coalesce(ct.eltart,\'{}\') AND ct.cikkszam in (SELECT cikkszam FROM szallitok WHERE cegkod = ANY (string_to_array(\'20469\',\'|\')) AND deleted=0 AND !vevo) AND l.maker_id=\'95420\' AND cv.votp = ANY (string_to_array(\'VSLT|AKSB\',\'|\')) AND cv.shippartnerid= ANY (string_to_array(\'K0013\',\'|\')) AND stc.stockdate <= \'2018-10-10\' AND cv.shipaddrid= ANY (string_to_array(\'6635\',\'|\')) AND l.gyarszam like\'%5RGT%\' AND coalesce(stc.dir, CASE inv.kind WHEN \'B\' THEN iif(ii.discharge, \'I\', \'O\') WHEN \'S\' THEN iif(ii.discharge, \'O\', \'I\') WHEN \'I\' THEN \'N\' END) in (\'I\', \'O\', \'\', \'\') AND cv.dbid = 1 AND coalesce(ct.tcskod,szt.tcskod)= ANY (string_to_array(\'001\',\'|\')) AND cv.invpartnerid= ANY (string_to_array(\'K0253\',\'|\')) AND lv.shippartnerid=\'41877\'AND coalesce(ct.cikkod,szt.cikkod)=ANY (string_to_array(\'032\',\'|\')) AND lv.shipaddrid=\'14198\' AND stc.stockdate >= \'2018-10-10\' AND i.ocsid = ANY (string_to_array(\'3557\',\'|\')::int4[]::oid[]) AND stc.status=\'s\' AND i.qty>0 GROUP BY 1,2,3,4,5,6,7,8 ORDER BY 1,2,3');The problem is in the evaluation of the FROM part. The current dependencies are: 1 FROM voucher cv 2 1 JOIN item i ON i.dbid=cv.dbid AND i.cvoid=cv.void AND i.deleted=0 3 2 JOIN cikktorzs ct ON ct.ocsid=i.ocsid 4 2 JOIN stockv_item si ON si.itemid=i.itemid AND i.dbid=si.dbid AND si.deleted=0 5 4 JOIN stockv stc ON stc.void=si.void AND stc.dbid=si.dbid AND stc.deleted=0 6 2 JOIN item_lot il ON i.dbid=il.dbid AND i.itemid=il.itemid and il.deleted=0 7 2 JOIN stockv_stock_item ssi ON ssi.itemid=i.itemid AND ssi.dbid=i.dbid AND ssi.deleted=0 8 7,6 JOIN stockv_stock ss ON ss.dbid=cv.dbid AND ssi.svid=ss.svid AND il.objid=ss.objid AND ss.deleted=0 9 8 JOIN stockv_stock sso ON sso.dbid=ss.dbid AND sso.svid=(string_to_array(substr(ss.vfpath, 2), \'/\'))[1]::oid AND sso.deleted=0 A 6 JOIN lot l ON il.dbid=l.dbid AND il.objid=l.objid B A JOIN item li ON li.itemid=l.itemid_inl AND l.dbid=li.dbid C B JOIN voucher lv ON li.dbid=lv.dbid AND li.cvoid=lv.void D 2 LEFT JOIN invoice_item ii ON ii.itemid=i.itemid AND i.dbid=ii.dbid AND ii.deleted=0 E D LEFT JOIN invoice inv ON inv.void=ii.void AND inv.dbid=cv.dbid AND inv.deleted=0 F 2 LEFT JOIN szolgtorzs szt ON szt.ocsid=i.ocsid
While in your mapping: ((((((((((((((1/2)/3)/4)/5)/6)/7)/8)/9)/A)/B)/C)/D)/E)/F)
So if I skip the unnecessary tables, psqlQuery will not be created... It is all in fact and true, but it is difficult to change... Maybe some idea would help me to create my project?