mysql - 从连接或子查询中选择记录作为列(子数组)

Question

我有我的 table

companies (id)
purchase_invoice (id,company_id,date)
items(id,company_id,purchase_invoice_id)
gifted_items(id,company_id,purchase_invoice_id)
rebated_items(id,company_id,purchase_invoice_id)
return_items(id,company_id,purchase_invoice_id)

我需要在company_id等于(user selected)的其他表中查询公司的所有采购发票及其关联记录。我需要所有这些子表结果作为子列(如果我是正确的，idk)。

注意:company_id 和 purchase_invoice_id 在所有其他表中都是 FK

这就是我想要的

 $ledgers = array(
            'com'   => $data['company_id'],
            'sd'    => $data['start_date'],
            'ed'    => $data['end_date']
        );
$purchaseObj = new Application_Model_DbTable_Ledgers();
$purchaseRes = $purchaseObj->getPurchaseInvoices($ledgers);


public function getPurchaseInvoices($ledgers) {
    $sql = $this->select()
            ->setIntegrityCheck(false)
            ->from(array('pi' => $this->_name))
            ->join(array('c' => 'companies'), 'pi.company_id = c.id', array('c.name as companyName'))
            ->join(array('it' => 'items'), 'it.purchase_invoice_id = pi.id');

          //  ->join(array('prm' => 'rebated_purchase_mobiles'), 'prm.company_id = pi.company_id')
           // ->join(array('rpm' => 'returned_purchase_mobiles'), 'rpm.company_id = pi.company_id')
           // ->join(array('pgm' => 'purchase_gifted_mobiles'), 'pgm.company_id = pi.company_id');

$sql = $sql->where('pi.company_id  = ?', $ledgers['com']);  

    if (count($ledgers)) {
        if (Performance_Engine::notEmpty($ledgers['sd']) && $ledgers['sd'] != 'sd') {
            $dt = new DateTime($ledgers['sd']);
            $sdate = $dt->format('Y-m-d');
            $sql = $sql->where('CAST(pi.date AS DATE) >= ?', $sdate);
        }
        if (Performance_Engine::notEmpty($ledgers['ed']) && $ledgers['ed'] != 'ed') {
            $dt = new DateTime($ledgers['ed']);
            $edate = $dt->format('Y-m-d');
            $sql = $sql->where('CAST(pi.date AS DATE) <= ?', $edate);
        }
          $sql->group('pi.id');
        $sql->order('pi.id ASC');

        return $sql->query()->fetchAll();
    }
}

我需要如下数组的结果

Array (

[0] => Array
    (
        [id] => 102
        [company_id] => 8
        [grand_total_price] => 45000
        [disc_by_rupees] => 111
        [disc_by_percent] => 0
        [total_price] => 44889
        [pay_amount] => 15000
        [total_paid_amount] => 15000
        [remaining_amount] => 29889
        [bill_no] => Q13
        [date] => 2015-01-15
        [companyName] => QMobile
        [items]=> array(
                [0] => array(
                   'id' => 1
                   'company_id'=> 8
                   'purchase_invoice_id '=> 102
                   'model_id' => 1
                   'date' => 2015-12-1
                   'IMEI'=>7654367876
                   'color' => black     
                   'purchase_price' => 500
                   'is_sold' => 1
                   'is_returned'=>1
               )

               [1] => array(
                   'id' => 2
                   'company_id'=> 8
                   'purchase_invoice_id '=> 102
                   'model_id' => 3
                   'date' => 2015-12-1
                   'IMEI'=>34567890
                   'color' => white
                   'purchase_price' => 6500
                   'is_sold' => 1
                   'is_returned'=>1
               )

       )
       [gifted_items]=> array(
                [0] => array(
                   'id' => 1
                   'company_id'=> 8
                   'purchase_invoice_id '=> 102
               )

               [1] => array(
                   'id' => 2
                   'company_id'=> 8
                   'purchase_invoice_id '=> 102
               )
       )
       [rebated_items]=> array(
                [0] => array(
                   'id' => 1
                   'company_id'=> 8
                   'purchase_invoice_id '=> 102
               )

               [1] => array(
                   'id' => 2
                   'company_id'=> 8
                   'purchase_invoice_id '=> 102
               )
       )
      [return_items]=> array(
                [0] => array(
                   'id' => 1
                   'company_id'=> 8
                   'purchase_invoice_id '=> 102
               )

               [1] => array(
                   'id' => 2
                   'company_id'=> 8
                   'purchase_invoice_id '=> 102
               )
       )
 )

我希望这是理解。请纠正我需要做什么。我需要做的就是采购发票和所有关联的表格作为包含所选记录数组的列

Answer 1

这在查询中是不可能的。