php - 如何在codeigniter中合并两个查询

Question

我在 codeigniter 中有 2 个查询，这些查询用于 ajax 数据表。在此之前，当我使用单个查询时它会起作用。在为 UNION 添加了一个查询之后，它对我不起作用。请找到我用于数据表的模型函数。

Model.php（使用此功能）在 _get_online_supplier_reports_datatable() 函数中使用单个查询

<?php
public function _get_online_supplier_reports_datatable(){

     $this->db->select('"Amadeus" AS SupplierName,TST_Type,TKT_Type,TKTNumber,TKT_FO_REF,BasePrice,TaxPrice,Commission1,(BasePrice+TaxPrice) AS TotalPrice,"issued" AS tkt_type,createdby,TicketedDate as created_date,CONCAT(a3m.firstname," ",a3m.lastname) AS created_name,a3m.account_id,a3m.locationvalue AS Location_ID,a3m.locationdescription AS Location_Name,a3m.costcentervalue AS CostCenter_ID,a3m.costcenterdescription AS CostCenter_Name');
        $this->db->from($this->traveldb->database.".booking_price bp");
        $this->db->join('a3m_account_details a3m','a3m.account_id=createdby','left');
        $this->db->where('TKTNumber !=', '');


        if(isset($_POST['order'])) // here order processing
        {
            $this->db->order_by($this->column_order_online_supplier_reports_match[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        } 
        else if(isset($this->order_online_supplier_reports_match))
        {
            $order = $this->order_online_supplier_reports_match;
            $this->db->order_by(key($order), $order[key($order)]);
        }

    }

    public function get_online_supplier_reports_datatable(){
        $this->_get_online_supplier_reports_datatable();
        if($_POST['length'] != -1)
        $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
         return $query->result();
    }
    public function count_filtered_online_supplier_reports_rec(){
        $this->_get_online_supplier_reports_datatable();
        $query = $this->db->get();
        return $query->num_rows();
    } 

    ?>

我在 _get_online_supplier_reports_datatable() 函数中为联合两个查询添加了一个查询。之后它不起作用

<?php
public function _get_online_supplier_reports_datatable(){

        //Query 1 here
        $this->db->select('"Amadeus" AS SupplierName,TST_Type,TKT_Type,TKTNumber,TKT_FO_REF,BasePrice,TaxPrice,Commission1,(BasePrice+TaxPrice) AS TotalPrice,"issued" AS tkt_type,createdby,TicketedDate as created_date,CONCAT(a3m.firstname," ",a3m.lastname) AS created_name,a3m.account_id,a3m.locationvalue AS Location_ID,a3m.locationdescription AS Location_Name,a3m.costcentervalue AS CostCenter_ID,a3m.costcenterdescription AS CostCenter_Name');
        $this->db->from($this->traveldb->database.".booking_price bp");
        $this->db->join('a3m_account_details a3m','a3m.account_id=createdby','left');
        $this->db->where('TKTNumber !=', '');
        $query1 = $this->db->get_compiled_select(); 

        //QUery2 Here
         $this->db->select('"Amadeus" AS SupplierName,"" AS TST_Type,"" AS  TKT_Type,br_tkt AS TKTNumber,"" as TKT_FO_REF,br_fare_paid_B as BasePrice,br_tax_refund_TXT as TaxPrice,br_cancellation_penalty as Commission1,br_fare_total_RFT AS TotalPrice,br_cancel_type AS tkt_type,br_added_by as createdby,br_refund_date as created_date,CONCAT(a3m.firstname," ",a3m.lastname) AS created_name,a3m.account_id,a3m.locationvalue AS Location_ID,a3m.locationdescription AS Location_Name,a3m.costcentervalue AS CostCenter_ID,a3m.costcenterdescription AS CostCenter_Name');
        $this->db->from($this->traveldb->database.".booking_refund bp");
        $this->db->join('a3m_account_details a3m','a3m.account_id=br_added_by','left');
        $this->db->where('br_tkt !=', '');
        $query2 = $this->db->get_compiled_select(); 

    //Union both querys 
     $query=  $this->db->query($query1." UNION ALL".$query2);

        if(isset($_POST['order'])) // here order processing
        {
            $this->db->order_by($this->column_order_online_supplier_reports_match[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        } 
        else if(isset($this->order_online_supplier_reports_match))
        {
            $order = $this->order_online_supplier_reports_match;
            $this->db->order_by(key($order), $order[key($order)]);
        }

    }

    public function get_online_supplier_reports_datatable(){
        $this->_get_online_supplier_reports_datatable();
        if($_POST['length'] != -1)
        $this->db->limit($_POST['length'], $_POST['start']);
        $query = $this->db->get();
         return $query->result();
    }
    public function count_filtered_online_supplier_reports_rec(){
        $this->_get_online_supplier_reports_datatable();
        $query = $this->db->get();
        return $query->num_rows();
    } 

主要问题是当我使用联合时会获取数据，但我不想只获取数据。只是我想联合两个查询。我所需的输出函数 _get_online_supplier_reports_datatable() 就像这段代码。

public function _get_online_supplier_reports_datatable(){

        //Query 1 here
        $query1= $this->db->select('"Amadeus" AS SupplierName,TST_Type,TKT_Type,TKTNumber,TKT_FO_REF,BasePrice,TaxPrice,Commission1,(BasePrice+TaxPrice) AS TotalPrice,"issued" AS tkt_type,createdby,TicketedDate as created_date,CONCAT(a3m.firstname," ",a3m.lastname) AS created_name,a3m.account_id,a3m.locationvalue AS Location_ID,a3m.locationdescription AS Location_Name,a3m.costcentervalue AS CostCenter_ID,a3m.costcenterdescription AS CostCenter_Name');
        $this->db->from($this->traveldb->database.".booking_price bp");
        $this->db->join('a3m_account_details a3m','a3m.account_id=createdby','left');
        $this->db->where('TKTNumber !=', '');

        //$query1 = $this->db->get_compiled_select(); 

        //QUery2 Here
        $query2=  $this->db->select('"Amadeus" AS SupplierName,"" AS TST_Type,"" AS  TKT_Type,br_tkt AS TKTNumber,"" as TKT_FO_REF,br_fare_paid_B as BasePrice,br_tax_refund_TXT as TaxPrice,br_cancellation_penalty as Commission1,br_fare_total_RFT AS TotalPrice,br_cancel_type AS tkt_type,br_added_by as createdby,br_refund_date as created_date,CONCAT(a3m.firstname," ",a3m.lastname) AS created_name,a3m.account_id,a3m.locationvalue AS Location_ID,a3m.locationdescription AS Location_Name,a3m.costcentervalue AS CostCenter_ID,a3m.costcenterdescription AS CostCenter_Name');
        $this->db->from($this->traveldb->database.".booking_refund bp");
        $this->db->join('a3m_account_details a3m','a3m.account_id=br_added_by','left');
        $this->db->where('br_tkt !=', '');

        //$query2 = $this->db->get_compiled_select(); 

    //Union both querys 
     $query=  $this->db->query($query1." UNION ALL".$query2);

        if(isset($_POST['order'])) // here order processing
        {
            $this->db->order_by($this->column_order_online_supplier_reports_match[$_POST['order']['0']['column']], $_POST['order']['0']['dir']);
        } 
        else if(isset($this->order_online_supplier_reports_match))
        {
            $order = $this->order_online_supplier_reports_match;
            $this->db->order_by(key($order), $order[key($order)]);
        }

    }

标签： phpmysqlcodeigniter