Error Code : 907 Error Message : ORA-00907: missing right parenthesis PHP Laravel (Eloquent method)-CodePudding

After I had a collection from a query, I want to sort it. Then, this error message came up:

Error Code : 907 Error Message : ORA-00907: missing right parenthesis Position : 202 Statement : select count(*) as aggregate from "ATTENDANCE_LISTS" where exists (select * from "MEETINGS" where "ATTENDANCE_LISTS"."MEETING_ID" = "MEETINGS"."ID" and "STATUS_MEETING" = :p0 and "START_MEETING" <= :p1 order by "START_MEETING" desc) Bindings : [Disetujui,2022-04-19 20:11:24] (SQL: select count(*) as aggregate from "ATTENDANCE_LISTS" where exists (select * from "MEETINGS" where "ATTENDANCE_LISTS"."MEETING_ID" = "MEETINGS"."ID" and "STATUS_MEETING" = Disetujui and "START_MEETING" <= 2022-04-19 20:11:24 order by "START_MEETING" desc))

The code is as follows:

$meetings2 = AttendanceLists::whereHas('meeting', function ($query) {
    $now = new DateTime("now");
    $query->where('status_meeting', '=', 'Disetujui')
    ->where('start_meeting', '<=', $now)
    ->orderBy('start_meeting', 'desc')
    ;
})->paginate(5);

I only built the query with the Laravel eloquent method above, and I have been struggling with this for days. Please help me.

Yes, there's a seemingly similar post right here: ORA-00907: missing right parenthesis

But, the problem I have has nothing to do with manually constructing the query with SQL format. I constructed the query using PHP Laravel eloquent method, so it can't really be about a missing parenthesis.**

Edit: In a nutshell, the problem comes when I tried to order the AttendanceLists by the attribute of 'meeting' (order by an attribute of an attribute). Any help?

CodePudding user response：

Here's the solution.

The solution is to change the whereHas to join. Here's the code:

$meetings = AttendanceLists::join('meetings', 'meetings.id', '=', 'attendance_lists.meeting_id')
    ->where('attendance_lists.user_id', '=', $id_user)
    ->where('meetings.status_meeting', '=', 'Disetujui')
    ->where('meetings.start_meeting', '<', $now)
    ->orderBy('meetings.start_meeting', 'desc')
    ->paginate(5);

CodePudding user response：

I don't know PHP nor Laravel, but - from Oracle side of the story, it is the ORDER BY clause you use in the EXISTS subquery. Here's a demo.

This is what you have:

SQL> select * from dual
  2  where exists (select *
  3                from dual
  4                order by dummy);
              order by dummy)
              *
ERROR at line 4:
ORA-00907: missing right parenthesis

Remove ORDER BY clause:

SQL> select * from dual
  2  where exists (select *
  3                from dual
  4               );

D
-
X

SQL>

CodePudding user response：

It looks like you should move the order by clause out of the whereHas function:

$meetings2 = AttendanceLists::whereHas(
  'meeting',
  function ($query) {
    $now = new DateTime("now");
    $query->where('status_meeting', '=', 'Disetujui')
          ->where('start_meeting', '<=', $now);
})->orderBy('start_meeting', 'desc')
  ->paginate(5);

(Note: this is untested as I don't have access to Laravel or your schema.)

Alternatively, use a join rather than whereHas.