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; }
echo '<td>' . $item['type'] . '</td>'; echo '<td>' . $item['title'] . '</td>'; echo '<td style="text-align: center"><a href="' . url("admin/objects/{$item['id']}/edit") . '">' . $item['id'] . '</a></td>'; echo '</tr>'; } echo '</tbody>'; echo '<table>'; echo '<style>td{padding: 2px 5px;}</style><pre>'; break; default: die('Please pass a type (address / coordinates)'); } }); Route::get('/fixAddresses', function () { ini_set('max_execution_time', 300); $list = \App\Object::whereNotIn('objects.type', ['image', 'category'])->join('object_meta as om', function ($join) { $join->on('om.object_id', '=', 'objects.id'); })->where(function ($where) { $where->where('om.meta_key', '=', '_field_address-address')->orWhere('om.meta_key', '=', '_field_address-city')->orWhere('om.meta_key', '=', '_field_address-country')->orWhere('om.meta_key', '=', '_field_address-location-g')->orWhere('om.meta_key', '=', '_field_address-location-k'); })->select('om.id', 'om.object_id', 'om.meta_key', 'om.meta_value')->orderBy('om.object_id', 'ASC'); $limit = isset($_GET['limit']) ? intval($_GET['limit']) : false; $offset = isset($_GET['offset']) ? intval($_GET['offset']) : false; $print = isset($_GET['print']) ? true : false; if (!$limit) { die('You did not send a limit'); } $items = array(); foreach ($list->get() as $item) { $items[$item->object_id][$item->meta_key] = array('id' => $item->id, 'value' => $item->meta_value); } $items = array_slice($items, $offset, $limit, true);
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; }