atfinho
6 months ago

multi level relationships dbquery

Posted 6 months ago by atfinho

Hey guys , I've an issue that I couldn't access to multi level relationships in dbquery

I have already this query that works properly the idea is I have one table called " car_maintenance" that is related to " cars" Table

car hasMany car_maintenance

I want to display sum of a field inside " car_maintenance" table called " total_price" for every car_id

                                        $getBookingDays= DB::table('bookings')
                                         ->groupBy('bookings.car_id')
                                         ->groupBy('cars.model')
                                         ->groupBy('cars.daily_price')
                                         ->groupBy('car_manufacturers.name')
                                         ->join('cars','bookings.car_id','cars.id')
                                         ->join('car_manufacturers','cars.car_manufacturer_id','car_manufacturers.id')
                                       ->select([
                                        'cars.model','car_manufacturers.name',
                                        DB::raw('sum(DATEDIFF(bookings.to,bookings.from)) as bookingDays'),
                                        DB::raw('365-sum(DATEDIFF(bookings.to,bookings.from)) as parkingStay'),
                                        DB::raw('cars.daily_price*sum(DATEDIFF(bookings.to,bookings.from)) as gains'),
                                        ])->get();

whenever I add the line below , I get a double value for "booking days "

 ->leftJoin('car_maintenance','car_maintenance.car_id','=','cars.id')

I hope you're getting what I mean..

thank you in advance

Please sign in or create an account to participate in this conversation.