Reservation System - Check for timeconflicts

Hi,

i'm get crazy. I try to write a query, for a reservation system, to find all free stocks. A customer can rent a stock for a time. So i have Articles, Stocks, Stations, BookingArticles, Bookings

My first try was: [code] /** * Find free Stocks available for a Station to Date * @param datetime $date_from * @param datetime $date_to * @return Stock[] */ public function findFreeStocksByStation($station_id, $date_from, $date_to) { return $this->stocks()->where('available', 1)->whereHas('station', function($q) use($station_id) { $q->where('id', $station_id); })->doesntHave('bookings')->orWhereHas('bookings', function($q) use ($date_from, $date_to) { $q->with(['booking' => function ($q2) use ($date_from, $date_to) { $q2->where('date_from', '>=', $date_from) ->orWhere('date_to', '<=', $date_to); }]); }); } [/code]

That is not working. So i think - i write a Query.

[code] public function findFreeStocksByStation($station_id, $date_from, $date_to) {

    return DB::table('stocks')->select(DB::raw('stocks.*'))
            ->join('stations', function($q) {
                $q->on('stations.id', '=', 'stocks.station_id')->where('stations.deleted_at', null);
            })->leftJoin('booking_articles', function($q) use($date_from, $date_to) {
                $q->on('booking_articles.stock_id', '=', 'stocks.id');
            })->leftJoin('bookings', function($q) use($date_from, $date_to) {
                $q->on('bookings.id', '=', 'booking_articles.booking_id')
                       ->where('bookings.date_to', '<=', $date_from)
                        ->where('bookings.date_from', '>=', $date_to)
                        ->where('bookings.deleted_at', null); 
            })
                    ->where('stations.id', '=', $station_id)
                    ->where('stocks.article_id', '=', $this->id)
                    ->where('bookings.id', null)
                    ->where('stocks.available', true)
                    ->where('stocks.deleted_at', null);
                    
    
}

[/code]

That is not working too. Any Ideas?

To explain: Article Model -> stocks (many) -> bookings (many BookingArticles) -> booking (one)

hodel1

7 years ago

Level 1

Oh i cant edit this now. So i write the code below with Markdown :D

public function old($station_id, $date_from, $date_to) {
        return $this->stocks()->where('available', 1)->whereHas('station', function($q) use($station_id) {
            $q->where('id', $station_id);
        })->doesntHave('bookings')->orWhereHas('bookings', function($q) use ($date_from, $date_to) {
            $q->with(['booking' => function ($q2) use ($date_from, $date_to) {
                $q2->where('date_from', '>=', $date_from)
                   ->orWhere('date_to', '<=', $date_to);
            }]);
        });
    }

public function findFreeStocksByStation($station_id, $date_from, $date_to) {
        
        return DB::table('stocks')->select(DB::raw('stocks.*'))
                ->join('stations', function($q) {
                    $q->on('stations.id', '=', 'stocks.station_id')->where('stations.deleted_at', null);
                })->leftJoin('booking_articles', function($q) use($date_from, $date_to) {
                    $q->on('booking_articles.stock_id', '=', 'stocks.id');
                })->leftJoin('bookings', function($q) use($date_from, $date_to) {
                    $q->on('bookings.id', '=', 'booking_articles.booking_id')
                           ->where('bookings.date_to', '<=', $date_from)
                            ->where('bookings.date_from', '>=', $date_to)
                            ->where('bookings.deleted_at', null); 
                })
                        ->where('stations.id', '=', $station_id)
                        ->where('stocks.article_id', '=', $this->id)
                        ->where('bookings.id', null)
                        ->where('stocks.available', true)
                        ->where('stocks.deleted_at', null);
                        
        
    }

Please or to participate in this conversation.