Home / Questions / 537646
Accepted
Darinel Cigarroa
Asked: 2022-08-01 09:11:35 +0800 CST 2022-08-01 09:11:35 +0800 CST

Find a record falling between two ranges in Laravel?

  • 772

Hello everyone in advance, thank you very much for your answers and comments, I want to achieve the following, I am trying to search for a record according to two fields in my database, the structure of my table named "incident_days" is as follows:

---+----------------------------+---------------+-------------------+
|id|cat_employee_incident_type_id|first_year_rank| second_year_rank |
---+-----------------------------+---------------+------------------+
| 1|           2                 |        1      |        5         |
_________________________________+_______________+__________________+

In my controller I have the function validateIncidentDates in it I have a variable called $antiquity, with it I want to filter the records between the fields first_year_rank and second_year_rank, I am trying as follows:

public function validateIncidentDates (Request $request) {
    try {
        $antiquity = 3;

        $days_granted  = DB::table('incident_days')->where('cat_employee_incident_type_id', $request->incident_id)
        ->where($antiquity)->between('first_year_rank', 'and', 'second_year_rank')->first();

        return response()->json([
            'success' => true,
        ]);
    } catch (\Exception $e) {
        return response()->json([
            'success' => false,
            'message' => 'Error'
        ]);
    }
}

That is, I want to find the records where antiquity is between first_year_rank and second_yaear_rank, the table has more records, I have placed only one to make the question easier.

laravel

1 Answers

  1. Best Answer

    The search between dates is done like this:

    $query->whereBetween('columna', ['fecha_inicio', 'fecha_termino']);

    But I see in your case that you want to do a search between 2 columns in that case you must do a filter by the first column, then by the second and only then filter by antiquity

    example:

    DB::table('incident_days')->where('cat_employee_incident_type_id', $request->incident_id)
        ->where('first_year_rank','<',$antiquity)
        ->where('second_year_rank','>',$antiquity)->first();
    • 0