/**
* Add the constraints for a relationship count query on the same table.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param \Illuminate\Database\Eloquent\Builder $parent
* @return \Illuminate\Database\Eloquent\Builder
*/
public function getRelationCountQueryForSelfRelation(Builder $query, Builder $parent)
{
$query->select(new Expression('count(*)'));
$query->from($query->getModel()->getTable() . ' as ' . ($hash = $this->getRelationCountHash()));
$key = $this->wrap($this->getQualifiedParentKeyName());
return $query->where($hash . '.' . $this->getPlainForeignKey(), '=', new Expression($key));
}
/**
* @param EloquentBuilder $query
* @param EloquentBuilder $parent
* @param array $columns
*
* @return mixed
*/
public function getRelationQuery(EloquentBuilder $query, EloquentBuilder $parent, $columns = ['*'])
{
$query->select($columns);
$table = $query->getModel()->getTable();
$query->from($table . ' as ' . ($hash = $this->getRelationCountHash()));
$grammar = $query->getQuery()->getGrammar();
$table = $grammar->wrapTable($table);
$hash = $grammar->wrapTable($hash);
$lft = $grammar->wrap($this->parent->getLftName());
$rgt = $grammar->wrap($this->parent->getRgtName());
return $query->whereRaw("{$hash}.{$lft} between {$table}.{$lft} + 1 and {$table}.{$rgt}");
}
/**
* Constrain the given roles query to those that were assigned to the given authorities.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param string|\Illuminate\Database\Eloquent\Model|\Illuminate\Database\Eloquent\Collection $model
* @param array $keys
* @return void
*/
public function constrainWhereAssignedTo($query, $model, array $keys = null)
{
list($model, $keys) = Helper::extractModelAndKeys($model, $keys);
$query->whereExists(function ($query) use($model, $keys) {
$table = $model->getTable();
$key = "{$table}.{$model->getKeyName()}";
$pivot = Models::table('assigned_roles');
$roles = Models::table('roles');
$prefix = Models::prefix();
$query->from($table)->join($pivot, $key, '=', $pivot . '.entity_id')->whereRaw("{$prefix}{$pivot}.role_id = {$prefix}{$roles}.id")->where("{$pivot}.entity_type", $model->getMorphClass())->whereIn($key, $keys);
});
}
/**
* Add the constraints for a relationship count query on the same table.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param \Illuminate\Database\Eloquent\Builder $parent
* @return \Illuminate\Database\Eloquent\Builder
*/
public function getRelationCountQueryForSelfJoin(Builder $query, Builder $parent)
{
$query->select(new Expression('count(*)'));
$tablePrefix = $this->query->getQuery()->getConnection()->getTablePrefix();
$query->from($this->table . ' as ' . $tablePrefix . ($hash = $this->getRelationCountHash()));
$key = $this->wrap($this->getQualifiedParentKeyName());
return $query->where($hash . '.' . $this->foreignKey, '=', new Expression($key));
}
/**
* Add the constraints for a relationship count query on the same table.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param \Illuminate\Database\Eloquent\Builder $parent
* @return \Illuminate\Database\Eloquent\Builder
*/
public function getRelationCountQueryForSelfJoin(Builder $query, Builder $parent)
{
$query->select(new Expression('count(*)'));
$query->from($this->related->getTable() . ' as ' . ($hash = $this->getRelationCountHash()));
$this->related->setTable($hash);
$this->setJoin($query);
return parent::getRelationCountQuery($query, $parent);
}
/**
* Add the constraints for a relationship query on the same table.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param \Illuminate\Database\Eloquent\Builder $parent
* @param array|mixed $columns
* @return \Illuminate\Database\Eloquent\Builder
*/
public function getRelationQueryForSelfJoin(Builder $query, Builder $parent, $columns = ['*'])
{
$query->select($columns);
$query->from($this->table . ' as ' . ($hash = $this->getRelationCountHash()));
$key = $this->wrap($this->getQualifiedParentKeyName());
return $query->where($hash . '.' . $this->foreignKey, '=', new Expression($key));
}
/**
* Merge our cloned query builder with the original one.
*
* @param \Illuminate\Database\Eloquent\Builder $clone
* @param \Illuminate\Database\Eloquent\Builder $original
*/
protected function mergeQueries(Builder $clone, Builder $original)
{
$original->from(DB::connection($this->connection)->raw("({$clone->toSql()}) as `{$this->getTable()}`"));
$original->mergeBindings($clone->getQuery());
}
/**
* Add the constraints for a relationship count query on the same table.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param \Illuminate\Database\Eloquent\Builder $parent
* @return \Illuminate\Database\Eloquent\Builder
*/
public function getRelationCountQueryForSelfJoin(Builder $query, Builder $parent)
{
$query->select(new \Illuminate\Database\Query\Expression('count(*)'));
$query->from($this->table . ' as ' . ($hash = $this->getRelationCountHash()));
$key = $this->wrap($this->getQualifiedParentKeyName());
return $query->where($hash . '.' . $this->foreignKey, '=', new \Illuminate\Database\Query\Expression($key));
}
/**
* Add the constraints for a relationship query on the same table.
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param \Illuminate\Database\Eloquent\Builder $parent
* @param array|mixed $columns
* @return \Illuminate\Database\Eloquent\Builder
*/
public function getRelationQueryForSelfJoin(Builder $query, Builder $parent, $columns = ['*'])
{
$query->select($columns);
$query->from($this->related->getTable() . ' as ' . ($hash = $this->getRelationCountHash()));
$this->related->setTable($hash);
$this->setJoin($query);
return parent::getRelationQuery($query, $parent, $columns);
}
/**
* Merge our cloned query builder with the original one.
*
* @param \Illuminate\Database\Eloquent\Builder $clone
* @param \Illuminate\Database\Eloquent\Builder $original
*/
protected function mergeQueries(Builder $clone, Builder $original)
{
if ($this->getDatabaseDriver() == 'pgsql') {
$original->from(DB::connection($this->connection)->raw("({$clone->toSql()}) as {$this->getTable()}"));
} else {
$original->from(DB::connection($this->connection)->raw("({$clone->toSql()}) as `{$this->getTable()}`"));
}
$original->mergeBindings($clone->getQuery());
}
/**
* Merge our cloned query builder with the original one.
*
* @param \Illuminate\Database\Eloquent\Builder $clone
* @param \Illuminate\Database\Eloquent\Builder $original
*/
protected function mergeQueries(Builder $clone, Builder $original)
{
$prefix = App::$Database->connection($this->connection)->getTablePrefix();
$tableName = $prefix . $this->getTable();
if ($this->getDatabaseDriver() == 'pgsql') {
$original->from(App::$Database->connection($this->connection)->raw("({$clone->toSql()}) as {$tableName}"));
} else {
$original->from(App::$Database->connection($this->connection)->raw("({$clone->toSql()}) as `{$tableName}`"));
}
$original->mergeBindings($clone->getQuery());
}
/**
* 扩展查询，获取排除通识素质课的课程数据
* @author FuRongxin
* @date 2016-03-09
* @version 2.0
* @param \Illuminate\Database\Eloquent\Builder $query 查询对象
* @return \Illuminate\Database\Eloquent\Builder 查询对象
*/
public function scopeExceptGeneral($query)
{
return $query->whereNotExists(function ($query) {
$query->from('pk_kczy AS a')->whereRaw('t_a.nd = t_pk_kczy.nd AND t_a.xq = t_pk_kczy.xq AND t_a.zsjj = t_pk_kczy.zsjj AND t_a.kcxh = t_pk_kczy.kcxh')->wherePt('T')->where(function ($query) {
$query->whereXz('W')->orWhere('xz', '=', 'I')->orWhere('xz', '=', 'Y')->orWhere('xz', '=', 'Q');
});
});
}
/**
* Filter by Field ID
*
* @param \Illuminate\Database\Eloquent\Builder $query
* @param int $fieldId
* @return \Illuminate\Database\Eloquent\Builder
*/
public function scopeFieldId(Builder $query, $fieldId)
{
return $query->from('channel_grid_field_' . $fieldId);
}
/**
* 扩展查询，获取已修读课程列表
* @author FuRongxin
* @date 2016-03-10
* @version 2.0
* @param \Illuminate\Database\Eloquent\Builder $query 查询对象
* @param object $user 用户对象
* @return \Illuminate\Database\Eloquent\Builder 查询对象
*/
public function scopeStudied($query, $user)
{
return $query->whereXh($user->xh)->whereNotExists(function ($query) {
$query->from('xk_xkxx AS a')->whereNd(session('year'))->whereXq(session('term'))->whereRaw('t_a.xh = t_xk_xkxx.xh AND t_a.kcxh = t_xk_xkxx.kcxh');
});
}