Query working on local, failing on production

So I'm pretty confused because I have a query that passed all tests on the local environment but on production it fails.

This is the query.

if ($request->date === null) {
    $direction[0] = $request->direction ? 'max(date)' : 'min(date)';
    $direction[1] = $request->direction ? 'DESC' : 'ASC';

    $dataRevToday = Revision::select((DB::raw($direction[0] . ' as "date"')))
        ->where('office_id', auth()->user()->office_id)
        ->whereDate('date', '=', Carbon::now()->toDateString())
        ->where('time', '>', Carbon::now()->toTimeString())
        ->where('available', 1)
        ->where('locked', 0)
        ->where('active', 1)
        ->when($request->location_filter !== null, function ($query) use ($request) {
            return $query->where('location_id', $request->location_filter);
        });

    $dataRevAfterToday = Revision::select((DB::raw($direction[0] . ' as "date"')))
        ->where('office_id', auth()->user()->office_id)
        ->whereDate('date', '>', Carbon::now()->toDateString())
        ->where('available', 1)
        ->where('locked', 0)
        ->where('active', 1)
        ->when($request->location_filter !== null, function ($query) use ($request) {
            return $query->where('location_id', $request->location_filter);
        });

    $dataRev = $dataRevToday->union($dataRevAfterToday);

    if ($request->entity_filter === null) {
        $dataAppToday = Appointment::select((DB::raw($direction[0] . ' as "date"')))
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '=', Carbon::now()->toDateString())
            ->where('time', '>', Carbon::now()->toTimeString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataAppAfterToday = Appointment::select((DB::raw($direction[0] . ' as "date"')))
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '>', Carbon::now()->toDateString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataApp = $dataAppToday->union($dataAppAfterToday);
    } else {
        $dataAppToday = Appointment::select((DB::raw($direction[0] . ' as "date"')))
            ->join('appointment_entity', 'appointments.id', '=', 'appointment_entity.appointment_id')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '=', Carbon::now()->toDateString())
            ->where('time', '>', Carbon::now()->toTimeString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->where('entity_id', $request->entity_filter)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataAppAfterToday = Appointment::select((DB::raw($direction[0] . ' as "date"')))
            ->join('appointment_entity', 'appointments.id', '=', 'appointment_entity.appointment_id')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '>', Carbon::now()->toDateString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->where('entity_id', $request->entity_filter)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataApp = $dataAppToday->union($dataAppAfterToday);
    }

    $data = $dataRev->union($dataApp)
        ->orderByRaw('date ' . $direction[1])
        ->limit(1)
        ->first();
} else {
    $formatDate = Carbon::createFromFormat('Y-m-d', date('Y-m-d', strtotime($request->date)));
    $direction[0] = $request->direction ? '>' : '<';
    $direction[1] = $request->direction ? 'ASC' : 'DESC';

    $dataRevToday = Revision::select('date')
        ->where('office_id', auth()->user()->office_id)
        ->whereDate('date', '=', Carbon::now()->toDateString())
        ->where('time', '>', Carbon::now()->toTimeString())
        ->where('available', 1)
        ->where('locked', 0)
        ->where('active', 1)
        ->whereDate('date', $direction[0], $formatDate->toDateString())
        ->whereDate('date', '>=', Carbon::createFromFormat('Y-m-d', Carbon::now()->toDateString()))
        ->when($request->location_filter !== null, function ($query) use ($request) {
            return $query->where('location_id', $request->location_filter);
        });

    $dataRevAfterToday = Revision::select('date')
        ->where('office_id', auth()->user()->office_id)
        ->whereDate('date', '>', Carbon::now()->toDateString())
        ->where('available', 1)
        ->where('locked', 0)
        ->where('active', 1)
        ->whereDate('date', $direction[0], $formatDate->toDateString())
        ->whereDate('date', '>=', Carbon::createFromFormat('Y-m-d', Carbon::now()->toDateString()))
        ->when($request->location_filter !== null, function ($query) use ($request) {
            return $query->where('location_id', $request->location_filter);
        });

    $dataRev = $dataRevToday->union($dataRevAfterToday);

    if ($request->entity_filter === null) {
        $dataAppToday = Appointment::select('date')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '=', Carbon::now()->toDateString())
            ->where('time', '>', Carbon::now()->toTimeString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->whereDate('date', $direction[0], $formatDate->toDateString())
            ->whereDate('date', '>=', Carbon::createFromFormat('Y-m-d', Carbon::now()->toDateString()))
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataAppAfterToday = Appointment::select('date')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '>', Carbon::now()->toDateString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->whereDate('date', $direction[0], $formatDate->toDateString())
            ->whereDate('date', '>=', Carbon::createFromFormat('Y-m-d', Carbon::now()->toDateString()))
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataApp = $dataAppToday->union($dataAppAfterToday);
    } else {
        $dataAppToday = Appointment::select('date')
            ->join('appointment_entity', 'appointments.id', '=', 'appointment_entity.appointment_id')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '=', Carbon::now()->toDateString())
            ->where('time', '>', Carbon::now()->toTimeString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->whereDate('date', $direction[0], $formatDate->toDateString())
            ->whereDate('date', '>=', Carbon::createFromFormat('Y-m-d', Carbon::now()->toDateString()))
            ->where('entity_id', $request->entity_filter)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataAppAfterToday = Appointment::select('date')
            ->join('appointment_entity', 'appointments.id', '=', 'appointment_entity.appointment_id')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '>', Carbon::now()->toDateString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->whereDate('date', $direction[0], $formatDate->toDateString())
            ->whereDate('date', '>=', Carbon::createFromFormat('Y-m-d', Carbon::now()->toDateString()))
            ->where('entity_id', $request->entity_filter)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataApp = $dataAppToday->union($dataAppAfterToday);
    }

    $data = $dataRev->union($dataApp)
        ->orderByRaw('date ' . $direction[1])
        ->limit(1)
        ->first();
}

and this is the error log in production:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '((select date from appointments where office_id = ? and date(date) = ? a' at line 1 (SQL: (select date from revisions where office_id = 1 and date(date) = 2020-04-25 and time > 12:34:06 and available = 1 and locked = 0 and active = 1 and date(date) > 2020-04-25 and date(date) >= 2020-04-25) union (select date from revisions where office_id = 1 and date(date) > 2020-04-25 and available = 1 and locked = 0 and active = 1 and date(date) > 2020-04-25 and date(date) >= 2020-04-25) union ((select date from appointments where office_id = 1 and date(date) = 2020-04-25 and time > 12:34:06 and available = 1 and locked = 0 and active = 1 and date(date) > 2020-04-25 and date(date) >= 2020-04-25) union (select date from appointments where office_id = 1 and date(date) > 2020-04-25 and available = 1 and locked = 0 and active = 1 and date(date) > 2020-04-25 and date(date) >= 2020-04-25)) order by date ASC limit 1)

I will assume there's a module in place in my forge + DO server doing some extra checks on this then failing?

Thanks in advance for any help

EDIT: Yes, I have noticed I have some redundancies I gotta fix, that was after a change yesterday, apologies for that

cuartas15

6 years ago

Level 2

Answering myself. The solution was to avoid doing the union of two variables that are anoter union operation, better to nest them at the end when you're gonna do the actual query.

My final code is this:

if ($request->date === null) {
    $direction[0] = $request->direction ? 'max(date)' : 'min(date)';
    $direction[1] = $request->direction ? 'DESC' : 'ASC';

    $dataRevToday = Revision::select((DB::raw($direction[0] . ' as "date"')))
        ->where('office_id', auth()->user()->office_id)
        ->whereDate('date', '=', Carbon::now()->toDateString())
        ->where('time', '>', Carbon::now()->toTimeString())
        ->where('available', 1)
        ->where('locked', 0)
        ->where('active', 1)
        ->when($request->location_filter !== null, function ($query) use ($request) {
            return $query->where('location_id', $request->location_filter);
        });

    $dataRevAfterToday = Revision::select((DB::raw($direction[0] . ' as "date"')))
        ->where('office_id', auth()->user()->office_id)
        ->whereDate('date', '>', Carbon::now()->toDateString())
        ->where('available', 1)
        ->where('locked', 0)
        ->where('active', 1)
        ->when($request->location_filter !== null, function ($query) use ($request) {
            return $query->where('location_id', $request->location_filter);
        });
    if ($request->entity_filter === null) {
        $dataAppToday = Appointment::select((DB::raw($direction[0] . ' as "date"')))
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '=', Carbon::now()->toDateString())
            ->where('time', '>', Carbon::now()->toTimeString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataAppAfterToday = Appointment::select((DB::raw($direction[0] . ' as "date"')))
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '>', Carbon::now()->toDateString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });
    } else {
        $dataAppToday = Appointment::select((DB::raw($direction[0] . ' as "date"')))
            ->join('appointment_entity', 'appointments.id', '=', 'appointment_entity.appointment_id')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '=', Carbon::now()->toDateString())
            ->where('time', '>', Carbon::now()->toTimeString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->where('entity_id', $request->entity_filter)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataAppAfterToday = Appointment::select((DB::raw($direction[0] . ' as "date"')))
            ->join('appointment_entity', 'appointments.id', '=', 'appointment_entity.appointment_id')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '>', Carbon::now()->toDateString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->where('entity_id', $request->entity_filter)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });
    }

    $data = $dataRevToday->union($dataRevAfterToday)->union($dataAppToday)->union($dataAppAfterToday)
        ->orderByRaw('date ' . $direction[1])
        ->limit(1)
        ->first();
} else {
    $formatDate = Carbon::createFromFormat('Y-m-d', date('Y-m-d', strtotime($request->date)));
    $direction[0] = $request->direction ? '>' : '<';
    $direction[1] = $request->direction ? 'ASC' : 'DESC';

    $dataRevToday = Revision::select('date')
        ->where('office_id', auth()->user()->office_id)
        ->whereDate('date', '=', Carbon::now()->toDateString())
        ->where('time', '>', Carbon::now()->toTimeString())
        ->where('available', 1)
        ->where('locked', 0)
        ->where('active', 1)
        ->when($request->location_filter !== null, function ($query) use ($request) {
            return $query->where('location_id', $request->location_filter);
        });

    $dataRevAfterToday = Revision::select('date')
        ->where('office_id', auth()->user()->office_id)
        ->whereDate('date', '>', Carbon::now()->toDateString())
        ->where('available', 1)
        ->where('locked', 0)
        ->where('active', 1)
        ->whereDate('date', $direction[0], $formatDate->toDateString())
        ->when($request->location_filter !== null, function ($query) use ($request) {
            return $query->where('location_id', $request->location_filter);
        });
    if ($request->entity_filter === null) {
        $dataAppToday = Appointment::select('date')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '=', Carbon::now()->toDateString())
            ->where('time', '>', Carbon::now()->toTimeString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataAppAfterToday = Appointment::select('date')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '>', Carbon::now()->toDateString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->whereDate('date', $direction[0], $formatDate->toDateString())
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });
    } else {
        $dataAppToday = Appointment::select('date')
            ->join('appointment_entity', 'appointments.id', '=', 'appointment_entity.appointment_id')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '=', Carbon::now()->toDateString())
            ->where('time', '>', Carbon::now()->toTimeString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->where('entity_id', $request->entity_filter)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });

        $dataAppAfterToday = Appointment::select('date')
            ->join('appointment_entity', 'appointments.id', '=', 'appointment_entity.appointment_id')
            ->where('office_id', auth()->user()->office_id)
            ->whereDate('date', '>', Carbon::now()->toDateString())
            ->where('available', 1)
            ->where('locked', 0)
            ->where('active', 1)
            ->whereDate('date', $direction[0], $formatDate->toDateString())
            ->where('entity_id', $request->entity_filter)
            ->when($request->location_filter !== null, function ($query) use ($request) {
                return $query->where('location_id', $request->location_filter);
            });
    }

    $data = $dataRevToday->union($dataRevAfterToday)->union($dataAppToday)->union($dataAppAfterToday)
        ->orderByRaw('date ' . $direction[1])
        ->limit(1)
        ->first();
}

This was the key to fix it:

$data = $dataRevToday->union($dataRevAfterToday)->union($dataAppToday)->union($dataAppAfterToday)

As to why is working on local, I really have no idea

Please or to participate in this conversation.