Join two database tables then group the results as subarrays-CodePudding

I am having a hard time relating the result set of two database queries. There can be zero or many related rows between my "barang" rows and my "harga" rows.

My CodeIgniter queries look like this:

$barang = $this->db
    ->select('pj_barang.*')
    ->where('dihapus', 'tidak')
    ->get('pj_barang')->result_array();

$harga = $this->db
    ->select('pj_detailsupplier.*')
    ->join('pj_barang', 'pj_detailsupplier.id_barang = pj_barang.id_barang', 'right')
    ->get('pj_detailsupplier')
    ->result_array();

These queries populate array structures like these (many columns removed for simplicity):

$barang=array(
    [
        'id_barang' => 01,
        'nama_barang' => "laptop",
        'merk' => 'lenovo'
    ],
    [
        'id_barang' => 02,
        'nama_barang' => "RAM",
        'merk' => 'lenovo'
    ],
    [
        'id_barang' => 03,
        'nama_barang' => "Keyborad",
        'merk' => 'lenovo'
    ],
);

$harga=array(
    [
        'id_barang' => 01,
        'harga' => 2000000,
    ],
    [
        'id_barang' => 02,
        'harga' => 100000,
    ]
);

I want to join them to form a multidimensional array with subsets of data grouped by id_barang, but my code is still wrong.

$result=array();
foreach ($barang as $value) {
    foreach ($harga as $k => $val) {
        $result[$value['id_barang'] = $val["id_barang"]] = [
            'harga' => $val["harga"]
        ];
    }
}
echo "<pre>";
echo print_r($result);

The output is:

Array
(
   [1] => Array
    (
        [harga] => 2000000
    )
   [2] => Array
    (
        [harga] => 100000
    )
)

The desired result:

Array
(
  [0] => Array
  (
    [id_barang] => 01
    [nama_barang] => laptop
    [merk] => lenovo
    [dataharga] => Array
            (
                [0] => Array
                    (
                        [id_barang] => 01
                        [harga] => 2000000
                    )
            )
  )
  [1] => Array
  (
    [id_barang] => 02
    [nama_barang] => RAM
    [merk] => lenovo
    [dataharga] => Array
            (
                [1] => Array
                    (
                        [id_barang] => 02
                        [harga] => 100000
                    )
            )
  )
  [2] => Array
  (
    [id_barang] => 03
    [nama_barang] => Keyboard
    [merk] => lenovo
    [dataharga] => Array
            (
                [2] => Array
                    (
                        [id_barang] => ""
                        [harga] => ""
                    )
            )
  )
)

CodePudding user response：

Try the following code and see whether it works:

$barang=array(
    [
        'id_barang' => 01,
        'nama_barang' => "laptop",
        'merk' => 'lenovo'
    ],
    [
        'id_barang' => 02,
        'nama_barang' => "RAM",
        'merk' => 'lenovo'
    ],
    [
        'id_barang' => 03,
        'nama_barang' => "Keyborad",
        'merk' => 'lenovo'
    ],
);
$harga=array(
    [
        'id_barang' => 01,
        'harga' => 2000000,
    ],
    [
        'id_barang' => 02,
        'harga' => 100000,
    ]
    );


foreach ($barang as &$value) {
    $index =array_search($value['id_barang'], array_column($harga, 'id_barang'));

    if($index > -1) {
      $value['dataharga'] = array($harga[$index]); // Do you really need additional array() ?
    } else {
      $value['dataharga'] = array(['id_barang' => "", 'harga' => ""]); // Do you really need additional array() ?
    }
}

echo "<pre>";
echo print_r($barang);

CodePudding user response：

Judging by your subsequent question, I'll recommend a LEFT JOIN and only link the pj_detailsupplier table when id_barang is not null.

Then use PHP to iterate the rows and group on the id_barang value and push "harga" data into the dataharga subarray.

Recommended (untested) CodeIgniter code:

$rows = $this->db
    ->select('pj_barang.*, pj_detailsupplier.*')
    ->from('pj_barang')
    ->join('pj_detailsupplier', 'pj_barang.id_barang = pj_detailsupplier.id_barang AND pj_barang.id_barang IS NOT NULL', 'LEFT')
    ->where('dihapus', 'tidak')
    ->get()
    ->result_array();

$grouped = [];
foreach ($rows as $row) {
    if (!isset($grouped[$row['id_barang']])) {
        $grouped[$row['id_barang']] = [
            'id_barang' => $row['id_barang'],
            'kode_barang' => $row['kode_barang'],
            'nama_barang' => $row['nama_barang'],
            'total_stok' => $row['total_stok'],
            'harga_beli' => $row['harga_beli'],
            'id_kategori_barang' => $row['id_kategori_barang'],
            'id_merk_barang' => $row['id_merk_barang'],
            'tanggal_expired' => $row['tanggal_expired'],
            'deskripsi' => $row['deskripsi'],
            'gambar' => $row['gambar'],
            'dihapus' => $row['dihapus'],
        ];
    }
    $grouped[$row['id_barang']]['dataharga'][] =  [
        'id_detailsupplier' => $row['id_detailsupplier'],
        'id_supplier' => $row['id_supplier'],
        'no_batch' => $row['no_batch'],
        'stok' => $row['stok'],
        'tanggal' => $row['tanggal'],
        'status_detailsupplier' => $row['status_detailsupplier'],
    ];
}
var_export(array_values($grouped));
// return array_values($grouped));