public function getSearchPage($fromLocation = false) { $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')->groupBy('objects.id')->orderBy('objects.score', 'DESC')->orderBy('objects.title', 'ASC'); if (!$fromLocation) { $objects->take($limit)->skip($offset); } $objects = $objects->get(); foreach ($objects as $object) { $this->processObject($object); } if (!$fromLocation) { 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']); } $object['excerpt'] = mb_substr($object['excerpt'], 0, 200); } } return ['items' => $objects, 'offset' => count($objects) >= $limit ? $offset + $limit : 0]; /* dd($objects->toArray()); // Category Image $featuredImageUrl = ''; 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( '"'. $featuredImageUrl . '"' . ' as featured_image'), 'objects.name', 'objects.title', 'objects.excerpt', 'objects.content' ) ); // dd($objects->get()); $objects = DB::table('objects') ->whereIn('type', $types) ->where(function( $query ) use ( $search ) { $query->where('title', 'LIKE', '%' . $search . '%') ->orWhere('name', 'LIKE', '%' . $search . '%'); }) ->orderBy('score','DESC') ->select( array( 'objects.id', DB::raw( '"'. $featuredImageUrl . '"' . ' as featured_image'), 'objects.name', 'objects.title', 'objects.excerpt', 'objects.content' ) ) ->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); dd($objects->toSql()); */ } else { if ($search) { $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'); })->select('obj.id AS objID', 'obj.name AS objName', 'objects.id', 'objects.excerpt', 'objects.parent_id', 'objects.name', 'objects.type', 'objects.title')->orderBy('objects.score', 'DESC')->orderBy('objects.title', 'ASC')->groupBy('objects.id')->get(); $parentObjects = array_unique($objects->map(function ($v) { return $v->objID; })->toArray()); if ($parentObjects) { $_categories = ObjectMeta::whereIn('object_meta.object_id', $parentObjects)->where('object_meta.meta_key', '_category_id')->join('objects as cat', function ($join) { $join->on('cat.id', '=', 'object_meta.meta_value'); })->orderBy('cat.score', 'DESC')->orderBy('cat.title', 'ASC')->select('cat.id', 'cat.title', 'cat.name', 'object_meta.object_id')->get(); if ($_categories) { $categories = []; foreach ($_categories->toArray() as $item) { if (isset($categories[$item['id']])) { $categories[$item['id']]['parents'][] = $item['object_id']; } else { $categories[$item['id']] = array('id' => $item['id'], 'title' => $item['title'], 'name' => $item['name'], 'parents' => array($item['object_id'])); } } $objects = $objects->toArray(); $counter = 0; foreach ($categories as $k => $item) { foreach ($objects as $v) { if (in_array($v['objID'], $item['parents'])) { $v['excerpt'] = mb_substr($v['excerpt'], 0, 200); $categories[$k]['items'][] = $v; } } $categories[$k]['itemsCount'] = count($categories[$k]['items']); $counter += $categories[$k]['itemsCount']; $categories[$k]['items'] = array_slice($categories[$k]['items'], 0, 3); } if ($limit) { $categories = array_slice($categories, $offset, $limit); } return ['data' => $categories, 'count' => $counter, 'offset' => count($categories) >= $limit ? $offset + $limit : 0]; } } return []; } } // dd($objects); //dd($objects->toSql()); if ($objects) { $objects = $objects->get(); dd($objects); foreach ($objects as $object) { $this->processObject($object); } } return $objects; }