/** * 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(); }