Deekshith
8 months ago

Query regarding fetching data,

Posted 8 months ago by Deekshith

Hi, I have three tables namely, users,user_membership,user_payment_details, Users have more than 1 user_membership and user_payment_details data. so that relation would be hasmany.

right now i am fetching all users data using below query,

$users = $users->select('users.*','chapters.chapter_name as chapter_name','membership_payment_structure.name as fee_name','membership.name as membership_name')->leftJoin('chapters','users.chapter_id','chapters.id') ->leftJoin('membership', 'users.membership_id', 'membership.id') ->leftJoin('membership_payment_structure', 'users.mebership_fee_id','membership_payment_structure.id') ->with(['lastmembership'=>function($query){ $query->select('started_at', 'ended_at','users_id','id'); }])->with(['lastpayment'=>function($query){ $query->where('status','!=','Expired')->select('amount', 'status','user_id','currency','check_no','transaction_id'); }])->orderBy('users.created_at','DESC')->paginate(20);

public function lastmembership(){ return $this->hasOne('App\UserMembership','users_id','id')->orderBy('id', 'desc'); }

public function lastpayment(){
  return $this->hasOne('App\UserPayment')->orderBy('id', 'desc');
}

Above query displays all users. But now i am trying to implement filters for above query and now i want to apply the condition for user_payment_details table. user_payment_details has status columns where I want to apply where condition.

So i want to display users which match user_payment_details status column. But if i put where condition in "lastpayment" it is displaying all users. Any suggestions?

Please sign in or create an account to participate in this conversation.