how to get the data using whereHas clauses and relationship?

Question

user table

id
email
password

membership table

id
user_id
expiry_date
delivery_day_innum(eg:like 7,8 days)

deliverdetailes table

id
user_id
created_at
updated_at

goods table

id
user_id
name
qty

goods table

id
user_id
name
qty

Relationship

user->hasOne(Membership::class)
user->hasMany(Deliverdetailes::class)
user->hasMany(Goods::class)

now, i wan to get the goods whose account is not expired and deliver_day_innum is equals to the difference between the present day with the latest created_at date

what u actually want ?? The respective goods of the user with the conditions? — void, May 12 '20 at 12:30

score 0 · Answer 1 · answered May 12 '20 at 19:18

0

$myGoods= Goods::join('user','user.id','goods.user_id')
->join('membership','membership.user_id','=','user.id')
->join('deliverdetailes','deliverdetailes.user_id','=','user.id')
->whereDate('expiry_date','>',Carbon::now())
->whereRaw('DATEDIFF(maxDate, CURDATE())=membership.delivery_day_innum')
->select('goods.*',DB::raw('max(deliverdetailes.created_at) as maxDate'))->get();

answered May 12 '20 at 19:18

OMR

11,736
5
20
35

would you mind writing using ORM?? i appreciate your work – Razeev Kumar Yadav May 13 '20 at 13:12
it's generally ORM, except for using "raw" twice, it's necessary to use (DATEDIFF ,max) function from mysql, and for 'join' it's necessary and i don't know other way to do it without join. – OMR May 13 '20 at 15:47
you can use advanced query but the complication would be the same: https://laravel.com/docs/7.x/eloquent#advanced-subqueries – OMR May 13 '20 at 15:52

score 0 · Answer 2 · answered Dec 07 '20 at 13:24

First problem: Goods for accounts that have not expired:

Adjust Carbon::now() to match the date format for expiry_date.

   $goods = Good::whereHas('user.membership', function($query){
       return $query->where('expiry_date', '<=', Carbon::now());
   })->get();

Required in App\Good: relationship with App\User.

public function user()
{
    return $this->belongsTo('App\User');
}

Second problem: please clarify: "deliver_day_innum is equals to the difference between the present day with the latest created_at date".

how to get the data using whereHas clauses and relationship?

2 Answers2