/**
  * 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;');
 }
Exemple #3
0
 /**
  * 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;');
 }
Exemple #5
0
 /**
  * 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;
 }