select n0y_t_item_description.s_title,
n0y_t_item_description.s_slug as item_slug,
n0y_t_item.d_price as price,
n0y_t_category_description.s_name,
n0y_t_category_description.s_slug as category_slug,
n0y_t_item_resource.pk_i_id as path_id,
n0y_t_item_resource.s_path as image_path,
n0y_t_item_resource.s_extension as path_ext,
parent_slug from `n0y_t_item` inner join `n0y_t_item_description` on `n0y_t_item_description`.`fk_i_item_id` = `n0y_t_item`.`pk_i_id` inner join `n0y_t_category` on `n0y_t_category`.`pk_i_id` = `n0y_t_item`.`fk_i_category_id` inner join `n0y_t_category_description` on `n0y_t_category`.`pk_i_id` = `n0y_t_category_description`.`fk_i_category_id` inner join `n0y_t_item_resource` on `n0y_t_item_resource`.`fk_i_item_id` = `n0y_t_item`.`pk_i_id` inner join (select pk_i_id, s_slug as parent_slug
from n0y_t_category join n0y_t_category_description on n0y_t_category.pk_i_id = n0y_t_category_description.fk_i_category_id
) as n0y_pCat on `n0y_pCat`.`pk_i_id` = `n0y_t_category`.`fk_i_parent_id` where (`b_active` = '1' and `i_main` = '1') order by RAND() limit 7
$sql = ' xxt_item_description.s_title,
xxt_item_description.s_slug as item_slug,
xxt_item.d_price as price,
xxt_category_description.s_name,
xxt_category_description.s_slug as category_slug,
xxt_item_resource.pk_i_id as path_id,
xxt_item_resource.s_path as image_path,
xxt_item_resource.s_extension as path_ext,
parent_slug';
$sql = str_replace('xx', DB::getTablePrefix(), $sql);
$joinQry = "(select pk_i_id, s_slug as parent_slug
from xxt_category join xxt_category_description on xxt_category.pk_i_id = xxt_category_description.fk_i_category_id
) as xxpCat";
$joinQry = str_replace('xx', DB::getTablePrefix(), $joinQry);
$items = DB::table('t_item')
->select(DB::raw(
$sql
))
->join('t_item_description', 't_item_description.fk_i_item_id', 't_item.pk_i_id')
->join('t_category', 't_category.pk_i_id', 't_item.fk_i_category_id')
->join('t_category_description', 't_category.pk_i_id', 't_category_description.fk_i_category_id')
->join('t_item_resource', 't_item_resource.fk_i_item_id', 't_item.pk_i_id')
->where(function ($q) use ($tenDaysAgo, $currentDate) {
$q->where('b_active', 1)
->where('i_main',1);
// ->whereDate('dt_expiration', '>', date($currentDate->toDateTimeString()))
// ->whereBetween('dt_pub_date', array(date($tenDaysAgo->toDateTimeString()), date($currentDate->toDateTimeString())));
})
->join(DB::raw($joinQry), function ($join) {
$join->on("pCat.pk_i_id", "=", "t_category.fk_i_parent_id");
})
->orderByRaw('RAND()')
// ->order('dt_pub_date','desc')
->take(7)
->get();
ORIGINAL SQL WHERE EXISTS
SELECT *
FROM `items`
WHERE EXISTS
(SELECT `items_city`.`id`
FROM `items_city`
WHERE items_city.item_id = items.id)
Laravel Eloquent Query with Update
DB::table('contributions')
->whereStatusId(ContributionStatus::RECONCILED)
->whereNull('submission_id')
->whereExists(function ($query) {
$query->select("declarations.id")
->from('declarations')
->whereRaw('declarations.id = contributions.declaration_id');
})
->update([
'submission_id' => $submission->id,
]);