I have written a query in Laravel 5.2
as below, where I used scope. The scope scopeSubscriberCriteriaSearch
have further sub scope. The scenario is, if the main scope finds "first_name" "=" "test" OR "last_name" "!=" "demo"
etc, the related sub scope will be called.
$record = self::leftJoin("$customersTable as customers", 'customers.email', '=', "{$this->table}.email")
->leftJoin("$campaignsTable as campaigns", 'campaigns.campaign_id', '=', "{$this->table}.campaign_id")
->select("$this->primaryKey", "{$this->table}.first_name", "{$this->table}.last_name", "{$this->table}.email", "{$this->table}.phone", "{$this->table}.address", "campaigns.campaign_id", "campaigns.campaign_name", "customers.order_count")
->whereRaw($where);
$record->subscriberCriteriaSearch($criteria, $search_type, $persist)
->searchAfter($waiting_min, $search_type, $this->user_id)
->excludeLeadEmail($last_used_email)
->greaterCreateDateSearch($campaign_start_date)
->get();
=========== The main scope is as below:
public function scopeSubscriberCriteriaSearch($query, $criteria, $search_type, $persist = TRUE)
{
if (!empty($criteria))
{
$criteria_var = ['first_name', 'last_name', 'email', 'phone_number', 'city', 'country', 'state', 'zip', 'address', 'campaign', 'affiliate', 'subAffiliate', 'createdAt'];
return $query->where(function($sub_query) use ($criteria, $criteria_var, $search_type, $persist)
{
foreach ($criteria as $rule)
{
if (in_array($rule->condition, $criteria_var))
{
$position = array_search($rule->condition, $criteria_var);
($rule->condition == 'affiliate' || $rule->condition == 'subAffiliate') ? $sub_query->{$criteria_var[$position] . 'Search'}($rule->logical, $rule->input_val, $search_type, $persist) : $sub_query->{$criteria_var[$position] . 'Search'}($rule->logical, $rule->input_val, $persist);
}
}
});
}
}
The sub scopes (for example:) as below:
public function scopeFirst_nameSearch($query, $operator, $first_name, $persist = TRUE)
{
$query_operator = $this->getQueryOperator($operator);
if (!empty($operator) && !empty($first_name))
{
$query_operator_val = preg_match('/like/i', $query_operator) ? ($operator == 'starts' ? '"' . $first_name . '%"' : ($operator == 'ends' ? '"%' . $first_name . '"' : '"%' . $first_name . '%"')) : '"' . $first_name . '"';
return $persist ? $query->where($this->table . ".first_name", $query_operator, DB::raw($query_operator_val)) : $query->orWhere($this->table . ".first_name", $query_operator, DB::raw($query_operator_val));
}
}
public function scopeLast_nameSearch($query, $operator, $last_name, $persist = TRUE)
{
$query_operator = $this->getQueryOperator($operator);
if (!empty($operator) && !empty($last_name))
{
$query_operator_val = preg_match('/like/i', $query_operator) ? ($operator == 'starts' ? '"' . $last_name . '%"' : ($operator == 'ends' ? '"%' . $last_name . '"' : '"%' . $last_name . '%"')) : '"' . $last_name . '"';
return $persist ? $query->where($this->table . ".last_name", $query_operator, DB::raw($query_operator_val)) : $query->orWhere($this->table . ".last_name", $query_operator, DB::raw($query_operator_val));
}
}
So, if $persist is False, then the orWhere part of these sub queries will execute. But in my case, the query now generated as below:
select prospect_id
, sem_1_prospects
.first_name
, sem_1_prospects
.last_name
, sem_1_prospects
.email
, sem_1_prospects
.phone
, sem_1_prospects
.address
, sem_campaigns
.campaign_id
, sem_campaigns
.campaign_name
, sem_customers
.order_count
from sem_1_prospects
left join sem_1_customers
as sem_customers
on sem_customers
.email
= sem_1_prospects
.email
left join sem_1_campaigns
as sem_campaigns
on sem_campaigns
.campaign_id
= sem_1_prospects
.campaign_id
where ((sem_1_prospects
.first_name
= "test") and (sem_1_prospects
.last_name
NOT LIKE "%demo%"));
Can someone help me to find out the issue?