/** * Get locations and number of store per location or * return specify location by name. * * @param string $name Location name * * @return \Illuminate\Support\Collection $collection */ function locations($name = '') { //Get table name dynamically $city = new \App\Models\City(); $store = new \App\Models\Store(); $citiesTbl = DB::getQueryGrammar()->wrapTable($city->getTable()); $storesTbl = DB::getQueryGrammar()->wrapTable($store->getTable()); $join = DB::table($city->getTable())->leftJoin($store->getTable(), "{$city->getTable()}.id", '=', "{$store->getTable()}.city_id"); if ($name !== '') { $join->where("{$city->getTable()}.name", 'LIKE', "%{$name}%"); } return $join->select(DB::raw("{$citiesTbl}.id, {$citiesTbl}.name, COUNT({$storesTbl}.id) AS count_store"))->groupBy("{$city->getTable()}.id")->get(); }
/** * Reverse the migrations. * * @return void */ public function down() { $table = DB::getQueryGrammar()->wrapTable('tasks'); DB::statement('ALTER TABLE ' . $table . ' CHANGE `last_run` `last_run` TIMESTAMP;'); }
/** * Applies any filters to the model. */ protected function prepareModel() { $query = $this->model->newQuery(); $selects = [$this->model->getTable() . '.*']; $tables = ['base' => $this->model->getTable()]; $joins = []; /* * Extensibility */ Event::fire('backend.list.extendQueryBefore', [$this, $query]); $this->fireEvent('list.extendQueryBefore', [$this, $query]); /* * Related custom selects, must come first */ foreach ($this->getVisibleListColumns() as $column) { if (!isset($column->relation) || !isset($column->sqlSelect)) { continue; } if (!$this->model->hasRelation($column->relation)) { throw new ApplicationException(Lang::get('backend::lang.model.missing_relation', ['class' => get_class($this->model), 'relation' => $column->relation])); } $alias = Db::getQueryGrammar()->wrap($column->columnName); $table = $this->model->makeRelation($column->relation)->getTable(); $relationType = $this->model->getRelationType($column->relation); $sqlSelect = $this->parseTableName($column->sqlSelect, $table); if (in_array($relationType, ['hasMany', 'belongsToMany', 'morphToMany', 'morphedByMany', 'morphMany', 'attachMany', 'hasManyThrough'])) { $selects[] = DbDongle::raw("group_concat(" . $sqlSelect . " separator ', ') as " . $alias); } else { $selects[] = DbDongle::raw($sqlSelect . ' as ' . $alias); } $joins[] = $column->relation; $tables[$column->relation] = $table; } if ($joins) { $query->joinWith(array_unique($joins), false); } /* * Custom select queries */ foreach ($this->getVisibleListColumns() as $column) { if (!isset($column->sqlSelect) || isset($column->relation)) { continue; } $alias = Db::getQueryGrammar()->wrap($column->columnName); $sqlSelect = $this->parseTableName($column->sqlSelect, $tables['base']); $selects[] = DbDongle::raw($sqlSelect . ' as ' . $alias); } /* * Handle a supplied search term */ if (!empty($this->searchTerm) && ($searchableColumns = $this->getSearchableColumns())) { $query->orWhere(function ($innerQuery) use($searchableColumns, $tables) { $columnsToSearch = []; foreach ($searchableColumns as $column) { if (isset($column->sqlSelect)) { $table = isset($column->relation) ? $tables[$column->relation] : 'base'; $columnName = DbDongle::raw($this->parseTableName($column->sqlSelect, $table)); } else { $columnName = $tables['base'] . '.' . $column->columnName; } $columnsToSearch[] = $columnName; } $innerQuery->searchWhere($this->searchTerm, $columnsToSearch); }); } /* * Handle sorting */ if ($sortColumn = $this->getSortColumn()) { $query->orderBy($sortColumn, $this->sortDirection); } /* * @todo Apply filters etc */ /* * Extensibility */ Event::fire('backend.list.extendQuery', [$this, $query]); $this->fireEvent('list.extendQuery', [$this, $query]); // Grouping due to the joinWith() call $query->select($selects); $query->groupBy($this->model->getQualifiedKeyName()); return $query; }
/** * Reverse the migrations. * * @return void */ public function down() { $tableName = DB::getQueryGrammar()->wrapTable('articles'); DB::statement('ALTER TABLE ' . $tableName . ' MODIFY `published_at` TIMESTAMP NULL;'); }
/** * Applies any filters to the model. */ protected function prepareModel() { $query = $this->model->newQuery(); $primaryTable = $this->model->getTable(); $selects = [$primaryTable . '.*']; $joins = []; $withs = []; /* * Extensibility */ Event::fire('backend.list.extendQueryBefore', [$this, $query]); $this->fireEvent('list.extendQueryBefore', [$query]); /* * Prepare searchable column names */ $primarySearchable = []; $relationSearchable = []; $columnsToSearch = []; if (!empty($this->searchTerm) && ($searchableColumns = $this->getSearchableColumns())) { foreach ($searchableColumns as $column) { /* * Related */ if ($this->isColumnRelated($column)) { $table = $this->model->makeRelation($column->relation)->getTable(); $columnName = isset($column->sqlSelect) ? DbDongle::raw($this->parseTableName($column->sqlSelect, $table)) : $table . '.' . $column->nameFrom; $relationSearchable[$column->relation][] = $columnName; } else { $columnName = isset($column->sqlSelect) ? DbDongle::raw($this->parseTableName($column->sqlSelect, $primaryTable)) : $primaryTable . '.' . $column->columnName; $primarySearchable[] = $columnName; } } } /* * Prepare related eager loads (withs) and custom selects (joins) */ foreach ($this->getVisibleListColumns() as $column) { if (!$this->isColumnRelated($column) || !isset($column->sqlSelect) && !isset($column->nameFrom)) { continue; } if (isset($column->nameFrom)) { $withs[] = $column->relation; } $joins[] = $column->relation; } /* * Include any relation constraints */ if ($joins) { foreach (array_unique($joins) as $join) { /* * Apply a supplied search term for relation columns and * constrain the query only if there is something to search for */ $columnsToSearch = array_get($relationSearchable, $join, []); if (count($columnsToSearch) > 0) { $query->whereHas($join, function ($_query) use($columnsToSearch) { $_query->searchWhere($this->searchTerm, $columnsToSearch); }); } } } /* * Add eager loads to the query */ if ($withs) { $query->with(array_unique($withs)); } /* * Custom select queries */ foreach ($this->getVisibleListColumns() as $column) { if (!isset($column->sqlSelect)) { continue; } $alias = Db::getQueryGrammar()->wrap($column->columnName); /* * Relation column */ if (isset($column->relation)) { $table = $this->model->makeRelation($column->relation)->getTable(); $relationType = $this->model->getRelationType($column->relation); $sqlSelect = $this->parseTableName($column->sqlSelect, $table); /* * Manipulate a count query for the sub query */ $relationObj = $this->model->{$column->relation}(); $countQuery = $relationObj->getRelationCountQuery($relationObj->getRelated()->newQuery(), $query); $joinSql = $this->isColumnRelated($column, true) ? DbDongle::raw("group_concat(" . $sqlSelect . " separator ', ')") : DbDongle::raw($sqlSelect); $joinSql = $countQuery->select($joinSql)->toSql(); $selects[] = Db::raw("(" . $joinSql . ") as " . $alias); } else { $sqlSelect = $this->parseTableName($column->sqlSelect, $primaryTable); $selects[] = DbDongle::raw($sqlSelect . ' as ' . $alias); } } /* * Apply a supplied search term for primary columns */ if (count($primarySearchable) > 0) { $query->orWhere(function ($innerQuery) use($primarySearchable) { $innerQuery->searchWhere($this->searchTerm, $primarySearchable); }); } /* * Apply sorting */ if ($sortColumn = $this->getSortColumn()) { if (($column = array_get($this->columns, $sortColumn)) && $column->sqlSelect) { $sortColumn = $column->sqlSelect; } $query->orderBy($sortColumn, $this->sortDirection); } /* * Apply filters */ foreach ($this->filterCallbacks as $callback) { $callback($query); } /* * Add custom selects */ $query->select($selects); /* * Extensibility */ Event::fire('backend.list.extendQuery', [$this, $query]); $this->fireEvent('list.extendQuery', [$query]); return $query; }