public function getQuery()
{
$query = new Query(Yii::app()->db);
$query->from('groupon as g');
if ($this->id) {
$query->andWhere('g.id=:id', array(':id' => $this->id));
}
if ($this->title) {
$query->andWhere(array('like', 'g.title', '%' . $this->title . '%'));
}
if ($this->state) {
$now = time();
switch ($this->state) {
case 'online':
//在线
$query->andWhere('g.begin_time<=:time and g.end_time>=:time', array(':time' => $now));
break;
case 'offline':
//下线
$query->andWhere('g.end_time<:time', array(':time' => $now));
break;
case 'beonline':
//即将上线
$query->andWhere('g.begin_time>:time', array(':time' => $now));
break;
default:
break;
}
}
return $query;
}
/**
* 根据分类id获取分类名
* @param type $id
* @param type $level
* @return type
*/
public static function getCateName($id, $level = null)
{
$query = new Query(Yii::app()->db);
$query->select('name');
$query->from('groupon_cates');
$query->andWhere('id=:id', array(':id' => $id));
if ($level) {
$query->andWhere('level=:level', array(':level' => $level));
}
$name = $query->queryScalar();
return $name;
}
public function getQuery()
{
$query = new Query(Yii::app()->db);
$query->select('id,biz_id,name,city_id,area_id,is_reservation');
$query->from('groupon_biz_shop');
if ($this->biz_id) {
$query->andWhere('biz_id=:bid', array(':bid' => $this->biz_id));
}
if ($this->name) {
$query->andWhere(array('like', 'name', '%' . $this->name . '%'));
}
return $query;
}
public function getQuery()
{
$query = new Query(Yii::app()->db);
$query->from('groupon_biz');
$query->andWhere('display=' . ARBiz::DISPLAY);
if ($this->id) {
$query->andWhere('id=:id', array(':id' => trim($this->id)));
}
if ($this->title) {
$query->andWhere(array('like', 'title', '%' . $this->title . '%'));
}
if ($this->status) {
$query->andWhere('examine_status=:status', array(':status' => $this->status));
}
$query->order('id desc');
return $query;
}
public function test()
{
/*
* $query=new Query();
$query->addSelect(['g.*'])
->from ([Order::tableName().' o'])
->leftJoin(Position::tableName().' p','o.id = p.id_order')
->leftJoin(Good::tableName(). ' g','p.id_good = g.id')
->where(['o.id'=>':id'],[':id'=>$id]);
return $query->all();
*
*/
$query = new Query();
$query->from('list');
return $query->all();
}
/**
* 获取子市区的键值对列表
* @param int/null $pid 父ID
* @param int $grade 等级
* @return array
*/
public static function getAreas($pid = null, $grade = self::GRADE_PROVINCE)
{
$key = 'arealist_' . $grade . '_' . $pid;
$areas = Yii::app()->cache->get($key);
// dump($areas);
if ($areas === false) {
$query = new Query(Yii::app()->db);
$query->select('id,name');
$query->from('area');
if ($pid == null) {
$query->andWhere('parent_id is null');
} else {
$query->andWhere('parent_id=:pid', array(':pid' => $pid));
}
$areas = $query->queryAll();
if (!empty($areas)) {
$areas = A::map($areas, 'id', 'name');
}
Yii::app()->cache->set($key, $areas, 60 * 60 * 24);
}
// dump($areas);
return $areas;
}
/**
* Parse from statement
*
* @param Query $query
* @param string $inputString
*
* @return string
*/
private function from(Query $query, $inputString)
{
if (substr($inputString, 0, 1) == '(') {
$fromString = $this->getWord($inputString, ')');
$inputString = $this->trimString($inputString, $fromString . ')');
$fromString = str_replace(array('(', ')'), '', $fromString);
$query->from(explode(', ', $fromString));
} else {
$from = $this->getWord($inputString);
$inputString = $this->trimString($inputString, $from);
$query->from($from);
}
return $inputString;
}
function from($table)
{
$query = new Query();
return $query->from($table);
}
/**
* Constructor.
*
* Provide a Query object to run this query and return found objects.
* Provide an integer to look for this ID (or $column).
* Provide an array to create an new object filled with these values.
*
* @param mixed $mixed What to get?
* @param mixed $column Column to search in.
*
* @return void
*/
public function __construct($mixed = null, $column = 'id')
{
//set modelname
$this->modelName = get_called_class();
//set table name
if (empty($this->tableName)) {
$this->tableName = Config::getSetting('db_table_prefix') . strtolower($this->modelName);
}
//cache fields
parent::__construct();
//determine action
if ($mixed !== null) {
if (is_object($mixed) && get_class($mixed) == 'Query' && Clockwork::isModuleLoaded('Data/Query')) {
if ($column == 1) {
$this->values = $mixed->from(substr($this->tableName, strlen(Config::getSetting('db_table_prefix'))), null, true, true)->limit(1)->run(1);
if (empty($this->values)) {
$this->setError(404, 'Object not found');
}
} else {
$this->objects = $this->createObjects($mixed->from(substr($this->tableName, strlen(Config::getSetting('db_table_prefix'))), null, true, true)->run());
}
} else {
if (is_array($mixed)) {
foreach ($mixed as $key => $value) {
$this->set($key, $value);
}
} else {
if (Clockwork::isModuleLoaded('Data/Query')) {
$query = new Query();
$this->values = $query->from(strtolower($this->modelName))->where($column . " = '" . $mixed . "'")->run(1);
if (empty($this->values)) {
$this->setError(404, 'Object not found');
}
}
}
}
}
}
public static function queryBuilder()
{
$query = new Query();
// $user = new Query();
// $userQuery = $user->select('id')->from('tbl_posts')->where(['title' => 'php'])->one();
/**
* group by statement
*/
// return $query->select(['title', 'name'])->from('tbl_job')
// ->join('inner join', 'tbl_category', 'tbl_job.category_id = tbl_category.id')
// ->where(['>', 'tbl_job.category_id', 6])->all();
$category_id = [2, 3, 4];
/**
* like and order
*/
//return $query->select(['title', 'category_id'])->from('tbl_job')
// ->where(['like','title', ['php', 'java']])->orderBy(['category_id' => SORT_DESC])
// ->all();
return $query->from('tbl_job')->orderBy('id');
}
public function testDeleteStatement_AddTable()
{
$query = new Query("DELETE FROM `test`");
$query->from("abc", Query::APPEND);
$this->assertEquals("DELETE FROM `test` , `abc`", (string) $query);
}
