API tools faq
paste
indraNur

sql & builder

indraNur
May 25th, 2023 (edited)
77
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 13.09 KB | None | 0 0
raw download clone embed print report
  1. Query SQL
  2. =========
  3. Join 1.
  4. SELECT id_tm_bdp_gudang_stok, tm_po_barang.nama_tm_po_barang, tm_po_barang.kode_tm_po_barang, jml_min_tm_bdp_gudang_stok, jml_max_tm_bdp_gudang_stok, jumlah_tm_bdp_gudang_stok, gudang_lokasi_id_tm_bdp_gudang_stok, dibuat_saat_tm_bdp_gudang_stok, dibuat_oleh_tm_bdp_gudang_stok, diperbarui_saat_tm_bdp_gudang_stok, diperbarui_oleh_tm_bdp_gudang_stok
  5. FROM po_budgeting.tm_bdp_gudang_stok
  6. JOIN tm_po_barang on tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok = tm_po_barang.id_tm_po_barang
  7. WHERE barang_id_tm_bdp_gudang_stok ='6951';
  8.  
  9. Join 2.
  10. SELECT tm_bdp_gudang_stok.id_tm_bdp_gudang_Stok, tm_po_barang.nama_tm_po_barang, tm_po_barang.kode_tm_po_barang, tm_bdp_gudang_stok.jumlah_tm_bdp_gudang_stok
  11. FROM tm_bdp_gudang_stok
  12. JOIN tm_po_barang ON tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok = tm_po_barang.id_tm_po_barang ;
  13.  
  14. Query Builder Ci3
  15. =================
  16. $this->_join = [$this->_table_po_barang => 'tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok = tm_po_barang.id_tm_po_barang'];
  17.  
  18. Dja_model
  19. =========
  20.  
  21. // nama_opg_client
  22. $dt_join_opg_client = $this->dja_model->m_get_other_db([
  23. 'db' => 'db_osm',
  24. 'table' => $this->_table,
  25. 'where' => [
  26. 'id_bmg_proyek' => $id,
  27. ],
  28. 'single',
  29. 'join' => [
  30. 'opg_client' => 'opg_client.id_opg_client = bmg_proyek.klien_id_bmg_proyek'
  31. ],
  32. ]);
  33.  
  34.  
  35. // penagihan
  36. $dt_join_penagihan = $this->dja_model->m_get_other_db([
  37. 'db' => 'db_osm',
  38. 'table' => $this->_table,
  39. 'where' => [
  40. 'id_bmg_proyek' => $id,
  41. ],
  42. '',
  43. 'join' => [
  44. 'bmg_penagihan' => 'bmg_penagihan.proyek_id_penagihan = bmg_proyek.id_bmg_proyek'
  45. ],
  46. ]);
  47.  
  48. $data['data'] = $dt_join_opg_client;
  49. $data['title] = "Penarikan Uang Muka | $dt_join_opg_client->nama_bmg_proyek";
  50. $data['getTagihan'] = $dt_join_penagihan;
  51.  
  52. $this->load->view('layout', $data);
  53.  
  54.  
  55. join 3 table
  56. =============
  57. 1.
  58. //simulasi 1.
  59. $stok = $this->dja_model->m_get([
  60. "select" => "*",
  61. "table" => "tm_bdp_gudang_stok",
  62. "join" => [
  63. "tm_po_barang" => 'tm_po_barang.id_tm_po_barang = tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok',
  64. "tm_po_gudang_lokasi" => 'tm_po_gudang_lokasi.id_tm_po_gudang_lokasi = tm_bdp_gudang_stok.gudang_lokasi_id_tm_bdp_gudang_stok'
  65. ],
  66. "where" => [
  67. 'gudang_lokasi_id_tm_bdp_gudang_stok' => 1,
  68. 'dihapus_tm_po_barang' => 0,
  69. ],
  70. "limit" => 10,
  71. // "order_by" => 'id_tm_bdp_gudang_stok'
  72. ]);
  73.  
  74. //simulasi 2.
  75. $stok = $this->db->select('*')
  76. ->from($this->_table_stok)
  77. ->join('tm_po_barang', 'tm_po_barang.id_tm_po_barang = tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok', 'left')
  78. ->join('tm_po_gudang_lokasi', 'tm_po_gudang_lokasi.id_tm_po_gudang_lokasi = tm_bdp_gudang_stok.gudang_lokasi_id_tm_bdp_gudang_stok', 'left')
  79. ->where([
  80. 'gudang_lokasi_id_tm_bdp_gudang_stok' => 1,
  81. 'dihapus_tm_po_barang' => 0
  82. ])
  83. ->limit(10)
  84. ->order_by('nama_tm_po_barang')
  85. ->get()->result();
  86.  
  87.  
  88. //master
  89. $stok_opname = $this->db->select('*')
  90. ->from($this->_table_stok)
  91. ->join('table2', 'table2.id = table1.table2_id', 'left')
  92. ->join('table3', 'table3.id = table1.table3_id', 'left')
  93. ->where('gudang_lokasi_id_tm_bdp_gudang_stok', 1)
  94. ->limit(10)
  95. ->order_by('nama_barang_tm_bdp_gudang_stok')
  96. ->get()->result();
  97.  
  98. studikasus menu itemTransaksi.
  99. WHERE IN query SQL
  100. ------------------
  101. SELECT tm_po_barang.nama_tm_po_barang, tm_bdp_gudang_stok.stok_barang_tm_bdp_gudang_stok, tt_bdp_item_request_detail.item_request_id_tt_bdp_item_request_detail
  102. FROM tm_po_barang
  103. JOIN tm_bdp_gudang_stok ON tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok = tm_po_barang.id_tm_po_barang
  104. JOIN tt_bdp_item_request_detail ON tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail = tm_po_barang.id_tm_po_barang
  105. WHERE tt_bdp_item_request_detail.item_request_id_tt_bdp_item_request_detail IN (SELECT id_tt_bdp_item_request FROM tt_bdp_item_request)
  106. AND tm_po_barang.id_tm_po_barang IN (SELECT id_tm_po_barang FROM tm_po_barang ORDER BY id_tm_po_barang DESC);
  107.  
  108. WHERE IN query builder ci3
  109. --------------------------
  110. $this->db->select('tm_po_barang.nama_tm_po_barang, tm_bdp_gudang_stok.stok_barang_tm_bdp_gudang_stok');
  111. $this->db->from('tm_po_barang');
  112. $this->db->join('tm_bdp_gudang_stok', 'tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok = tm_po_barang.id_tm_po_barang');
  113. $this->db->join('tt_bdp_item_request_detail', 'tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail = tm_po_barang.id_tm_po_barang');
  114. $this->db->where_in('tt_bdp_item_request_detail.item_request_id_tt_bdp_item_request_detail', 'SELECT id_tt_bdp_item_request FROM tt_bdp_item_request');
  115. $this->db->where_in('tm_po_barang.id_tm_po_barang', '(SELECT id_tm_po_barang FROM tm_po_barang ORDER BY id_tm_po_barang DESC LIMIT 1)');
  116. $query = $this->db->get();
  117.  
  118. $result = $query->result();
  119.  
  120. foreach ($result as $row) {
  121. echo 'Nama Barang: ' . $row->nama_tm_po_barang . '<br>';
  122. echo 'Stok: ' . $row->stok_barang_tm_bdp_gudang_stok . '<br><br>';
  123. }
  124.  
  125. sql 30-06-23
  126. SELECT
  127. tt_bdp_item_request_detail.id_tt_bdp_item_request_detail,
  128. tt_bdp_item_request_detail.item_request_id_tt_bdp_item_request_detail,
  129. tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail,
  130. tt_bdp_item_request.kode_tt_bdp_item_request,
  131. tt_bdp_item_request.gudang_id_tt_bdp_item_request,
  132. tm_bdp_gudang_stok.id_tm_bdp_gudang_stok,
  133. tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok,
  134. tm_bdp_gudang_stok.stok_barang_tm_bdp_gudang_stok,
  135. tm_po_barang.id_tm_po_barang,
  136. tm_po_barang.nama_tm_po_barang,
  137. tm_po_barang.kode_tm_po_barang
  138. FROM
  139. tt_bdp_item_request_detail
  140. JOIN
  141. tt_bdp_item_request ON tt_bdp_item_request.id_tt_bdp_item_request = tt_bdp_item_request_detail.item_request_id_tt_bdp_item_request_detail
  142. JOIN
  143. tm_bdp_gudang_stok ON tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok = tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail
  144. JOIN
  145. tm_po_barang ON tm_po_barang.id_tm_po_barang = tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail
  146. GROUP BY id_tt_bdp_item_request_detail DESC;
  147.  
  148. query builder ci3
  149. noted:1 SELECT
  150. (tinggal gmana caranya stok barang yg di tampilin itu adalah stok terakhir dari table gudang stok berdasarkan id barang)
  151. -----------------
  152. $item_stok = $this->dja_model->m_get([
  153. "select" => "tt_bdp_item_request_detail.id_tt_bdp_item_request_detail,
  154. tt_bdp_item_request_detail.item_request_id_tt_bdp_item_request_detail,
  155. tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail,
  156. tt_bdp_item_request_detail.nama_barang_tt_bdp_item_request_detail,
  157. tt_bdp_item_request_detail.barang_golongan_tt_bdp_item_request_detail,
  158. tt_bdp_item_request_detail.barang_kategori_tt_bdp_item_request_detail,
  159. tt_bdp_item_request_detail.barang_kelas_tt_bdp_item_request_detail,
  160. tt_bdp_item_request_detail.barang_satuan_tt_bdp_item_request_detail,
  161. tt_bdp_item_request_detail.jumlah_tt_bdp_item_request_detail,
  162. tt_bdp_item_request_detail.kode_barang_tt_bdp_item_request_detail,
  163. tt_bdp_item_request_detail.diproses_tidak_tt_bdp_item_request_detail,
  164.  
  165. tt_bdp_item_request.kode_tt_bdp_item_request,
  166. tt_bdp_item_request.gudang_id_tt_bdp_item_request,
  167. tm_bdp_gudang_stok.id_tm_bdp_gudang_stok,
  168. tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok,
  169. (SELECT stok_barang_tm_bdp_gudang_stok FROM tm_bdp_gudang_stok WHERE barang_id_tm_bdp_gudang_stok = tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail ORDER BY id_tm_bdp_gudang_stok DESC LIMIT 1) AS stok_barang_tm_bdp_gudang_stok,
  170. tm_po_barang.id_tm_po_barang,
  171. tm_po_barang.nama_tm_po_barang,
  172. tm_po_barang.kode_tm_po_barang",
  173. "table" => "tt_bdp_item_request_detail",
  174. "join" => [
  175. "tt_bdp_item_request" => "tt_bdp_item_request.id_tt_bdp_item_request = tt_bdp_item_request_detail.item_request_id_tt_bdp_item_request_detail",
  176. "tm_bdp_gudang_stok" => "tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok = tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail",
  177. "tm_po_barang" => "tm_po_barang.id_tm_po_barang = tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail"
  178. ],
  179. "where" => [
  180. "item_request_id_tt_bdp_item_request_detail" => $value->id_tt_bdp_item_request
  181. ],
  182. "group_by" => "id_tt_bdp_item_request_detail",
  183. "order" => "id_tt_bdp_item_request_detail DESC"
  184. ]);
  185.  
  186. query builder ci3
  187. noted:2 JOIN mengguanakn AND
  188. (tinggal gmana caranya stok barang yg di tampilin itu adalah stok terakhir dari table gudang stok berdasarkan id barang)
  189. $item_stok = $this->dja_model->m_get([
  190. "select" => "tt_bdp_item_request_detail.id_tt_bdp_item_request_detail,
  191. tt_bdp_item_request_detail.item_request_id_tt_bdp_item_request_detail,
  192. tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail,
  193. tt_bdp_item_request_detail.nama_barang_tt_bdp_item_request_detail,
  194. tt_bdp_item_request_detail.barang_golongan_tt_bdp_item_request_detail,
  195. tt_bdp_item_request_detail.barang_kategori_tt_bdp_item_request_detail,
  196. tt_bdp_item_request_detail.barang_kelas_tt_bdp_item_request_detail,
  197. tt_bdp_item_request_detail.barang_satuan_tt_bdp_item_request_detail,
  198. tt_bdp_item_request_detail.jumlah_tt_bdp_item_request_detail,
  199. tt_bdp_item_request_detail.kode_barang_tt_bdp_item_request_detail,
  200. tt_bdp_item_request_detail.diproses_tidak_tt_bdp_item_request_detail,
  201.  
  202. tt_bdp_item_request.kode_tt_bdp_item_request,
  203. tt_bdp_item_request.gudang_id_tt_bdp_item_request,
  204. tm_bdp_gudang_stok.id_tm_bdp_gudang_stok,
  205. tm_bdp_gudang_stok.stok_barang_tm_bdp_gudang_stok,
  206. tm_po_barang.id_tm_po_barang,
  207. tm_po_barang.nama_tm_po_barang,
  208. tm_po_barang.kode_tm_po_barang",
  209. "table" => "tt_bdp_item_request_detail",
  210. "join" => [
  211. "tt_bdp_item_request" => "tt_bdp_item_request.id_tt_bdp_item_request = tt_bdp_item_request_detail.item_request_id_tt_bdp_item_request_detail",
  212. "tm_bdp_gudang_stok" => "tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok = tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail AND tm_bdp_gudang_stok.id_tm_bdp_gudang_stok = (SELECT id_tm_bdp_gudang_stok FROM tm_bdp_gudang_stok WHERE barang_id_tm_bdp_gudang_stok = tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail ORDER BY id_tm_bdp_gudang_stok DESC LIMIT 1)",
  213. "tm_po_barang" => "tm_po_barang.id_tm_po_barang = tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail"
  214. ],
  215. "where" => [
  216. "item_request_id_tt_bdp_item_request_detail" => $value->id_tt_bdp_item_request
  217. ],
  218. "group_by" => "id_tt_bdp_item_request_detail",
  219. "order" => "id_tt_bdp_item_request_detail DESC"
  220. ]);
  221.  
  222. // mas.fahmi (simulasi test.query by.Table)
  223. ===========================================
  224. $kode = 'IR.2023.00013';
  225. $ir = $this->dja_model->m_get([
  226. "select" => "id_tt_bdp_item_request",
  227. "table" => "tt_bdp_item_request",
  228. "where" => [
  229. "kode_tt_bdp_item_request" => $kode
  230. ],
  231. "single"
  232. ]);
  233.  
  234. $item_request = $this->dja_model->m_get([
  235. "select" => "tm_po_barang.nama_tm_po_barang, tm_bdp_gudang_stok.stok_barang_tm_bdp_gudang_stok, tt_bdp_item_request_detail.jumlah_tt_bdp_item_request_detail, tt_bdp_item_request_detail.item_request_id_tt_bdp_item_request_detail, tt_bdp_item_request_detail.barang_satuan_tt_bdp_item_request_detail",
  236. "table" => "tt_bdp_item_request_detail",
  237. "join" => [
  238. "tm_bdp_gudang_stok" => "tm_bdp_gudang_stok.barang_id_tm_bdp_gudang_stok = tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail",
  239. "tm_po_barang" => "tm_po_barang.id_tm_po_barang = tt_bdp_item_request_detail.barang_id_tt_bdp_item_request_detail"
  240. ],
  241. "where" => [
  242. "item_request_id_tt_bdp_item_request_detail" => $ir->id_tt_bdp_item_request
  243. ]
  244. ]);
  245.  
  246. echo "<table>";
  247. echo "<tr><td>No</td><td>Nama Barang</td><td>Stok Gudang</td><td>Jumlah Request</td><td>ID Item Request</td><td>Satuan</td></tr>";
  248. $no = 1;
  249. foreach ($item_request as $val) {
  250. echo "<tr><td>$no</td><td>$val->nama_tm_po_barang</td><td>$val->stok_barang_tm_bdp_gudang_stok</td><td>$val->jumlah_tt_bdp_item_request_detail</td><td>$val->item_request_id_tt_bdp_item_request_detail</td><td>$val->barang_satuan_tt_bdp_item_request_detail</td></tr>";
  251. $no++;
  252. }
  253. echo "</table>"
  254.  
  255.  
  256.  
  257.  
  258.  
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!