pass same query to multiple variables

i have a query like below

$confirmedusers = Orders::with(['userdetail.useridcards'])->join('user_registered_courses','user_registered_courses.user_id','=','orders.user_id')->join('users','users.user_id','=','user_registered_courses.user_id')->where('user_registered_courses.course_id',$course_id)->whereNotNull('user_registered_courses.registration_number')->where('orders.course_id',$course_id);

if($package_display != "all") {
$confirmedusers = $confirmedusers->where('user_registered_courses.package_id',$package_display);
 }
 $confirmedusers = $confirmedusers->where('users.id_card_confirm','yes')->orderBy('user_registered_courses.registration_number','ASC')->select('user_registered_courses.id','user_registered_courses.user_id','user_registered_courses.package_id','user_registered_courses.language','user_registered_courses.center','orders.order_id','orders.course_id','orders.package_language')->groupBy('orders.user_id')->paginate(5, ['*'], 'authenticateuserspage');

$not_confirmed_users = Orders::with(['userdetail.useridcards'])->join('user_registered_courses','user_registered_courses.user_id','=','orders.user_id')->join('users','users.user_id','=','user_registered_courses.user_id')->where('user_registered_courses.course_id',$course_id)->whereNotNull('user_registered_courses.registration_number')->where('orders.course_id',$course_id);

 if($package_display != "all") {
$not_confirmed_users = $not_confirmed_users->where('user_registered_courses.package_id',$package_display);
     }

  $not_confirmed_users = $not_confirmed_users->where('users.id_card_confirm','no')->orderBy('user_registered_courses.registration_number','ASC')->select('user_registered_courses.id','user_registered_courses.user_id','user_registered_courses.package_id','user_registered_courses.language','user_registered_courses.center','orders.order_id','orders.course_id','orders.package_language')->groupBy('orders.user_id')->paginate(50, ['*'], 'notconfirmedpage');

In above query only one where condition is changing. for $confirmedusers

$confirmedusers->where('users.id_card_confirm','yes')

for $not_confirmed_users,

$not_confirmed_users = $not_confirmed_users->where('users.id_card_confirm','no')

only this part is changing apart from this everything is same but i need in changed data in different variables how to avoid writing same query again from first for $not_confirmed_users ? Thank you,

Snapey

4 years ago

Level 122

clone

1 like

kevinbui

4 years ago

Best Answer

Level 41

You can always use query scopes to refactor your db queries. Reading your code, I can refactor to the following scopes:

public class Order extends Model
{
    public function scopeRegisteredInCourse($query, $courseId)
    {
        return $query->with(['userdetail.useridcards'])
            - >join('user_registered_courses','user_registered_courses.user_id','=','orders.user_id')
            ->join('users','users.user_id','=','user_registered_courses.user_id')
            ->where('user_registered_courses.course_id',$courseId)
            ->whereNotNull('user_registered_courses.registration_number')->where('orders.course_id',$courseId)
            ->orderBy('user_registered_courses.registration_number','ASC')
            ->select('user_registered_courses.id','user_registered_courses.user_id','user_registered_courses.package_id','user_registered_courses.language','user_registered_courses.center','orders.order_id','orders.course_id','orders.package_language')
            ->groupBy('orders.user_id');
    }

    public function scopePackage($query, $packageId)
    {
        return $query->where('user_registered_courses.package_id',$packageId);
    }

    public function scopeConfirmed($query)
    {
        return $query->where('users.id_card_confirm', 'yes');
    }

    public function scopeNotConfirmed($query)
    {
        return $query->where('users.id_card_confirm', 'no');
    }
}

Then, to retrieve the results:

$confirmedOrders = Order::registeredInCourse($course_id)
    when($package_display != 'all', function ($query) use ($package_display) {
        return $query->package($package_displayed);
    }) 
    ->confirmed();

$NotConfirmedOrders = Order::registeredInCourse($course_id)
    when($package_display != 'all', function ($query) use ($package_display) {
        return $query->package($package_displayed);
    }) 
    ->notConfirmed();

Deekshith

4 years ago

Level 5

@kevinbui Thank you,

Please or to participate in this conversation.