Level 102
Please remove the millon spaces form these two lines :) You broke the layout
Oct 27, 2020
6
Level 5
sort data on relationship with hasmany
i have two tables one is learner_map and another is users
learner_map contains below colums,
id, user_id, center_id, client_id
users table contants
id, name,email,mobile,gender,username,
Now to display all mapped users to a particular center i used below query,
$userlist = LearnerCenterMap::with('learnerdetails')->where('center_id',$center_id)->where('client_id',$client_id)->get();
LearnerCenterMap.php
public function learnerdetails()
{
return $this->hasOne('App\User','id','learner_id');
}
Above query displays data like below,
[
{
"id": 1,
"learner_id": 2,
"center_id": 2,
"client_id": 2,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-10-27T07:35:19.000000Z",
"learnerdetails": {
"id": 2,
"name": "Deekshith Prakash",
"username": "UN0009206",
"email": "[email protected]",
"token": "token",
"email_verified_at": null,
"raw_password": "deek@123",
"mobile": "",
"city": null,
"state": null,
"address": null,
"role": null,
"profile_picture": "profile6.png",
"gender": null,
"designation": null,
"user_role_id": 1,
"professional_bio": "I will wear white belt",
"active_status": 1,
"created_at": "2020-07-06T14:12:28.000000Z",
"updated_at": "2020-09-30T08:59:21.000000Z"
}
},
{
"id": 2,
"learner_id": 55,
"center_id": 2,
"client_id": 2,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-10-27T07:35:19.000000Z",
"learnerdetails": {
"id": 55,
"name": "Deekshith 2",
"username": "UN0001605",
"email": "",
"token": "token",
"email_verified_at": null,
"raw_password": "QLvgywiw",
"mobile": "",
"city": null,
"state": null,
"address": null,
"role": null,
"profile_picture": null,
"gender": "Male",
"designation": "",
"user_role_id": 1,
"professional_bio": null,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-07-28T10:34:46.000000Z"
}
},
{
"id": 3,
"learner_id": 56,
"center_id": 2,
"client_id": 2,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-10-27T07:35:19.000000Z",
"learnerdetails": {
"id": 56,
"name": "Deekshith 3",
"username": "UN0005017",
"email": "",
"token": "token",
"email_verified_at": null,
"raw_password": "ixGMJ06q",
"mobile": "",
"city": null,
"state": null,
"address": null,
"role": null,
"profile_picture": null,
"gender": "",
"designation": "",
"user_role_id": 1,
"professional_bio": null,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-07-28T10:34:46.000000Z"
}
},
{
"id": 4,
"learner_id": 57,
"center_id": 2,
"client_id": 2,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-10-27T07:35:19.000000Z",
"learnerdetails": null
},
{
"id": 5,
"learner_id": 58,
"center_id": 2,
"client_id": 2,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-10-27T07:35:19.000000Z",
"learnerdetails": {
"id": 58,
"name": "Deekshith 5",
"username": "UN0004658",
"email": "",
"token": "token",
"email_verified_at": null,
"raw_password": "l9QuVrnH",
"mobile": "mobile number",
"city": null,
"state": null,
"address": null,
"role": null,
"profile_picture": null,
"gender": "",
"designation": "",
"user_role_id": 1,
"professional_bio": null,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-07-28T10:34:46.000000Z"
}
},
{
"id": 6,
"learner_id": 59,
"center_id": 2,
"client_id": 2,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-10-27T07:35:19.000000Z",
"learnerdetails": {
"id": 59,
"name": "Deekshith 6",
"username": "UN00023",
"email": "email",
"token": "token",
"email_verified_at": null,
"raw_password": "G5SkxtZ8",
"mobile": "",
"city": null,
"state": null,
"address": null,
"role": null,
"profile_picture": null,
"gender": "Male ",
"designation": "Tester",
"user_role_id": 1,
"professional_bio": null,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-07-28T10:34:46.000000Z"
}
},
{
"id": 7,
"learner_id": 60,
"center_id": 2,
"client_id": 2,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-10-27T07:35:19.000000Z",
"learnerdetails": {
"id": 60,
"name": "name",
"username": "UN0006774",
"email": "email",
"token": "tokenval",
"email_verified_at": null,
"raw_password": "9OjYoJrm",
"mobile": "mobile number",
"city": null,
"state": null,
"address": null,
"role": null,
"profile_picture": null,
"gender": "Male",
"designation": "Designation",
"user_role_id": 1,
"professional_bio": null,
"active_status": 1,
"created_at": "2020-07-28T10:34:46.000000Z",
"updated_at": "2020-07-28T10:34:46.000000Z"
}
},
{
"id": 40,
"learner_id": 87,
"center_id": 2,
"client_id": 2,
"active_status": 1,
"created_at": "2020-10-23T10:12:59.000000Z",
"updated_at": "2020-10-27T07:35:19.000000Z",
"learnerdetails": null
}
]
Now i am applying sort by username and alerted like below when users click sort by name,
$userlist = LearnerCenterMap::with(['learnerdetails' => function($q) {
$q->orderBy('username','asc');
}])->where('center_id',$center_id)->where('client_id',$client_id)->get();
but above query applies sort only for learnerdetails how can i apply sort for base table LearnerCenterMap also? once user clicks sort then learnercentermap data should be sorted along with learner details as well.
Level 5
@sinnbeck sorry edited the question
Level 102
No worries. What should LearnerCenterMap be sorted by? The learner details as well?
In that case you need to join it in to be able to sort by it
$userlist = LearnerCenterMap::with('learnerdetails')
->join('users', 'users.id', '=', 'learners_center_maps.user_id')
->where('center_id',$center_id)
->where('client_id',$client_id)
->orderBy('users.username', 'asc')
->get();
Level 75
$userlist = LearnerCenterMap::select('learner_map.*')->with('learnerdetails')
->leftJoin('users', 'learner_map.user_id', '=', 'users.id')
->where('learner_map.center_id', $center_id)
->where('learner_map.client_id', $client_id)
->orderBy('users.username');
->get();
Level 5
@sinnbeck @michaloravec perfect thanks
Level 5
i have a username with below format , if username is : UN0001605
constant is: "UN000";
dynamic values will be displayed after constant so here 1605 is the dynamic value i am trying to sort this using below query,
$userlist = $userlist->orderByRaw('LENGTH(users.username)','desc')->orderBy('users.username','desc');
it is not working any wrong in this?
Please or to participate in this conversation.