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,
]);
Another exist implemenation
$tenantUsers = User::whereHas('roles', function ($query) {
$query->whereIn('roles.name', ['broker', 'manager', 'administrator']);
})
->with(['roles' => function ($q) {
$q->whereIn('roles.name', ['broker', 'manager', 'administrator'])
->select('roles.id', 'roles.name');
}])->where(function ($query) use ($search) {
$query->where('email', 'LIKE', '%' . $search . '%')
->orWhere(DB::raw("concat(first_name, ' ', last_name)"), 'LIKE', "%" . $search . "%")
->orWhere('first_name', 'LIKE', '%' . $search . '%')
->orWhere('last_name', 'LIKE', '%' . $search . '%');
})->get(['id', 'first_name', 'last_name', 'email']);
select
id,
first_name,
last_name,
email
from
users
where
exists (
select
*
from
roles
inner join model_has_roles on roles.id = model_has_roles.role_id
where
users.id = model_has_roles.model_uuid
and model_has_roles.model_type = 'App\Models\Universal\User'
and roles.name in ('broker', 'manager', 'administrator')
and model_has_roles.team_id is null
and (roles.team_id is null or roles.team_id is null)
)
and (
email LIKE '%dev%'
or concat(first_name, ' ', last_name) LIKE '%dev% '
or first_name LIKE '%dev%'
or last_name LIKE '%dev%'
)
and users.deleted_at is null
Another exist implemenation
return Contact::where('is_third_party', true)
->when($search, function ($query) use ($search) {
$query->where(function ($q) use ($search) {
$q->orWhere('mobile_number', 'LIKE', '%' . $search . '%')
->orWhere('email_address', 'LIKE', '%' . $search . '%');
});
})->get();
select
*
from
`contacts`
where
`is_third_party` = 1
and (
`mobile_number` LIKE '%3%' or `email_address` LIKE '%3%'
)
and `contacts`.`deleted_at` is null
Another exist implemenation
$contacts = Contact::query()
->join('applications', 'applications.id', '=', 'contacts.application_id')
->join('users as brokers', 'brokers.id', '=', 'applications.broker_uuid')
->when($searchString, function ($query) use ($searchString) {
//these where creates a parenthesis for the or
$query->where(function ($query) use ($searchString) {
$query->where('contacts.first_name', 'like', '%' . $searchString . '%')
->orWhere('contacts.last_name', 'like', '%' . $searchString . '%')
->orWhere('contacts.email_address', 'like', '%' . $searchString . '%');
});
})
->when($brokerId, function ($query) use ($brokerId) {
$query->where('applications.broker_uuid', $brokerId);
})
->select(['contacts.*', 'brokers.first_name as broker_first_name', 'brokers.last_name as broker_last_name']);
SELECT `contacts`.*,
`brokers`.`first_name` AS `broker_first_name`,
`brokers`.`last_name` AS `broker_last_name`
FROM `contacts`
INNER JOIN `applications`
ON `applications`.`id` = `contacts`.`application_id`
INNER JOIN `users` AS `brokers`
ON `brokers`.`id` = `applications`.`broker_uuid`
WHERE ( `contacts`.`first_name` LIKE '%popo%'
OR `contacts`.`last_name` LIKE '%popo%'
OR `contacts`.`email_address` LIKE '%popo%' )
AND `applications`.`broker_uuid` = '2bb75f31-8a87-443d-421a-3f21f4992322'
Another exist implemenation
here_here
Another exist implemenation
here_here
Another exist implemenation
here_here
Another exist implemenation
here_here
http://laravel.at.jeffsbox.eu/laravel-5-query-builder-where-exists
ReplyDelete