php Codeigniter 的 `where` 和 `or_where`

Codeigniter's `where` and `or_where`

提问by garethdn

I'm trying to specify a query in my model

我正在尝试在我的模型中指定一个查询

$this->db
        ->select('*')
        ->from('library')
        ->where('library.rating >=', $form['slider'])
        ->where('library.votes >=', '1000')
        ->where('library.language !=', 'German')
        ->where('library.available_until >=', date("Y-m-d H:i:s"))
        ->or_where('library.available_until =', "00-00-00 00:00:00")
        ->where('library.release_year >=', $year_start)
        ->where('library.release_year <=', $year_end)
        ->join('rating_repo', 'library.id = rating_repo.id')

So, the trouble i'm having is with my or_where. I want the orto be restricted to only the available_untilfield. Currently, however, i'm getting results which have a language of German which isn't what i want. How do i restrict my or_wherefilter to the available_untilfield only?

所以，我遇到的问题是我的or_where. 我希望or仅限于该available_until领域。然而，目前我得到的结果是德语，这不是我想要的。我如何将我的or_where过滤器限制为available_until仅该字段？

回答by Yan Berk

You can modify just the two lines:

您可以只修改两行：

->where('(library.available_until >=', date("Y-m-d H:i:s"), FALSE)
->or_where("library.available_until = '00-00-00 00:00:00')", NULL, FALSE)

EDIT:

编辑：

Omitting the FALSEparameter would have placed the backticks before the brackets and make them a part of the table name/value, making the query unusable.

省略FALSE参数会将反引号放在括号之前，并使它们成为表名/值的一部分，从而使查询无法使用。

The NULLparameter is there just because the function requires the second parameter to be a value, and since we don't have one, we send NULL.

该NULL参数是存在的，只是因为功能要求的第二个参数是一个值，因为我们没有一个，我们发送NULL。

回答by m4t1t0

You can change your code to this:

您可以将代码更改为：

$where_au = "(library.available_until >= '{date('Y-m-d H:i:s)}' OR library.available_until = '00-00-00 00:00:00')";
$this->db
    ->select('*')
    ->from('library')
    ->where('library.rating >=', $form['slider'])
    ->where('library.votes >=', '1000')
    ->where('library.language !=', 'German')
    ->where($where_au)
    ->where('library.release_year >=', $year_start)
    ->where('library.release_year <=', $year_end)
    ->join('rating_repo', 'library.id = rating_repo.id');

Tip: to watch the generated query you can use

提示：要观看生成的查询，您可以使用

echo $this->db->last_query(); die();

回答by MERT DO?AN

You may group your library.available_untilwheres area by groupingmethod of Codeigniter for without disable escaping where clauses.

您可以通过 Codeigniter 的分组方法对您的library.available_untilwheres 区域进行分组，而无需禁用转义 where 子句。

$this->db
    ->select('*')
    ->from('library')
    ->where('library.rating >=', $form['slider'])
    ->where('library.votes >=', '1000')
    ->where('library.language !=', 'German')
    ->group_start() //this will start grouping
    ->where('library.available_until >=', date("Y-m-d H:i:s"))
    ->or_where('library.available_until =', "00-00-00 00:00:00")
    ->group_end() //this will end grouping
    ->where('library.release_year >=', $year_start)
    ->where('library.release_year <=', $year_end)
    ->join('rating_repo', 'library.id = rating_repo.id')

Reference: https://www.codeigniter.com/userguide3/database/query_builder.html#query-grouping

参考：https: //www.codeigniter.com/userguide3/database/query_builder.html#query-grouping

回答by c.brahim

You can use : Query grouping allows you to create groups of WHERE clauses by enclosing them in parentheses. This will allow you to create queries with complex WHERE clauses. Nested groups are supported. Example:

您可以使用： 查询分组允许您通过将WHERE 子句括在括号中来创建 WHERE 子句组。这将允许您使用复杂的 WHERE 子句创建查询。支持嵌套组。例子：

$this->db->select('*')->from('my_table')
        ->group_start()
                ->where('a', 'a')
                ->or_group_start()
                        ->where('b', 'b')
                        ->where('c', 'c')
                ->group_end()
        ->group_end()
        ->where('d', 'd')
->get();

https://www.codeigniter.com/userguide3/database/query_builder.html#query-grouping

https://www.codeigniter.com/userguide3/database/query_builder.html#query-grouping

回答by Ali ?zy?ld?r?m

$this->db->where('(a = 1 or a = 2)');