时间戳

首页 > 解决方案 > 获取最后一次注册的复杂查询无法正常工作 - 未找到列错误

php - 获取最后一次注册的复杂查询无法正常工作 - 未找到列错误

问题描述

我有这个查询是因为我想在会议管理页面中的表格中显示有关特定会议中最后 3 次注册的一些信息（进行注册的用户的名称、所选注册类型的数量和所选注册类型的总价格）：

<table>
    <thead>
    <tr>
        <th scope="col">User that did the registration</th>
        <th scope="col">Quantity</th>
        <th scope="col">Value</th>
    </tr>
    </thead>
    <tbody>
    @foreach($registrations as $registration)
        <tr>
            <td>{{ $registration->userName}}</td>
            <td>{{$registration->participants_count}}</td>
            <td>{{number_format($registration->totalPrice, 2)}}$</td>
        </tr>
    @endforeach
    </tbody>
</table>

因此，有一种manage()方法可以获取一些数据并将用户重定向到具有上述数据的会议管理页面：

 public function manage($id) // $id is the id of the conference
   ...
   $registrations = DB::table('registrations')
   ->join('participants', 'registrations.id', '=', 'participants.registration_id')
   ->join('users', 'users.id', '=', 'registrations.main_participant_id')
   ->join('registration_types', 'registration_types.id', '=', 'participants.registration_type_id')
   ->select(DB::raw('count(participants.registration_id) as participants_count, sum(registration_types.price) as totalPrice, users.name as userName, 
            registrations.created_at'))
  ->where('conferences.id',$conference->id)
  ->groupBy('registrations.id')->orderBy('created_at', 'desc')->get();

  dd($registrations)
  ....

  return view('conferences.manage')
         ->with('conference', $conference)
         ->with('registrations', $registrations)
         ->with(...);
    }

但它不能正常工作。当访问会议管理页面时，它会显示：

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'conferences.id' in 
'where clause' (SQL: select count(participants.registration_id) as participants_count, 
sum(registration_types.price) as totalPrice, 
users.name as userName, registrations.created_at from `registrations` 
inner join `participants` on `registrations`.`id` = `participants`.`registration_id`
inner join `users` on `users`.`id` = `registrations`.`main_participant_id` 
 inner join `registration_types` on `registration_types`.`id` = `participants`.`registration_type_id` 
 where `conferences`.`id` = 1 
 group by `registrations`.`id`
  order by `created_at` desc)

表结构：

  Conferences: id, name, user_id, ...
  Registrations: id, conference_id, user_that_did_registration
  Registration_types: id, name, conference_id, price, ...
  Participants: id, name, surname, registration_type_id

标签： phplaravel

解决方案

您似乎没有conferences在查询中包含该表。只要您不这样做，对它的任何引用（在这种情况下，在WHERE子句中）都会阻止查询运行并产生该错误

推荐阅读