Laravel 5.2 orWhere generate “and” in query

2020-03-31 07:36发布

问题:

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?