Complex DB query

I had to write a complex query for a project long ago. At the time it was written using Drupal. I'm trying to figure out how to write this in Laravel.

I have resorted to writing it using DB::raw(), but would really like to know how to write it using Laravel. It's go so much that I get lost when trying to group parameters.

Can someone show me how the following would be written in Laravel?

Thanks in advance for your time and your help.

$raw_sql = "SELECT * FROM projects WHERE
                      (
                          (
                            (start_ts <= $start_month) OR (start_ts BETWEEN $start_month AND $end_month)
                          )
                          AND 
                          (
                            (end_ts BETWEEN $start_month AND $end_month) OR (end_ts > $end_month)
                          )
                      )
                      AND (cat_id = 1)";

         $query = DB::select(DB::raw($raw_sql));

sherwinmdev

8 years ago

Level 6

haha i started to write it out but got a headache. but here is how i would approach it. it's a very rough draft but i hope it gives you an idea of what you can do. essentially, you can use closures to chain them. hope that helps.

Project::where(function($query) use($start_month, $end_month) {
    $query->where(function($query2) use($start_month, $end_month) {
        $query2->where('start_ts', '<=', $start_month)
            ->orWhere('start_ts', 'BETWEEN', $start_month...);
    };
})
->where('cat_id', 1)->get();

sutherland

8 years ago

Level 28

Couldn't your original query be simplified? I'd use this:

Project::where('start_ts', '<', $end_month)
    ->where('end_ts', '>', $start_month)
    ->where('cat_id', 1)
    ->get();

And if you only want to compare the month value, Laravel has a whereMonth() function as well.

lara68236

8 years ago

Level 1

The purpose of the query is to find any record of a said category that already exists within the date range the user selected.

If a user puts in the dates of 2/2/18 - 5/15/18 and it records that record successfully, then the months of February, March, April, and May are taken and cannot exist in any other entry for that category.

So if the user tried to place any date in another entry that contained any one of those months, they would be alerted that the months are already taken by another record. There cannot be any overlap.

The start_ts is saved as mm/1/yyyy 00:00:00. The end_ts is saved as mm/last-day-of-month/yyyy 23:59:59.

If anyone knows of another way to do the exact same thing I am certainly open to it. Working with date/time is not my strong point. :)

sutherland

8 years ago

Level 28

I think this is prety much what you'd need then.

Project::whereMonth('start_ts', '<=', $end_month)
    ->whereMonth('end_ts', '>=', $start_month)
    ->where('cat_id', 1)
    ->get();

Using (start <= $start) OR (start BETWEEN $start AND $end) is the same as using (start <= $start) OR (start >= $start AND start <= $end) so the only part of that statement that's actually useful is start <= $end

lara68236

8 years ago

Best Answer

Level 1

Mine is kinda similar. I decided to go with

Project::where ('cat_id', '=', 1)
            ->whereBetween('start_ts', [$start_month, $end_month])
            ->orWhereBetween('end_ts', [$start_month, $end_month])
            ->get();

Of course I now I realize that I was completely over-thinking this. The other project was even more complex than this. In this case, as long as a recorded entry does not contain a start or end date within the range of the new record, then the new guy can come on in. :D

Thanks for the responses and giving me stuff to think about more clearly.

sutherland

8 years ago

Level 28

@cservices I think you'll find that doesn't work well. Imagine that there's a project that starts in January and ends in May. If someone comes along and tries adding one that starts in February and ends in April, your query won't find that there's an overlapping project because neither the start date or end date of the existing project is between the start and end of the new project.

Please or to participate in this conversation.