Level 104
Are the values in the database "0" or NULL?
Mar 31, 2021
3
Level 5
get null values at last in query orderBY
i have a query like below,
public function getTest()
{
return StudentMap::orderBy('project_number')
->get();
}
The model is,
public function getProjectNumberAttribute($value)
{
if($value == "0")
{
return NULL;
} else {
return $value;
}
}
The response is,
[
{
"student_category_mapping_id": 1,
"region_id": 1,
"student_id": 1,
"category_id": 11067,
"category_id_round_two": null,
"pref_cat_id": 11067,
"project_number": null,
},
{
"student_category_mapping_id": 6,
"region_id": 44,
"student_id": 6,
"category_id": 11093,
"category_id_round_two": null,
"pref_cat_id": 11093,
"project_number": null,
},
{
"student_category_mapping_id": 7,
"region_id": 2,
"student_id": 6,
"category_id": 11093,
"category_id_round_two": null,
"pref_cat_id": 11093,
"project_number": null,
},
{
"student_category_mapping_id": 8,
"region_id": 44,
"student_id": 7,
"category_id": 11101,
"category_id_round_two": null,
"pref_cat_id": 11101,
"project_number": null,
},
{
"student_category_mapping_id": 10,
"region_id": 44,
"student_id": 8,
"category_id": 11099,
"category_id_round_two": null,
"pref_cat_id": 11099,
"project_number": null,
},
{
"student_category_mapping_id": 2,
"region_id": 2,
"student_id": 2,
"category_id": 11095,
"category_id_round_two": null,
"pref_cat_id": 11095,
"project_number": "BCHM001MS",
},
{
"student_category_mapping_id": 5,
"region_id": 2,
"student_id": 5,
"category_id": 11094,
"category_id_round_two": null,
"pref_cat_id": 11094,
"project_number": "BEHA001MS",
},
{
"student_category_mapping_id": 4,
"region_id": 2,
"student_id": 4,
"category_id": 11099,
"category_id_round_two": null,
"pref_cat_id": 11099,
"project_number": "CHEM001MS",
},
{
"student_category_mapping_id": 11,
"region_id": 2,
"student_id": 8,
"category_id": 11099,
"category_id_round_two": null,
"pref_cat_id": 11099,
"project_number": "CHEM002MS",
},
{
"student_category_mapping_id": 9,
"region_id": 2,
"student_id": 7,
"category_id": 11101,
"category_id_round_two": null,
"pref_cat_id": 11101,
"project_number": "EAEV001MF",
},
{
"student_category_mapping_id": 12,
"region_id": 1,
"student_id": 9,
"category_id": 11061,
"category_id_round_two": null,
"pref_cat_id": 11061,
"project_number": "ENMC001MS",
},
{
"student_category_mapping_id": 3,
"region_id": 2,
"student_id": 3,
"category_id": 11099,
"category_id_round_two": null,
"pref_cat_id": 11099,
"project_number": "qwertyu",
}
]
Here I want to apply orderby for column project_number and I want to display null values of the project_number column at last. non null values of project_number column should come first,
I tried code like below,
return StudentMap::select(['*', DB::raw('project_number IS NULL AS projectnumberNull')])
->orderBy('projectnumberNull','ASC')
->orderBy('project_number')->get();
But no use,
I want the response like below,
[
{
"student_category_mapping_id": 2,
"region_id": 2,
"student_id": 2,
"category_id": 11095,
"category_id_round_two": null,
"pref_cat_id": 11095,
"project_number": "BCHM001MS",
},
{
"student_category_mapping_id": 5,
"region_id": 2,
"student_id": 5,
"category_id": 11094,
"category_id_round_two": null,
"pref_cat_id": 11094,
"project_number": "BEHA001MS",
},
{
"student_category_mapping_id": 4,
"region_id": 2,
"student_id": 4,
"category_id": 11099,
"category_id_round_two": null,
"pref_cat_id": 11099,
"project_number": "CHEM001MS",
},
{
"student_category_mapping_id": 11,
"region_id": 2,
"student_id": 8,
"category_id": 11099,
"category_id_round_two": null,
"pref_cat_id": 11099,
"project_number": "CHEM002MS",
},
{
"student_category_mapping_id": 9,
"region_id": 2,
"student_id": 7,
"category_id": 11101,
"category_id_round_two": null,
"pref_cat_id": 11101,
"project_number": "EAEV001MF",
},
{
"student_category_mapping_id": 12,
"region_id": 1,
"student_id": 9,
"category_id": 11061,
"category_id_round_two": null,
"pref_cat_id": 11061,
"project_number": "ENMC001MS",
},
{
"student_category_mapping_id": 3,
"region_id": 2,
"student_id": 3,
"category_id": 11099,
"category_id_round_two": null,
"pref_cat_id": 11099,
"project_number": "qwertyu",
},
{
"student_category_mapping_id": 1,
"region_id": 1,
"student_id": 1,
"category_id": 11067,
"category_id_round_two": null,
"pref_cat_id": 11067,
"project_number": null,
},
{
"student_category_mapping_id": 6,
"region_id": 44,
"student_id": 6,
"category_id": 11093,
"category_id_round_two": null,
"pref_cat_id": 11093,
"project_number": null,
},
{
"student_category_mapping_id": 7,
"region_id": 2,
"student_id": 6,
"category_id": 11093,
"category_id_round_two": null,
"pref_cat_id": 11093,
"project_number": null,
},
{
"student_category_mapping_id": 8,
"region_id": 44,
"student_id": 7,
"category_id": 11101,
"category_id_round_two": null,
"pref_cat_id": 11101,
"project_number": null,
},
{
"student_category_mapping_id": 10,
"region_id": 44,
"student_id": 8,
"category_id": 11099,
"category_id_round_two": null,
"pref_cat_id": 11099,
"project_number": null,
},
]
Please help me with this.
Level 5
in db it is zero but i have used in model to replace the value,
public function getProjectNumberAttribute($value)
{
if($value == "0")
{
return NULL;
} else {
return $value;
}
}
Level 104
The model accessor is not available at the query level. You can use an orderByRaw to arrange certain records based on their values, e.g. something like the following;
StudentMap::orderByRaw(CASE project_number WHEN 0 THEN 1 ELSE 0 END, project_number')->get()
Please or to participate in this conversation.