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;
}
