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

Question

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?

`->whereDate('date', '<=', date('Y-m-d'))` give that a shot. EDIT: Ignore me. sorry thought your field was just a date field. not a dateTime — Michael Mano, Apr 20 '22 at 06:21
Don't edit the question to include an answer. Instead, you should create a new answer to the question and put the solution into there (self-answers are perfectly acceptable and you can mark your own answer as the accepted solution) and then roll-back your edit to the question to the previous version. — MT0, Apr 20 '22 at 09:26

score 1 · Answer 1 · answered Apr 20 '22 at 10:16

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);

score 0 · Answer 2 · answered Apr 20 '22 at 06:04

0

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>

answered Apr 20 '22 at 06:04

Littlefoot

131,892
15
35
57

Thanks! But, that's the thing, I want the result with the order of the 'start_meeting' attribute, and I can't manually edit the SQL query because it's automatically generated by Laravel – Pika Chu Apr 20 '22 at 07:04
ORDER BY should then be applied to the "main" SELECT statement. EXISTS is used for a very different purpose - it just checks whether some rows exist or not, so sorting them is pretty much useless (and invalid, as Oracle showed you by raising the error). – Littlefoot Apr 20 '22 at 07:15

MT0 · Answer 3 · 2022-04-20T09:07:13.717

0

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.

edited Apr 20 '22 at 09:07

answered Apr 20 '22 at 08:41

MT0

143,790
11
59
117

I tried doing this a while ago, and it doesn't work since 'start meeting' is the attribute of 'meeting'. Any idea on how to order the AttendanceLists by the attribute of 'meeting' (order by an attribute of an attribute)? – Pika Chu Apr 20 '22 at 08:50
@PikaChu Sounds like you want a `join` rather than `whereHas`. – MT0 Apr 20 '22 at 08:53

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

3 Answers3