How to query data in yii2

data query

User::find()->all();    此方法返回所有数据；
User::findOne($id);   此方法返回 主键 id=1  的一条数据(举个例子)； 
User::find()->where(['name' => '小伙儿'])->one();   此方法返回 ['name' => '小伙儿'] 的一条数据；
User::find()->where(['name' => '小伙儿'])->all();   此方法返回 ['name' => '小伙儿'] 的所有数据；
User::find()->orderBy('id DESC')->all();   此方法是排序查询；
User::findBySql('SELECT * FROM user')->all();  此方法是用 sql  语句查询 user 表里面的所有数据；
User::findBySql('SELECT * FROM user')->one();  此方法是用 sql  语句查询 user 表里面的一条数据；
User::find()->andWhere(['sex' => '男', 'age' => '24'])->count('id');   统计符合条件的总条数；
User::find()->one();    此方法返回一条数据；
User::find()->all();    此方法返回所有数据；
User::find()->count();    此方法返回记录的数量；
User::find()->average();    此方法返回指定列的平均值；
User::find()->min();    此方法返回指定列的最小值 ；
User::find()->max();    此方法返回指定列的最大值 ；
User::find()->scalar();    此方法返回值的第一行第一列的查询结果；
User::find()->column();    此方法返回查询结果中的第一列的值；
User::find()->exists();    此方法返回一个值指示是否包含查询结果的数据行；
User::find()->batch(10);  每次取 10 条数据 
User::find()->each(10);  每次取 10 条数据， 迭代查询

Multiple table query:

/* 多表联查 */
$model=new Customer();
$model->fiind()->join(‘LEFT JOIN‘,‘student‘,‘student.cid=customer.id‘)
            ->where(‘student.id‘=>\Yii::$app->user->id)
            ->andwhere(‘is_ok=1‘)
            ->one()

Related query

Using the AR method, you can also query the data table Related data (for example, selecting data from table A can pull out related data from table B). With AR, the returned related data join is just like joining the properties of the AR object to the related main table.

After establishing the association, you can obtain an array of Order objects through $customer->orders, which represents the order set of the current customer object.

Define the association using a getter method that can return the [[yii\db\ActiveQuery]] object. The [[yii\db\ActiveQuery]] object has information about the associated context, so you can only query the associated data. .

class Customer extends \yii\db\ActiveRecord
{
    public function getOrders()
    {
        // 客户和订单通过 Order.customer_id -> id 关联建立一对多关系
        return $this->hasMany(Order::className(), ['customer_id' => 'id']);
    }
}
 
class Order extends \yii\db\ActiveRecord
{
    // 订单和客户通过 Customer.id -> customer_id 关联建立一对一关系
    public function getCustomer()
    {
        return $this->hasOne(Customer::className(), ['id' => 'customer_id']);
    }
}

The above uses the [[yii\db\ActiveRecord::hasMany()]] and [[yii\db\ActiveRecord::hasOne()]] methods. The above two examples are modeling examples of many-to-one relationships and one-to-one relationships in associated data respectively. For example, a customer has many orders, and one order only belongs to one customer. Both methods have two parameters and return [[yii\db\ActiveQuery]] objects.

After establishing the association, obtaining the associated data is as simple as obtaining the component attributes. Just execute the following corresponding getter methods:

// 取得客户的订单
$customer = Customer::findOne(1);
$orders = $customer->orders; // $orders 是 Order 对象数组

The above code actually executes the following two SQL statements:

SELECT * FROM customer WHERE id=1;
SELECT * FROM order WHERE customer_id=1;

Sometimes it is necessary to pass parameters in the related query. For example, if you do not need to return all the customer's orders, you only need to return the large orders whose purchase amount exceeds the set value. Declare a related data bigOrders through the following getter method:

class Customer extends \yii\db\ActiveRecord
{
    public function getBigOrders($threshold = 100)
    {
        return $this->hasMany(Order::className(), ['customer_id' => 'id'])
            ->where('subtotal > :threshold', [':threshold' => $threshold])
            ->orderBy('id');
    }
}

Join Query

When using a relational database, what is commonly done is to join multiple tables and explicitly use various JOIN queries. The query conditions and parameters of the JOIN SQL statement can be implemented by using [[yii\db\ActiveQuery::joinWith()]] to reuse the defined relationship and call it instead of using [[yii\db\ActiveQuery::join()]] Target.

// 查找所有订单并以客户 ID 和订单 ID 排序，并贪婪加载 "customer" 表
$orders = Order::find()->joinWith('customer')->orderBy('customer.id, order.id')->all();
// 查找包括书籍的所有订单，并以 `INNER JOIN` 的连接方式即时加载 "books" 表
$orders = Order::find()->innerJoinWith('books')->all();

The above method [[yii\db\ActiveQuery::innerJoinWith()|innerJoinWith()]] is to access the INNER JOIN type [[yii\db\ActiveQuery::joinWith()

|joinWith()]] shortcut.

You can connect one or more related relationships, you can freely use query conditions to related queries, and you can also nest connected related queries. For example:

// 连接多重关系
// 找出24小时内注册客户包含书籍的订单
$orders = Order::find()->innerJoinWith([
    'books',
    'customer' => function ($query) {
        $query->where('customer.created_at > ' . (time() - 24 * 3600));
    }
])->all();
// 连接嵌套关系：连接 books 表及其 author 列
$orders = Order::find()->joinWith('books.author')->all();

Behind the code, Yii first executes a JOIN SQL statement to find out the main models that meet the query conditions of the JOIN SQL statement, then executes a query statement for each relationship, and bing fills in the corresponding associated records.

The difference between [[yii\db\ActiveQuery::joinWith()|joinWith()]] and [[yii\db\ActiveQuery::with()|with()]] is that the former connects to the main model class and the data table of the associated model class to retrieve the main model, while the latter only queries and retrieves the main model class. Retrieve the main model

Because of this difference, you can apply query conditions that only work against a JOIN SQL statement. For example, filter the main model through the query conditions of the associated model. As in the previous example, you can use the columns of the associated table to select the main model data.

When using [[yii\db\ActiveQuery::joinWith()|joinWith( )]] method can respond to unambiguous column names. In the above examples, we useitem.id and order.id to disambiguate the id column references because both the order table and the items table include the id column.

When connecting to an association, the association uses instant loading by default. You can decide whether to use eager loading in the specified related query by passing the parameter $eagerLoading.

Default [[yii\db\ActiveQuery::joinWith()|joinWith()]] uses left joins to join related tables. You can also pass the $joinType parameter to customize the join type. You can also use [[yii\db\ActiveQuery::innerJoinWith()|innerJoinWith()]].

Yii2 Paging

Any method in the controller CommentController, here my method is actionComment();

use yii\data\Pagination;
use app\models\Comment;
  public function actionComment(){
       $data = Comment::find()->andWhere(['id' => '10']);
       $pages = new Pagination(['totalCount' =>$data->count(), 'pageSize' => '2']);
       $model = $data->offset($pages->offset)->limit($pages->limit)->all();
       
       return $this->render('comment',[
             'model' => $model,
             'pages' => $pages,
       ]);
  }

view Code inside

<?php
use yii\widgets\LinkPager;
?>
      foreach($model as $key=>$val)
      {
           这里就是遍历数据
      }
      <?= LinkPager::widget([&#39;pagination&#39; => $pages]); ?>

in() operation

SELECT * FROM `categ_price` WHERE `id` IN (9, 11)

$categ_price_id=[9>1,11=>3]
  $categPriceModel= \common\models\CategPrice::find()->where(['id' =>array_keys($categ_price_id)])->all();
  #>where(['id' => [1, 2, 3]])

not in() operation

SELECT * FROM `shop` WHERE (status=1) AND (`id` NOT IN (88, 93))

$shopModel= Shop::find()->where('status=1')->andWhere(['not in','id',[88,98]])->all();

PHP中文网, there are a lot of free Yii introductory tutorials, everyone is welcome to learn!

The above is the detailed content of How to query data in yii2. For more information, please follow other related articles on the PHP Chinese website!