Deekshith
947
11
General

Laravel hasmany relationship query

Posted 7 months ago by Deekshith

I have two tables User table and user membership table user membership table consists every year data for a user.

Example: User table

id name 1 Deekshith

User Membership Table:

id user_id started_at ended_at 1 1 2018-01-01 2018-12-31 2 1 2019-01-01 2019-12-31

So it has one to many relation

My question is how to fetch record from user table using laravel Eloquent: Relationships and query should return record from user membership table which has max of id.

please check my query below,

$users = User::where('role_id',2)->with(['membership' => function($query){

$query->where(DB::raw('users_membership.id  IN ( SELECT MAX(id) FROM users_membership GROUP BY users_id )'))
->select('started_at','ended_at'); //you may use any condition here or manual select operation

}])->with(['payment' => function($query){

$query->where(DB::raw('id IN ( SELECT MAX(id) FROM user_payment_details GROUP BY user_id )')) ->select('amount','status'); //you may use any condition here or manual select operation

}])->select('users.email')->get();

But above query returns cardinally violation on mysql query.

i can get expected result in raw mysql query but I want this to be done with the relationship.

so how can I combine below query with user model in a relationship,

SELECT * FROM users_membership WHERE id IN ( SELECT MAX(id) FROM users_membership GROUP BY users_id )

right now i am matching returned id with users table id in html table which is taking too much time to load.

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