PHP DB::getTablePrefix方法代码示例

 /**
  * Show the application dashboard to the user.
  *
  * @return Response
  */
 public function index()
 {
     $articles = Article::with('author')->orderBy('position', 'DESC')->orderBy('created_at', 'DESC')->limit(4)->get();
     //		TODO: abstract to model
     $sliders = Photo::join('photo_albums', 'photo_albums.id', '=', 'photos.photo_album_id')->where('photos.slider', 1)->orderBy('photos.position', 'DESC')->orderBy('photos.created_at', 'DESC')->select('photos.filename', 'photos.name', 'photos.description', 'photo_albums.folder_id')->get();
     $photoAlbums = PhotoAlbum::select(array('photo_albums.id', 'photo_albums.name', 'photo_albums.description', 'photo_albums.folder_id', DB::raw('(select filename from ' . DB::getTablePrefix() . 'photos WHERE album_cover=TRUE and ' . DB::getTablePrefix() . 'photos.photo_album_id=' . DB::getTablePrefix() . 'photo_albums.id LIMIT 1) AS album_image'), DB::raw('(select filename from ' . DB::getTablePrefix() . 'photos WHERE ' . DB::getTablePrefix() . 'photos.photo_album_id=' . DB::getTablePrefix() . 'photo_albums.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
     $videoAlbums = VideoAlbum::select(array('video_albums.id', 'video_albums.name', 'video_albums.description', 'video_albums.folder_id', DB::raw('(select youtube from ' . DB::getTablePrefix() . 'videos WHERE album_cover=TRUE and ' . DB::getTablePrefix() . 'videos.video_album_id=' . DB::getTablePrefix() . 'video_albums.id LIMIT 1) AS album_image'), DB::raw('(select youtube from ' . DB::getTablePrefix() . 'videos WHERE ' . DB::getTablePrefix() . 'videos.video_album_id=' . DB::getTablePrefix() . 'video_albums.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
     $categoriesArray = Object::where('type', 'category')->get();
     $categories = array();
     foreach ($categoriesArray as $category) {
         $categories[] = array($category->title);
     }
     $categories = Object::where('type', 'category')->get();
     global $types;
     $types = Object::getTypes()->select(array('id', DB::raw("REPLACE(name, '_object_type_', '') as name"), DB::raw("REPLACE(title, 'Object Type: ', '') as title"), 'created_at'))->get();
     return view('pages.home', compact('articles', 'sliders', 'videoAlbums', 'photoAlbums', 'categories', 'types'));
     //return view('pages.welcome');
 }

 public function getSearch()
 {
     $categoryId = Input::get('categoryid');
     $search = Input::get('query');
     $objects = null;
     if ($categoryId) {
         // Category Image
         if ($featuredImageId = ObjectMeta::getValue($categoryId, '_featured_image')) {
             $featuredImageUrl = getImageSrc($featuredImageId, 'thumbnail');
         }
         // Get objects where in category
         $objects = Object::Where('type', 'object_type')->whereExists(function ($query) use($categoryId) {
             $query->select(DB::raw(1))->from('object_meta')->whereRaw(DB::getTablePrefix() . 'object_meta.object_id = ' . DB::getTablePrefix() . 'objects.id')->where('meta_key', '_category_id')->where('meta_value', $categoryId);
         })->select(DB::raw('substr(name, 14) as field_name'))->get()->toArray();
         $types = array_map(function ($v) {
             return $v['field_name'];
         }, $objects);
         if (!empty($types)) {
             $objects = Object::whereIn('type', $types);
         }
     }
     if ($objects) {
         $objects = $objects->select(array('objects.id', DB::raw('"/uploads/' . $featuredImageUrl . '"' . ' as featured_image'), 'objects.name', 'objects.title', 'objects.excerpt'))->get();
     }
     return $objects;
 }

 public function scopeCreatedAt($query)
 {
     return $query->where(function ($query) {
         $prefix = DB::getTablePrefix();
         $this_table = $prefix . $this->table;
         $query->select(DB::raw('count(*) as resultcount'), DB::raw('ROUND(UNIX_TIMESTAMP(created_at)/(15 * 60)) AS timekey'))->from($prefix . 'results')->groupBy('timekey');
     });
 }

 /**
  * Get all galleries with files_count for back end.
  *
  * @return \Illuminate\Database\Eloquent\Collection
  */
 public function allRaw()
 {
     $query = $this->make()->select('galleries.id', 'name', 'image', 'status', DB::raw('COUNT(' . DB::getTablePrefix() . 'files.id) as files_count'))->leftJoin('files', function ($join) {
         $join->on('gallery_id', '=', 'galleries.id');
     })->groupBy('galleries.id')->order();
     // Get
     return $query->get();
 }

 public function index()
 {
     $news = $this->news->orderBy('position', 'DESC')->orderBy('created_at', 'DESC')->limit(4)->get();
     $sliders = Photo::join('photo_album', 'photo_album.id', '=', 'photo.photo_album_id')->where('photo.slider', 1)->orderBy('photo.position', 'DESC')->orderBy('photo.created_at', 'DESC')->select('photo.filename', 'photo.name', 'photo.description', 'photo_album.folderid')->get();
     $photoalbums = PhotoAlbum::select(array('photo_album.id', 'photo_album.name', 'photo_album.description', 'photo_album.folderid', DB::raw('(select filename from ' . DB::getTablePrefix() . 'photo WHERE album_cover=TRUE and ' . DB::getTablePrefix() . 'photo.photo_album_id=' . DB::getTablePrefix() . 'photo_album.id) AS album_image'), DB::raw('(select filename from ' . DB::getTablePrefix() . 'photo WHERE ' . DB::getTablePrefix() . 'photo.photo_album_id=' . DB::getTablePrefix() . 'photo_album.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
     $videoalbums = VideoAlbum::select(array('video_album.id', 'video_album.name', 'video_album.description', 'video_album.folderid', DB::raw('(select youtube from ' . DB::getTablePrefix() . 'video as v WHERE album_cover=TRUE and v.video_album_id=' . DB::getTablePrefix() . 'video_album.id) AS album_image'), DB::raw('(select youtube from ' . DB::getTablePrefix() . 'video WHERE ' . DB::getTablePrefix() . 'video.video_album_id=' . DB::getTablePrefix() . 'video_album.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
     return view('site.home.index', compact('news', 'sliders', 'videoalbums', 'photoalbums'));
 }

 /**
  * Show the application dashboard to the user.
  *
  * @return Response
  */
 public function index()
 {
     $articles = Article::with('author')->orderBy('position', 'DESC')->orderBy('created_at', 'DESC')->limit(4)->get();
     //		TODO: abstract to model
     $sliders = Photo::join('photo_albums', 'photo_albums.id', '=', 'photos.photo_album_id')->where('photos.slider', 1)->orderBy('photos.position', 'DESC')->orderBy('photos.created_at', 'DESC')->select('photos.filename', 'photos.name', 'photos.description', 'photo_albums.folder_id')->get();
     $photoAlbums = PhotoAlbum::select(array('photo_albums.id', 'photo_albums.name', 'photo_albums.description', 'photo_albums.folder_id', DB::raw('(select filename from ' . DB::getTablePrefix() . 'photos WHERE album_cover=TRUE and ' . DB::getTablePrefix() . 'photos.photo_album_id=' . DB::getTablePrefix() . 'photo_albums.id LIMIT 1) AS album_image'), DB::raw('(select filename from ' . DB::getTablePrefix() . 'photos WHERE ' . DB::getTablePrefix() . 'photos.photo_album_id=' . DB::getTablePrefix() . 'photo_albums.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
     $videoAlbums = VideoAlbum::select(array('video_albums.id', 'video_albums.name', 'video_albums.description', 'video_albums.folder_id', DB::raw('(select youtube from ' . DB::getTablePrefix() . 'videos WHERE album_cover=TRUE and ' . DB::getTablePrefix() . 'videos.video_album_id=' . DB::getTablePrefix() . 'video_albums.id LIMIT 1) AS album_image'), DB::raw('(select youtube from ' . DB::getTablePrefix() . 'videos WHERE ' . DB::getTablePrefix() . 'videos.video_album_id=' . DB::getTablePrefix() . 'video_albums.id ORDER BY position ASC, id ASC LIMIT 1) AS album_image_first')))->limit(8)->get();
     return view('pages.home', compact('articles', 'sliders', 'videoAlbums', 'photoAlbums'));
     //return view('pages.welcome');
 }

 public function getSearch()
 {
     $categoryId = Input::get('categoryid');
     $index = Input::get('index') ?: 0;
     $search = Input::get('query');
     $objects = null;
     if ($categoryId) {
         // Category Image
         if ($categoryFeaturedImageId = ObjectMeta::getValue($categoryId, '_featured_image')) {
             $featuredImageUrl = getImageSrc($categoryFeaturedImageId, 'thumbnail');
         }
         // Get objects where in category
         $objects = Object::Where('type', 'object_type')->whereExists(function ($query) use($categoryId) {
             $query->select(DB::raw(1))->from('object_meta')->whereRaw(DB::getTablePrefix() . 'object_meta.object_id = ' . DB::getTablePrefix() . 'objects.id')->where('meta_key', '_category_id')->where('meta_value', $categoryId);
         })->select(DB::raw('substr(name, 14) as field_name'))->get()->toArray();
         $types = array_map(function ($v) {
             return $v['field_name'];
         }, $objects);
         $objects = DB::table('objects')->whereIn('type', $types)->select(array('objects.id', DB::raw('"/uploads/' . $featuredImageUrl . '"' . ' as featured_image'), 'objects.name', 'objects.title', 'objects.excerpt'));
         $objects = DB::table('objects')->whereIn('type', $types)->select(array('objects.id', DB::raw('"/uploads/' . $featuredImageUrl . '"' . ' as featured_image'), 'objects.name', 'objects.title', 'objects.excerpt'))->whereExists(function ($query) {
             $query->select(DB::raw(1))->from('object_meta')->whereRaw(DB::getTablePrefix() . 'object_meta.object_id = ' . DB::getTablePrefix() . 'objects.id')->where('meta_key', '_field_promoted')->where('meta_value', '1');
         })->union($objects);
     } else {
         if ($search) {
             $objects = Object::whereNotIn('type', ['object_type', 'image'])->where(function ($query) use($search) {
                 $query->where('title', 'LIKE', '%' . $search . '%')->orWhere('name', 'LIKE', '%' . $search . '%');
             })->select('id', 'type', 'title')->get();
             return response($objects);
         }
     }
     if ($objects) {
         $objects = $objects->skip($index)->take(50)->get();
         foreach ($objects as $object) {
             $this->processObject($object);
         }
     }
     return $objects;
 }

 /**
  * Returns the table for a given model. Model can be an Eloquent model object, or a full namespaced
  * class string.
  *
  * @param string|Model $model
  *
  * @return mixed
  */
 protected function getModelTable($model)
 {
     if (!is_object($model)) {
         $model = new $model();
     }
     return DB::getTablePrefix() . $model->getTable();
 }

 /**
  * Show a list of all the languages posts formatted for Datatables.
  *
  * @return Datatables JSON
  */
 public function data($albumid = 0)
 {
     $condition = intval($albumid) == 0 ? ">" : "=";
     $videoalbum = Video::join('languages', 'languages.id', '=', 'videos.language_id')->join('video_albums', 'video_albums.id', '=', 'videos.video_album_id')->where('videos.video_album_id', $condition, $albumid)->orderBy('videos.position')->select(array('videos.id', DB::raw($albumid . ' as albumid'), DB::getTablePrefix() . 'videos.name', 'video_albums.name as category', DB::getTablePrefix() . 'videos.album_cover', 'languages.name as language', DB::getTablePrefix() . 'videos.created_at'));
     return Datatables::of($videoalbum)->edit_column('album_cover', '<a href="{{{ URL::to(\'admin/video/\' . $id . \'/\' . $albumid . \'/albumcover\' ) }}}" class="btn btn-warning btn-sm" >@if ($album_cover=="1") <span class="glyphicon glyphicon-ok"></span> @else <span class=\'glyphicon glyphicon-remove\'></span> @endif</a>')->add_column('actions', '<a href="{{{ URL::to(\'admin/video/\' . $id . \'/edit\' ) }}}" class="btn btn-success btn-sm iframe" ><span class="glyphicon glyphicon-pencil"></span>  {{ Lang::get("admin/modal.edit") }}</a>
             <a href="{{{ URL::to(\'admin/video/\' . $id . \'/delete\' ) }}}" class="btn btn-sm btn-danger iframe"><span class="glyphicon glyphicon-trash"></span> {{ Lang::get("admin/modal.delete") }}</a>
             <input type="hidden" name="row" value="{{$id}}" id="row">')->remove_column('id')->remove_column('albumid')->make();
 }

 public function getSearch()
 {
     $categoryId = Input::get('categoryid');
     $index = Input::get('index') ?: 0;
     $search = Input::get('query');
     $offset = (int) Input::get('offset');
     $limit = 10;
     $objects = null;
     if ($categoryId) {
         // Category Image
         $featuredImageUrl = '';
         if ($categoryFeaturedImageId = ObjectMeta::getValue($categoryId, '_featured_image')) {
             //                $featuredImageUrl = getImageSrc($categoryFeaturedImageId, 'thumbnail');
             $featuredImageUrl = 'cropped/' . getTheImageSize(getImageSrc($categoryFeaturedImageId), '_featured_image');
         }
         $objects = Object::whereNotIn('objects.type', ['object_type', 'image', 'category'])->where(function ($query) use($search) {
             $query->where('objects.title', 'LIKE', '%' . $search . '%')->orWhere('objects.content', 'LIKE', '%' . $search . '%')->orWhere('keywords.content', 'LIKE', '%' . $search . '%');
         })->join('objects as obj', function ($join) {
             $join->on('obj.name', '=', DB::raw("concat( '_object_type_', objects.type )"));
         })->leftJoin('keywords', function ($join) {
             $join->on('obj.id', '=', 'object_id')->orOn('objects.id', '=', 'object_id');
         })->join('object_meta', function ($join) {
             $join->on('object_meta.object_id', '=', 'obj.id')->where('object_meta.meta_key', '=', '_category_id');
         })->join('objects as cat', function ($join) {
             $join->on('cat.id', '=', 'object_meta.meta_value');
         })->where('object_meta.meta_value', $categoryId)->select(DB::raw("'{$featuredImageUrl}' AS featured_image"), 'cat.id AS catID', 'cat.title AS catTitle', 'cat.name AS catName', 'obj.id AS objID', 'obj.name AS objName', 'objects.id', 'objects.excerpt', 'objects.parent_id', 'objects.name', 'objects.type', 'objects.title', 'objects.score')->orderBy('objects.score', 'DESC')->orderBy('objects.title', 'ASC')->groupBy('objects.id')->take($limit)->skip($offset);
         //            dd($objects->toSql());
         $objects = $objects->get();
         foreach ($objects as $object) {
             $this->processObject($object);
         }
         foreach ($objects as &$object) {
             unset($object['address']);
             unset($object['address_city']);
             unset($object['address_country']);
             unset($object['address_street']);
             unset($object['catID']);
             //                unset($object['catName']);
             unset($object['catTitle']);
             unset($object['email']);
             unset($object['objID']);
             unset($object['objName']);
             unset($object['occupation']);
             unset($object['parent_id']);
             unset($object['phone']);
             unset($object['promoted']);
             unset($object['type']);
             if ($featuredImageUrl == $object['featured_image']) {
                 unset($object['featured_image']);
             }
         }
         //            dd($objects->toArray());
         return ['items' => $objects, 'offset' => count($objects) >= $limit ? $offset + $limit : 0];
     } else {
         if ($search) {
             $objects = Object::whereNotIn('objects.type', ['object_type', 'image'])->where(function ($query) use($search) {
                 $query->where('objects.title', 'LIKE', '%' . $search . '%')->orWhere('objects.content', 'LIKE', '%' . $search . '%')->orWhere('keywords.content', 'LIKE', '%' . $search . '%');
             })->leftJoin('objects as obj', function ($join) {
                 $join->on('obj.name', '=', DB::raw("concat( '_object_type_', objects.type )"));
             })->leftJoin('keywords', function ($join) {
                 $join->on('obj.id', '=', 'object_id')->orOn('objects.id', '=', 'object_id');
             })->select('objects.id', 'objects.parent_id', 'objects.name', 'objects.type', 'objects.title', 'objects.score')->orderBy(DB::raw("objects.type = 'category'"), 'DESC')->orderBy('objects.score', 'DESC')->orderBy('objects.title', 'ASC')->groupBy('objects.id')->take(50)->get();
             $objects->each(function ($v) {
                 if ($v->type != 'category') {
                     $parentObject = Object::where('type', 'object_type')->where('name', '_object_type_' . $v->type)->first();
                     if ($parentObject) {
                         $parentCategories = Object::where('type', 'category')->whereExists(function ($query) use($parentObject) {
                             $query->select(DB::raw(1))->from('object_meta')->whereRaw(DB::getTablePrefix() . 'object_meta.meta_value = ' . DB::getTablePrefix() . 'objects.id')->where('meta_key', '_category_id')->where('object_id', $parentObject->id);
                         })->first();
                         $v->category = array('id' => $parentCategories ? $parentCategories->id : '0', 'name' => $parentCategories ? $parentCategories->name : 'Home');
                     }
                 }
                 return $v;
             });
             //                dd($objects);
             return response($objects);
         }
     }
     //        dd($objects);
     //dd($objects->toSql());
     if ($objects) {
         $objects = $objects->skip($index)->take(50)->get();
         foreach ($objects as $object) {
             $this->processObject($object);
         }
     }
     return $objects;
 }

 public function scopeOfOrWithResults($query, $relation, $project)
 {
     return $query->with($relation)->whereExists(function ($query) use($project) {
         $this_table = DB::getTablePrefix() . $this->table;
         $query->selectRaw(DB::raw('resultable_id'))->from('results')->where('project_id', $project)->whereRaw('resultable_id = ' . $this_table . '.id');
     });
 }

 public function scopeOrNotWithResults($query)
 {
     return $query->orWhereNotExists(function ($query) {
         $this_table = DB::getTablePrefix() . $this->table;
         $query->selectRaw(DB::raw('resultable_id'))->from('results')->whereRaw('resultable_id = ' . $this_table . '.id');
     });
 }

 /**
  * Makes the query not repeat the results.
  *
  * @param \Illuminate\Database\Eloquent\Builder $query
  */
 protected function makeGroupBy(Builder $query)
 {
     if ($groupBy = $this->getGroupBy()) {
         $query->groupBy($groupBy);
     } else {
         $driver = $this->getDatabaseDriver();
         if ($driver == 'sqlsrv') {
             $columns = $this->getTableColumns();
         } else {
             $columns = $this->getTable() . '.' . $this->primaryKey;
         }
         $query->groupBy($columns);
         $joins = array_keys($this->getJoins());
         foreach ($this->getColumns() as $column => $relevance) {
             array_map(function ($join) use($column, $query) {
                 if (Str::contains($column, $join)) {
                     if (Str::startsWith($column, DB::getTablePrefix())) {
                         $column = substr($column, strlen(DB::getTablePrefix()));
                     }
                     $query->groupBy($column);
                 }
             }, $joins);
         }
     }
 }

 /**
  * @return mixed
  */
 protected function getTableColumns()
 {
     return DB::select('SHOW COLUMNS FROM ' . DB::getTablePrefix() . $this->name);
 }

 /**
  * Get all galleries with files_count for back end.
  *
  * @return \Illuminate\Database\Eloquent\Collection
  */
 public function allRaw()
 {
     $query = $this->make(['translations'])->select('id', 'name', 'image', DB::raw('(SELECT COUNT(*) FROM `' . DB::getTablePrefix() . 'files` WHERE `gallery_id` = `' . DB::getTablePrefix() . "galleries`.`id`) AS 'files_count'"))->order();
     // Get
     return $query->get();
 }