protected function actionAjax1() { $request = $this->getRequest(); $container = $this->getContainer(); /** @var \Zero\Module\Metronic\AdminModule\Database\MySQL\TestDatabase $db */ $db = $container->tag('metronic_test_db'); // 查询 information_schema的schemata表 $table_name = $db->getTableName('schemata'); $schema_sql = "show columns from {$table_name}"; $query = $db->getSelectQuery($schema_sql); $schema_data = $query->getResult(); $data_table = array('head' => array(), 'body' => array()); $fields = array(); foreach ($schema_data as $i => $row) { if ($i < 5) { $data_table['head'][] = $row['Field']; $fields[] = $row['Field']; } } $posts = $request->request; $page_offset = $posts->get('start'); $page_offset = intval($page_offset); $page_size = $posts->get('length'); $page_size = intval($page_size); $is_all = false; if ($page_size < 0) { $is_all = true; } $s_echo = $posts->get('draw'); $s_echo = intval($s_echo); $search = $posts->get('search'); $search_value = $search['value']; $records = array(); $records['data'] = array(); $records['draw'] = $s_echo; $records['recordsTotal'] = 0; $records['recordsFiltered'] = 0; $columns = $fields; $orders = $posts->get('order'); $sorts = array(); foreach ($orders as $order) { $col = $order['column']; $dir = $order['dir']; $sorts[] = array('field' => $columns[$col], 'direction' => $dir); } $pager = null; // 直接搜索所有 $sql = "select count(*) as `num` from {$table_name}"; $query = $db->getSelectQuery($sql); $row = $query->getRow(); $total = $row['num']; if ($is_all) { $page_size = $total; } $page = $page_offset / $page_size + 1; $qb = $db->createQueryBuilder(); $qb->select($fields)->from($table_name)->setFirstResult(max(0, ($page - 1) * $page_size))->setMaxResults($page_size); if (!empty($sorts)) { foreach ($sorts as $sort) { $field = "`{$sort['field']}`"; $direction = $sort['direction']; $qb->addOrderBy($field, $direction); } } if ($search_value) { foreach ($fields as $field) { $qb->orWhere($qb->expr()->eq("`{$field}`", ":{$field}"))->setParameter(":{$field}", $search_value); } } $sql = $qb->getSQL(); $query = $db->getSelectQuery($sql, $qb->getParameters()); $pager = new Pager($query->getResult(), $total, $page, $page_size); $total = $pager->getCount(); $records['recordsTotal'] = $total; $records['recordsFiltered'] = $total; $data = $pager->getData(); foreach ($data as $k => $v) { $line = array(); foreach ($v as $kk => $vv) { if ($vv) { $line[] = $vv; } else { $line[] = ' '; } } $records['data'][] = $line; } return new JsonResponse($records); }