mysql - 放入 CI 函数的长查询变得不起作用

Question

有没有人可以帮助我，我有 mysql 查询，我已经在 phpmyadmin 中测试了它:

select items.name, items.category, items.supplier_id, items.item_number, 
items.product_id, items.description, items.size, items.tax_included, 
items.cost_price, if(items_tier_prices.unit_price is not null, 
items_tier_prices.unit_price,items.unit_price) as unit_price, 
items.promo_price, items.start_date, items.end_date, items.reorder_level, 
items.item_id, items.allow_alt_description, items.is_serialized, 
items.image_id, items.override_default_tax, items.is_service, items.deleted 

from items, item_kit_items_formula
left join items_tier_prices
on items_tier_prices.item_id=item_kit_items_formula.item_id
left join price_tiers
on price_tiers.id=items_tier_prices.tier_id

where item_kit_items_formula.item_id=items.item_id
and item_kit_items_formula.item_kit_id=1
and (price_tiers.name is null or price_tiers.name like '%Jendela Kaca Mati Single%')

上面的查询显示结果就像我想要的那样。但是当我输入一个模型函数时(在简单查询之前就可以工作)，程序不工作。

function get_info3($item_id)
{

    $this->db->select('items.name, items.category, items.supplier_id, items.item_number, items.product_id, items.description, items.size, items.tax_included, items.cost_price, if(items_tier_prices.unit_price is not null, items_tier_prices.unit_price,items.unit_price) as unit_price, items.promo_price, items.start_date, items.end_date, items.reorder_level, items.item_id, items.allow_alt_description, items.is_serialized, items.image_id, items.override_default_tax, items.is_service, items.deleted '); 
    $this->db->from('items, item_kit_items_formula');
    $this->db->join('items_tier_prices','items_tier_prices.item_id=item_kit_items_formula.item_id','left');
    $this->db->join('price_tiers','price_tiers.id=items_tier_prices.tier_id','left');
    $this->db->where('item_kit_items_formula.item_id=items.item_id');

    $this->db->where('item_id',$item_id);
    $this->db->where('(price_tiers.name IS NULL or price_tiers.name like "%jendela kaca mati single%"');


     $query = $this->db->get();

    if($query->num_rows()==1)
    {
        return $query->row();
    }
    else
    {
        $item_obj=new stdClass();

        $fields = $this->db->list_fields('items');

        foreach ($fields as $field)
        {
            $item_obj->$field='';
        }

        return $item_obj;
    }
}

我以前的代码是: 函数 get_info($item_id) {

    $this->db->from('items');
    $this->db->where('item_id',$item_id);

    $query = $this->db->get();

    if($query->num_rows()==1)
    {
        return $query->row();
    }
    else
    {
        //Get empty base parent object, as $item_id is NOT an item
        $item_obj=new stdClass();

        //Get all the fields from items table
        $fields = $this->db->list_fields('items');

        foreach ($fields as $field)
        {
            $item_obj->$field='';
        }

        return $item_obj;
    }
}

谁能帮我看看这个CI函数？我做错了什么？ 谢谢你们!

Answer 1

function get_info3($item_id)
{

  $this->db->select('i.*,i_t_p.*,i_k_i_f.*,pt.*,if(i_t_p.unit_price is not null, i_t_p.unit_price,i.unit_price) as unit_price'); 
  $this->db->from('items as i');
  $this->db->join('items_tier_prices as i_t_p','i_t_p.item_id=i_k_i_f.item_id','left');
  $this->db->join('price_tiers as pt','pt.id=i_t_p.tier_id','left');
  $this->db->where('i_k_i_f.item_id=i.item_id');

  $this->db->where('i.item_id',$item_id);
  $this->db->where('(pt.name IS NULL or pt.name like "%jendela kaca mati single%"');



}

你必须加入表，我试图减少你的代码，但我不知道这个 item_kit_items_formula 表是在哪里加入哪个 id 所以你也必须加入这个..希望这会帮助..