需要帮助在codeigniter中转换这段代码

Can anyone help me convert this?

任何人都可以帮我转换吗?

SELECT  id,ospos_people.first_name,ospos_people.last_name,customer_id,is_draft 
from
(SELECT  
          ospos_quotations.id,
          ospos_quotations.customer_id, 
          ospos_quotations.is_draft,
          ospos_quotations.date 
 from     ospos_quotations, ospos_quotation_items
 where  ospos_quotations.id= ospos_quotation_items.quotation_id 
 and ospos_quotation_items.line=1) AS T 
LEFT OUTER JOIN ospos_people on T.customer_id = ospos_people.person_id

2 个解决方案

#1

I have converted your query to be more optimized like this

我已将您的查询转换为更加优化,如下所示

SELECT
  id,
  ospos_people.first_name,
  ospos_people.last_name,
  customer_id,
  is_draft
from (SELECT
    ospos_quotations.id,
    ospos_quotations.customer_id,
    ospos_quotations.is_draft,
    ospos_quotations.date
      from ospos_quotations
    LEFT JOIN ospos_quotation_items
      ON ospos_quotations.id = ospos_quotation_items.quotation_id
      where ospos_quotation_items.line = 1) AS T
  LEFT OUTER JOIN ospos_people
    on T.customer_id = ospos_people.person_id

In this inner query i have used join instead of FROM table1, table2

在这个内部查询中,我使用了join而不是FROM table1,table2

You can convert the query like this

您可以像这样转换查询

You can use sub query way of codeigniter to do this for this purpose you will have to hack codeigniter. like this. Go to system/database/DB_active_rec.php Remove public or protected keyword from these functions

您可以使用codeigniter的子查询方式为此目的执行此操作,您将不得不破解codeigniter。像这样。转到system / database / DB_active_rec.php从这些函数中删除public或protected关键字

public function _compile_select($select_override = FALSE)
public function _reset_select()

Now subquery writing in available And now here is your query with active record

现在子查询写入可用现在这里是您的查询与活动记录

function myquery()
{
    $select =   array(
                    'ospos_quotations.id',
                    'ospos_quotations.customer_id', 
                    'ospos_quotations.is_draft',
                    'ospos_quotations.date'
                );
    $this->db->select($select);
    $this->db->from('ospos_quotations');
    $this->db->join('ospos_quotation_items','ospos_quotations.id= ospos_quotation_items.quotation_id ','left');
    $this->db->where('ospos_quotation_items.line',1);
    $subQuery = $this->db->_compile_select(); // get query string

    $this->db->_reset_select(); // reset the object to write new query

    unset($select);

    $select =   array(
                    'id',
                    'ospos_people.first_name',
                    'ospos_people.last_name',
                    'customer_id,is_draft' 
                );
    $this->db->select($select);             
    $this->db->from("($subQuery) AS T");
    $this->db->join('ospos_people','T.customer_id = ospos_people.person_id ','left outer');
    return $this->db->get()->result();
}

And the thing is done. Cheers!!! Note : While using sub queries you must use

事情已经完成了。干杯!!!注意:使用子查询时必须使用

$this->db->from('table_name')

instead of

$this->db->get('table_name')

which runs the query.

它运行查询。

EDITS :

Here is an alternative way. Simply use $this->db->query($query) function

这是另一种方式。只需使用$ this-> db-> query($ query)函数

function myquery()
{
    $query  =   "   SELECT
                      id,
                      ospos_people.first_name,
                      ospos_people.last_name,
                      customer_id,
                      is_draft
                    from (SELECT
                        ospos_quotations.id,
                        ospos_quotations.customer_id,
                        ospos_quotations.is_draft,
                        ospos_quotations.date
                          from ospos_quotations
                        LEFT JOIN ospos_quotation_items
                          ON ospos_quotations.id = ospos_quotation_items.quotation_id
                          where ospos_quotation_items.line = 1) AS T
                      LEFT OUTER JOIN ospos_people
                        on T.customer_id = ospos_people.person_id";
    return $this->db->query($query)->result();  
}

#2

maybe:

$sql = 'SELECT  id,ospos_people.first_name,ospos_people.last_name,customer_id,is_draft 
from
(SELECT  
          ospos_quotations.id,
          ospos_quotations.customer_id, 
          ospos_quotations.is_draft,
          ospos_quotations.date 
 from     ospos_quotations, ospos_quotation_items
 where  ospos_quotations.id= ospos_quotation_items.quotation_id 
 and ospos_quotation_items.line=1) AS T 
LEFT OUTER JOIN ospos_people on T.customer_id = ospos_people.person_id';

$this->db->query($sql);


// OR 

$this->db
    ->select('id,ospos_people.first_name,ospos_people.last_name,customer_id,is_draft')
    ->from('(SELECT  
          ospos_quotations.id,
          ospos_quotations.customer_id, 
          ospos_quotations.is_draft,
          ospos_quotations.date 
 from     ospos_quotations, ospos_quotation_items
 where  ospos_quotations.id= ospos_quotation_items.quotation_id 
 and ospos_quotation_items.line=1) AS T', false)
    ->join('ospos_people', 'T.customer_id = ospos_people.person_id', 'left outer');