php - 如何根据数据库中的列排序日期,最近的日期在顶部
问题描述
我的数据库中有 6 个不同的表,所有 6 个表中都有一个列“created_at”。我想订购我的回复,以便最近的帖子出现在顶部。我正在使用 orderBy('created_at') 但它以错误的顺序返回帖子。我的数据库中有类型为“timestamp”的“created_at”列。它将在 2019 年 10 月 1 日至 10 月 6 日、2019 年 10 月 2 日至 7 日、2019 年 9 月 3 日至 23 日、2019 年 9 月 4 日至 26 日、2019 年 9 月 5 日至 27 日等按此顺序退回帖子。这是我的代码:-
public function wsUserActivity(){
$request = Input::all();
try {
$user_id = $request['user_id'];
$no=isset($request['page_number'])?$request['page_number']:0;
$nos=isset($request['count'])?$request['count']:10;
$skp=$no*$nos;
$array_json_return = array('status' => '1','msg' => 'Success');
$u_activity = array();
$u_article = DB::table('mst_article as article')
->select(DB::raw('"article" as type'),'id','title', DB::raw('DATE_FORMAT(created_at, "%d %b %Y") as created_at'), DB::raw('DATE_FORMAT(updated_at, "%d %b %Y") as updated_at'), 'imported', 'import_url', 'cover_type', 'profile_image')
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1');
$u_meetup = DB::table('mst_meetup as meetup')
->select(DB::raw('"meetup" as type'),'id','title', DB::raw('DATE_FORMAT(created_at, "%d %b %Y") as created_at'), DB::raw('DATE_FORMAT(updated_at, "%d %b %Y") as updated_at'), 'imported', 'import_url', 'cover_type', 'profile_image' )
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1');
$u_question = array();
$u_question = DB::table('mst_question as question')
->select(DB::raw('"question" as type'),'id','title', DB::raw('DATE_FORMAT(created_at, "%d %b %Y") as created_at'), DB::raw('DATE_FORMAT(updated_at, "%d %b %Y") as updated_at'), 'imported', 'import_url', 'cover_type', 'profile_image')
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1');
$u_job = array();
$u_job = DB::table('mst_job as job')
->select(DB::raw('"job" as type'),'id','title', DB::raw('DATE_FORMAT(created_at, "%d %b %Y") as created_at'), DB::raw('DATE_FORMAT(updated_at, "%d %b %Y") as updated_at'), 'imported', 'import_url', 'cover_type', 'profile_image')
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1');
$u_education = array();
$u_education = DB::table('mst_education as education')
->select(DB::raw('"education" as type'),'id','title', DB::raw('DATE_FORMAT(created_at, "%d %b %Y") as created_at'), DB::raw('DATE_FORMAT(updated_at, "%d %b %Y") as updated_at'), 'imported', 'import_url', 'cover_type', 'profile_image')
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1');
$u_activity= DB::table('mst_event as event')
->select(DB::raw('"event" as type'),'id','title', DB::raw('DATE_FORMAT(created_at, "%d %b %Y") as created_at'), DB::raw('DATE_FORMAT(updated_at, "%d %b %Y") as updated_at'), 'imported', 'import_url', 'cover_type', 'profile_image' )
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1')
->union($u_article)->union($u_question)->union($u_meetup)->union($u_job)->union($u_education)
->orderBy('created_at')
->skip($skp)
->take($nos)
->get();
if (count($u_activity)>0){
foreach ($u_activity as $key => $value){
if (!empty($value->profile_image)){
$u_activity[$key]->profile_image_url = config("feature_pic_url").$value->type.'_image/thumb/'.$value->profile_image;
}
$u_activity[$key]->post_url = url('/') . '/view-'.$value->type. '/' . $value->id;
}
}
$array_json_return['u_activity'] = $u_activity;
} catch (\Exception $e) {
$array_json_return = $this->api_default_fail_response(__function__, $e);
}
echo json_encode($array_json_return);
}
解决方案
public function wsUserActivity(){
$request = Input::all();
try {
$user_id = $request['user_id'];
$no=isset($request['page_number'])?$request['page_number']:0;
$nos=isset($request['count'])?$request['count']:10;
$skp=$no*$nos;
$array_json_return = array('status' => '1','msg' => 'Success');
$u_activity = array();
$u_article = DB::table('mst_article as article')
->select(DB::raw('"article" as type'),'id','title', 'created_at', 'updated_at', 'imported', 'import_url', 'cover_type', 'profile_image')
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1');
$u_meetup = DB::table('mst_meetup as meetup')
->select(DB::raw('"meetup" as type'),'id','title', 'created_at', 'updated_at', 'imported', 'import_url', 'cover_type', 'profile_image' )
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1');
$u_question = array();
$u_question = DB::table('mst_question as question')
->select(DB::raw('"question" as type'),'id','title', 'created_at', 'updated_at', 'imported', 'import_url', 'cover_type', 'profile_image')
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1');
$u_job = array();
$u_job = DB::table('mst_job as job')
->select(DB::raw('"job" as type'),'id','title','created_at', 'updated_at', 'imported', 'import_url', 'cover_type', 'profile_image')
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1');
$u_education = array();
$u_education = DB::table('mst_education as education')
->select(DB::raw('"education" as type'),'id','title', 'created_at', DB::raw('DATE_FORMAT(updated_at, "%d %b %Y") as updated_at'), 'imported', 'import_url', 'cover_type', 'profile_image')
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1');
$u_activity= DB::table('mst_event as event')
->select(DB::raw('"event" as type'),'id','title', 'created_at', 'updated_at', 'imported', 'import_url', 'cover_type', 'profile_image' )
->selectRaw('SUBSTRING(`description`, 1, 200) as `description`')
->where('user_id_fk',$user_id)
->where('status', '=', '1')
->union($u_article)->union($u_question)->union($u_meetup)->union($u_job)->union($u_education)
->skip($skp)
->take($nos)
->latest()
->get();
if (count($u_activity)>0){
foreach ($u_activity as $key => $value){
$u_activity[$key]->created_at = DATE_FORMAT(new \DateTime($value->created_at), "d M Y");
$u_activity[$key]->updated_at = DATE_FORMAT(new \DateTime($value->updated_at), "d M Y");
if (!empty($value->profile_image)){
$u_activity[$key]->profile_image_url = config("feature_pic_url").$value->type.'_image/thumb/'.$value->profile_image;
}
$u_activity[$key]->post_url = url('/') . '/view-'.$value->type. '/' . $value->id;
}
}
$array_json_return['u_activity'] = $u_activity;
} catch (\Exception $e) {
$array_json_return = $this->api_default_fail_response(__function__, $e);
}
echo json_encode($array_json_return);
}
推荐阅读
- azure-devops - 如何将文本文件中的值传递给 Azure Devops 变量并在另一个任务中使用
- powershell - 使用 Powershell,我们如何连接两个包含双引号的字符串变量?
- json - 无限滚动 Ajax JSON 尝试一次获取 10 个随机项目
- sql - 查询结果中缺少数据
- java - 如何找到整数中重复次数最多的最高数
- html - 如何将带有 th:action 的表单附加到输入 onfocusout
- python - MongoDB无法连接
- git - git rebase 显示在后续提交中已经解决的冲突
- python - AWS MSK 使用 shell 命令但不使用 python
- wordpress - WordPress:新浏览器会话的首次登录总是失败