php 如何在 CodeIgniter 中结合 query() 和 limit() 方法

how to combine query() and limit() methods in CodeIgniter

提问by dot

Is it possible to do something like this in codeigniter:

是否可以在 codeigniter 中做这样的事情：

publi function getcat($category_id)
{
    $query = "(SELECT cat.id, cat.title ";
    $query = $query . "FROM bf_categories cat ";
    $query = $query . "WHERE cat.id=" . $category_id;
    $this->db->limit(2, 4);
    $records = $this->db->query($query);
    return $records->result();
}

I've simplified the query just for demo purposes... but it's actually quite complex, which is why I've decided to use the query()method.

我只是出于演示目的简化了查询……但它实际上非常复杂，这就是我决定使用该query()方法的原因。

But the limit clause is not being included in the query... I've verified by enabling the codeigniter profiler in my controller and I can see that the query is run with out any limit clause.

但是 limit 子句没有包含在查询中......我已经通过在我的控制器中启用 codeigniter profiler 进行了验证，我可以看到查询是在没有任何 limit 子句的情况下运行的。

Can you tell me how I can accomplish this using the query()method?

您能告诉我如何使用该query()方法完成此操作吗？

Edit 1

编辑 1

I've modified my model to look like this:

我已经修改了我的模型，看起来像这样：

public function get_categories_and_products($limit=5, $offset=0, $category_id=null)
{
    print "<BR>the function got the following offeset: $offset and limit: $limit";
    $query = "(SELECT cat.category_id, cat.title, cat.image_thumb, cat.deleted, cat.display_weight ";
            $query = $query."FROM bf_categories cat ";
            $query = $query."WHERE cat.parent_id=".$category_id;
    $query = $query." AND cat.category_id <>".$category_id;
    $query = $query.") UNION (";
    $query = $query."SELECT p.product_id, p.name, p.image_thumb, p.deleted , p.display_weight";
    $query = $query." FROM bf_product p ";
    $query = $query."Inner join bf_product_category cp ";
    $query = $query."on p.product_id=cp.product_id ";
    $query = $query."Where cp.category_id=".$category_id.") ?";

            $records = $this->db->query($query,array($this->db->limit(2, 4)));
            return $records->result();
     }

I've hardcoded the limit values for now... but ultimately, i'll be using the values that get passed into the method. Unfortunately, this code still does not work. According to the profiler, here's what's being executed:

我现在已经对限制值进行了硬编码……但最终，我将使用传递到方法中的值。不幸的是，这段代码仍然不起作用。根据分析器，这是正在执行的内容：

(SELECT cat.category_id, cat.title, cat.image_thumb, cat.deleted, cat.display_weight FROM bf_categories cat WHERE cat.parent_id=3 AND cat.category_id <>3) UNION (SELECT p.product_id, p.name, p.image_thumb, p.deleted , p.display_weight FROM bf_product p Inner join bf_product_category cp on p.product_id=cp.product_id Where cp.category_id=3)

SELECT * FROM (`bf_categories`) WHERE `category_id` = '3' LIMIT 4, 2

So it's creating two separate select statements.

所以它创建了两个单独的选择语句。

采纳答案by dot

In the end, I'm doing everything manually.

最后，我正在手动完成所有操作。

public function get_categories_and_products($limit=10, $offset=0, $category_id=null)
{
    $query = "(SELECT cat.category_id, cat.title, cat.image_thumb, cat.deleted, cat.display_weight ";
    $query = $query."FROM bf_categories cat ";
    $query = $query."WHERE cat.parent_id=".$category_id;
    $query = $query." AND cat.category_id <>".$category_id;
    $query = $query.") UNION (";
    $query = $query."SELECT p.product_id, p.name, p.image_thumb, p.deleted , p.display_weight";
    $query = $query." FROM bf_product p ";
    $query = $query."Inner join bf_product_category cp ";
    $query = $query."on p.product_id=cp.product_id ";
    $query = $query."Where cp.category_id=".$category_id.") limit ".$offset.','.$limit;
    $catsandprods= $this->db->query($query);
    return $catsandprods->result();
}

回答by kishor soneji

I tried this way, multiple join with start and limit. you remove join as per choice.

我试过这种方式，多个连接开始和限制。您根据选择删除加入。

    $this->db->select('*');
    $this->db->from('tbl_requestservice as r');
    $this->db->join('tbl_service as s','s.service_id=r.service_id');
    $this->db->limit($limit, $start);
    $query = $this->db->get();
    print_r($query->result());
    return $query->result();

回答by Grigor

This is how a query looks like in CI:

这是查询在 CI 中的样子：

$this->db->select("id, title");
$this->db->where("id", $category_id);
$this->db->from("bf_categories");
$this->db->limit($limit);
$query = $this->db->get();

if($query->num_rows() > 0)
{
   foreach($query->result() as $row)
   {
      echo $row->title;
   }
}

If you include the full query, it would be easier to solve the problem or show you a better and more efficient approach.

如果您包含完整的查询，那么解决问题或向您展示更好、更有效的方法会更容易。

回答by Vilintritenmert

http://code-igniter.ru/user_guide/database/active_record.html

http://code-igniter.ru/user_guide/database/active_record.html

$this->db->start_cache();
$this->db->select('field1');
$this->db->stop_cache();
$this->db->get('tablename');

SELECT field1FROM (tablename)

field1从 ( tablename) 中选择

$this->db->select('field2');
$this->db->limit(10);
$this->db->get('tablename');

// SELECT field1, field2FROM (tablename) limit 10

// SELECT field1, field2FROM ( tablename) 限制 10

$this->db->flush_cache();
$this->db->select('field2');
$this->db->get('tablename');

// SELECT field2FROM (tablename)

//field2从 ( tablename) 中选择

回答by Arun Killu

you can use

您可以使用

$query = "(SELECT cat.id, cat.title ";
$query = $query."FROM bf_categories cat ";
$query = $query."WHERE cat.id=".$category_id." ?";

$this->db->query($query,array($this->db->limit(2, 4)));

or

或者

 $this->db->select('id,title')->from('bf_categories ')->where('id', $id)->limit(2, 4);

link to doc

链接到文档