public function cleanup() { $prefix = config('larablog.table.prefix'); // Clean out any old pivot data. DB::statement("DELETE {$prefix}_post_tag FROM {$prefix}_post_tag LEFT JOIN {$prefix}_posts ON {$prefix}_post_tag.post_id = {$prefix}_posts.id WHERE NOT({$prefix}_post_tag.post_id = {$prefix}_posts.id AND {$prefix}_posts.status = 'active' AND {$prefix}_posts.type='post')"); // TODO: convert to eloquent? DB::table($prefix . '_tags')->update(['posts_count' => DB::Raw("(SELECT COUNT(*) FROM {$prefix}_post_tag WHERE {$prefix}_post_tag.tag_id = {$prefix}_tags.id)")]); Tag::where('posts_count', 0)->delete(); }
/** * Counts current query * @param string $count variable to store to 'count_all' for iTotalRecords, 'display_all' for iTotalDisplayRecords * @return null */ private function count($count = 'count_all') { //Get columns to temp var. if ($this->query_type == 'eloquent') { $query = $this->query->getQuery(); $connection = $this->query->getModel()->getConnection()->getName(); } else { $query = $this->query; $connection = $query->getConnection()->getName(); } // if its a normal query ( no union ) replace the slect with static text to improve performance $myQuery = clone $query; if (!preg_match('/UNION/i', $myQuery->toSql())) { $myQuery->select(DB::Raw("'1' as row")); } $this->{$count} = DB::connection($connection)->table(DB::raw('(' . $myQuery->toSql() . ') AS count_row_table'))->setBindings($myQuery->getBindings())->remember(1)->count(); }
public function cleanup() { $prefix = config('larablog.table.prefix'); DB::table($prefix . '_series')->update(['posts_count' => DB::Raw("(SELECT COUNT(*) FROM {$prefix}_posts WHERE {$prefix}_posts.serie_id = {$prefix}_series.id)")]); Serie::where('posts_count', 0)->delete(); }
private function productQuery($language_id) { /* $sql="SELECT DISTINCT *, pd.name AS name, p.image, m.name AS manufacturer, (SELECT price FROM products_discount pd2 WHERE pd2.product_id = p.id AND pd2.customer_group_id = '1' AND pd2.quantity = '1' AND ( ( pd2.date_start = '0000-00-00' OR pd2.date_start < Now() ) AND ( pd2.date_end = '0000-00-00' OR pd2.date_end > Now() ) ) ORDER BY pd2.priority ASC, pd2.price ASC LIMIT 1) AS discount, (SELECT price FROM products_special ps WHERE ps.product_id = p.id AND ps.customer_group_id = '1' AND ( ( ps.date_start = '0000-00-00' OR ps.date_start < Now() ) AND ( ps.date_end = '0000-00-00' OR ps.date_end > Now() ) ) ORDER BY ps.priority ASC, ps.price ASC LIMIT 1) AS special, (SELECT points FROM products_reward pr WHERE pr.product_id = p.id AND customer_group_id = '1') AS reward, (SELECT ss.name FROM oc_stock_status ss WHERE ss.stock_status_id = p.stock_status_id AND ss.language_id = '1') AS stock_status, (SELECT wcd.unit FROM oc_weight_class_description wcd WHERE p.weight_class_id = wcd.weight_class_id AND wcd.language_id = '1') AS weight_class, (SELECT lcd.unit FROM oc_length_class_description lcd WHERE p.length_class_id = lcd.length_class_id AND lcd.language_id = '1') AS length_class, (SELECT Avg(rating) AS total FROM oc_review r1 WHERE r1.product_id = p.id AND r1.status = '1' GROUP BY r1.product_id) AS rating, (SELECT Count(*) AS total FROM oc_review r2 WHERE r2.product_id = p.id AND r2.status = '1' GROUP BY r2.product_id) AS reviews FROM products p LEFT JOIN products_description pd ON ( p.id = pd.product_id ) LEFT JOIN products_to_store p2s ON ( p.id = p2s.product_id ) LEFT JOIN oc_manufacturer m ON ( p.manufacturer_id = m.manufacturer_id) WHERE p.id = ? AND pd.language_id = ? AND p.status = '1' AND p.date_available <= Now() AND p2s.store_id = '0'"; $items=DB::select($sql,[$product_id,$this->language_id]); return $item; */ $query = DB::table('products as p')->select('p.id', 'p.model', 'p.price', 'p.tax_class_id', 'tc.name AS tax_class_name', 'p.weight_class_id', 'wcd.name AS weight_class_name', 'wcd.unit AS weight_class_unit', 'p.length_class_id', 'lcd.name AS length_class_name', 'lcd.unit AS length_class_unit', 'p.price', 'p.image', 'pd.name AS name', 'pd.description AS description', 'm.id AS manufacturer_id', 'm.name AS manufacturer_name', 'ss.name AS stock_status', DB::Raw('(SELECT points FROM products_rewards pr WHERE pr.product_id = p.id AND customer_group_id = 1) AS reward_points'), DB::Raw('(SELECT Avg(rating) FROM products_reviews pr WHERE pr.product_id = p.id AND pr.approved = 1 GROUP BY pr.product_id) AS rating'), DB::Raw('(SELECT Count(*) FROM products_reviews pr WHERE pr.product_id = p.id AND pr.approved = 1) AS reviews_count'), DB::Raw('(SELECT Count(*) FROM products_to_attributes pa WHERE pa.product_id = p.id) AS attributes_count'))->leftJoin('products_descriptions as pd', 'p.id', '=', 'pd.product_id')->leftJoin('manufacturers AS m', 'm.id', '=', 'p.manufacturer_id')->leftJoin('tax_classes AS tc', 'tc.id', '=', 'p.tax_class_id')->leftJoin('weight_classes_descriptions AS wcd', function ($join) use($language_id) { $join->on('wcd.weight_class_id', '=', 'p.weight_class_id'); $join->where('wcd.language_id', '=', Context::getLanguageId()); })->leftJoin('length_classes_descriptions AS lcd', function ($join) use($language_id) { $join->on('lcd.length_class_id', '=', 'p.length_class_id'); $join->where('lcd.language_id', '=', Context::getLanguageId()); })->leftJoin('stock_statuses AS ss', function ($join) use($language_id) { $join->on('ss.stock_status_id', '=', 'p.stock_status_id'); $join->where('ss.language_id', '=', Context::getLanguageId()); })->where('pd.language_id', Context::getLanguageId()); return $query; }