/** * Display a listing of the resource. * * @return \Illuminate\Http\Response */ public function index(Request $request) { $querystringArray = $request->input(); // set default values $orderBy = isset($request->orderby) ? $request->orderby : 'title'; $order = isset($request->order) ? $request->order : 'ASC'; // with filtering? if (isset($request->filterby) && $request->filterby != 'songs' && isset($request->filtervalue)) { // we need to set the Pagination currentPage to 0, // otherwise we would not see the search results if (isset($request->page)) { $currentPage = 0; Paginator::currentPageResolver(function () use($currentPage) { return $currentPage; }); $querystringArray = []; } if ($request->filterby == 'fulltext') { $songs = Song::withCount('items')->orderBy($orderBy, $order)->where('title', 'like', '%' . $request->filtervalue . '%')->orWhere('title_2', 'like', '%' . $request->filtervalue . '%')->orWhere('author', 'like', '%' . $request->filtervalue . '%')->orWhere('book_ref', 'like', '%' . $request->filtervalue . '%')->orWhere('lyrics', 'like', '%' . $request->filtervalue . '%'); } elseif ($request->filterby == 'title') { $songs = Song::withCount('items')->orderBy($orderBy, $order)->where('title', 'like', '%' . $request->filtervalue . '%')->orWhere('title_2', 'like', '%' . $request->filtervalue . '%'); } elseif ($request->filtervalue == '_') { $songs = Song::withCount('items')->orderBy($orderBy, $order)->where($request->filterby, null)->orWhere($request->filterby, ''); } else { $songs = Song::withCount('items')->orderBy($orderBy, $order)->where($request->filterby, 'like', '%' . $request->filtervalue . '%'); } } else { // (the where clause is needed since the 'withCount' would bring in all items with song_id = 0) $songs = Song::withCount('items')->where('id', '>', 0)->orderBy($orderBy, $order); } // if orderBy is 'book_ref', then exclude all songs without a book_ref! if ($request->orderby == 'book_ref') { $songs = $songs->where('book_ref', '<>', ''); } if ($request->orderby == 'author') { $songs = $songs->where('author', '<>', ''); } // special case for OrderBy Last Time Used (in a plan!) if ($request->orderby == 'last_used') { $songs = Song::withCount('items')->where('songs.id', '>', 0)->leftJoin('items', 'items.song_id', '=', 'songs.id')->leftJoin('plans', 'plans.id', '=', 'items.plan_id')->groupBy('songs.id')->selectRaw('songs.*, max(plans.date) as last_used')->orderBy('last_used', $order); } $heading = 'Manage Songs etc.'; if (isset($request->filterby) && $request->filtervalue == 'video') { $heading = 'Manage Videoclips'; } if (isset($request->filterby) && $request->filtervalue == 'slides') { $heading = 'Manage Slides'; } if (isset($request->filterby) && $request->filterby == 'songs' && isset($request->filtervalue) && $request->filtervalue == 'rare') { //:: $songs = Song::withCount('items')->where('songs.id', '>', 0)->leftJoin('items', 'items.song_id', '=', 'songs.id')->leftJoin('plans', 'plans.id', '=', 'items.plan_id')->groupBy('songs.id')->selectRaw('songs.*, max(plans.date) as last_used')->havingRaw('count(items.id) > 1')->orderBy('last_used', 'ASC'); } // URL contains ...?plan_id=xxx (needed in order to add a song to that plan) $plan_id = 0; if ($request->has('plan_id')) { $plan_id = $request->plan_id; $heading = 'Select A Song For Your Plan'; } // for pagination, always append the original query string $songs = $songs->paginate(20)->appends($querystringArray); return view($this->view_all, array('songs' => $songs, 'heading' => $heading, 'plan_id' => $plan_id, 'currentPage' => $songs->currentPage())); }