mysql - Laravel - 未找到列 : 1054 Unknown column 'vw_winning_report.id'

Question

我尝试使用以下查询将五个表组合为 Mysql View ，从而生成报告:

CREATE VIEW vw_winning_report AS
 SELECT             
            id as tid,
            msisdn,
            game_code,
            game_name,
            prize,
            total_per_day,
            user_channel,
            created_at
       FROM trivial_winners
      UNION ALL
      SELECT 
            id as tid,
            msisdn,
            game_code,
            game_name,
            prize,
            total_per_day,
            user_channel,
            created_at         
       FROM predict_and_win
      UNION ALL
      SELECT 
            id as tid,
            msisdn,
            game_code,
            game_name,
            prize,
            total_per_day,
            user_channel,
            created_at        
       FROM party_with_bbn
      UNION ALL
      SELECT 
            id as tid,
            msisdn,
            game_code,
            game_name,
            prize,
            total_per_day,
            user_channel,
            created_at         
       FROM happy_hour
      UNION ALL
      SELECT 
            id as tid,
            msisdn,
            game_code,
            game_name,
            prize,
            total_per_day,
            user_channel,
            created_at          
       FROM grand_price           

然后我在 Laravel 中创建了一个模型类，名称为 WinningReport。

class WinningReport extends Model
{
 protected $table = 'vw_winning_report';

 protected $fillable = [
  'tid',
  'msisdn',
  'game_code',
  'game_name',
  'prize',
  'total_per_day',
  'user_channel',
  'created_at'
 ];       
}

然后是索引页和导出功能的 Controller 。 export WinningReport 用于导出，而 WiningReportReport(Request $request) 用于索引页。

    public function winningreportReport(Request $request)
{
    $data['title'] = 'Winning Report';

    $winners = DB::table('vw_winning_report')
    ->select(
       'msisdn', 
       'game_code',
       'game_name',
       DB::raw('DATE(created_at) as created_date')
  )               
 ->orderByRaw('created_at DESC'); 

    $render=[];       
    if(isset($request->msisdn))
    {
        $winners=$winners->where('msisdn','like','%'.$request->msisdn.'%');
        $render['msisdn']=$request->msisdn;
    }
    if(isset($request->game_code))
    {
        $winners=$winners->where('game_code',$request->game_code);
        $render['game_code']=$request->game_code;
    }               
    if(isset($request->start_date) && isset($request->end_date))
    {
        $winners=$winners->whereBetween('created_at',[$request->start_date,$request->end_date]);
        $render['start_date']=$request->start_date;
        $render['end_date']=$request->end_date;
    }elseif(isset($request->start_date))
    {
        $winners=$winners->where('created_at',$request->start_date);
        $render['start_date']=$request->start_date;
    }         
    $winners= $winners->orderBy('created_at','DESC');
    $winners= $winners->paginate(15);
    $winners= $winners->appends($render);
    $data['winners'] = $winners;

return view('report.winningreportReport',$data);        
}

public function exportwinningreport() 
{
    return Excel::download(new WinningreportExport, 'winningreport.xlsx');
}     

然后，对于单击提交按钮时的导出，我们有:

class WinningreportExport implements FromQuery, WithHeadings, ShouldAutoSize, WithEvents
{

use Exportable;
/**
* @return \Illuminate\Support\Collection
*/
public function query()
{
    return WinningReport::query()->select(
    DB::raw("DATE(created_at)"),
    'msisdn',
    'game_code',
    'game_name'
            );
}    

public function headings(): array
{
    return [
    'Date Created',
    'MSISDN',
    'Game Code',
    'Game Name',
    ];
}   

public function registerEvents(): array
{
    return [
        AfterSheet::class    => function(AfterSheet $event) {
            $cellRange = 'A1:D1'; // All headers
            $event->sheet->getDelegate()->getStyle($cellRange)->getFont()->setSize(14);
        },

    ];
}       
}

索引 View 页面工作正常，但是当我单击导出提交按钮时出现此错误:

"SQLSTATE[42S22]: Column not found: 1054 Unknown column 'vw_winning_report.id' in 'order clause' (SQL: select DATE(created_at), msisdn, game_code, game_name from vw_winning_report order by vw_winning_report.id asc limit 1000 offset 0)

问题的原因是什么以及如何解决。

谢谢

Answer 1

已解决。我刚刚添加了

->orderByRaw('created_at DESC');

到查询并且它有效。

谢谢大家