Debug School

rakesh kumar
rakesh kumar

Posted on

How to get data from multiple table in laravel

Joins:

Combines data from multiple tables (addvechicles, users, shops, bookings, and addvehical_byadmins) using LEFT JOIN.
Ensures that records from addvechicles are included even if there's no matching data in the joined tables.
Filters:

Filters rows where the vendor_email matches the given $email.
Aggregation:

Uses DB::raw('MAX(...)') to fetch the maximum value for each field (useful for fields that might have duplicate data in the joined results).
Grouping:

           public function getVehicleData(Request $request)
                    {
                    $input = $request->all(); 
                    $email = $input['email'];                  
                   $getvehicle_vendor= addvechicles::where('vendor_email',$email)->first();
                   $vendor_id=$getvehicle_vendor->vender_ID;                   
                        $getvehicle_data = DB::table('addvechicles')
                        ->leftJoin('users', 'addvechicles.vendor_email', '=', 'users.email')
                        ->leftJoin('shops', 'addvechicles.shop_id', '=', 'shops.id')                       
                        ->leftJoin('bookings', 'addvechicles.vender_ID', '=', 'bookings.user_id')
                        ->leftJoin('addvehical_byadmins', 'addvechicles.vehical_id', '=', 'addvehical_byadmins.id')
                        ->where('addvechicles.vendor_email', $email) // Add condition
                        ->select(
                            'addvechicles.id',
                            DB::raw('MAX(addvechicles.vender_ID) as vender_ID'),
                            DB::raw('MAX(addvechicles.shop_id) as shop_id'),
                            DB::raw('MAX(addvechicles.price) as price'),
                            DB::raw('MAX(addvechicles.id) as vehical_id'),                      
                            DB::raw('MAX(addvechicles.numbers_of_vechile) as numbers_of_vechile'),
                            DB::raw('MAX(addvechicles.vendor_email) as vendor_email'),
                            DB::raw('MAX(addvechicles.rc_number_of_vechile) as rc_number_of_vechile'),
                            DB::raw('MAX(addvechicles.insurence) as insurence'),
                            DB::raw('MAX(addvechicles.pollution) as pollution'),
                            DB::raw('MAX(addvechicles.rc_ducoment) as rc_ducoment'),
                            DB::raw('MAX(addvechicles.vechicle_image) as vechicle_image'),
                            DB::raw('MAX(addvechicles.status) as status'),
                            DB::raw('MAX(addvechicles.publish) as publish'),
                            DB::raw('MAX(addvechicles.created_at) as created_at'),
                            DB::raw('MAX(addvechicles.updated_at) as updated_at'),
                            DB::raw('MAX(addvehical_byadmins.vehical) as vehical'),
                            DB::raw('MAX(addvehical_byadmins.brand) as brand'),
                            DB::raw('MAX(addvehical_byadmins.model) as model'),                      
                            DB::raw('MAX(shops.location) as location'),
                            DB::raw('MAX(users.state) as state'),
                            DB::raw('MAX(users.address) as address'),
                            DB::raw('MAX(users.city) as city'),
                            DB::raw('MAX(users.number) as number'),
                            DB::raw('MAX(bookings.id) as booking_id')
                        )
                        ->groupBy('addvechicles.id')
                        ->get();
                   Log::info('getvehicle_data data:', $request->all()); 
                   log::info($getvehicle_data); 

                    $response = [
                        'success' => true,
                        'data' => addvechiclesResource::collection($getvehicle_data),        
                        'message' => 'getvehicle_data retrieved successfully.',
                    ];
                    return response()->json($response, 200);
                  }
Enter fullscreen mode Exit fullscreen mode

Groups results by addvechicles.id.

Another way

public function getVehicleData(Request $request)
{
    $email = $request->input('email');

    // Define the fields and their aggregation methods
    $fields = [
        'addvechicles.vender_ID' => 'vender_ID',
        'addvechicles.shop_id' => 'shop_id',
        'addvechicles.price' => 'price',
        'addvechicles.numbers_of_vechile' => 'numbers_of_vechile',
        'addvechicles.rc_number_of_vechile' => 'rc_number_of_vechile',
        'addvechicles.insurence' => 'insurence',
        'addvechicles.pollution' => 'pollution',
        'addvechicles.rc_ducoment' => 'rc_ducoment',
        'addvechicles.vechicle_image' => 'vechicle_image',
        'addvechicles.status' => 'status',
        'addvechicles.publish' => 'publish',
        'addvehical_byadmins.vehical' => 'vehical',
        'addvehical_byadmins.brand' => 'brand',
        'addvehical_byadmins.model' => 'model',
        'shops.location' => 'location',
        'users.state' => 'state',
        'users.address' => 'address',
        'users.city' => 'city',
        'users.number' => 'number',
        'bookings.id' => 'booking_id',
    ];

    // Add `MAX` aggregation for each field
    $selectFields = ['addvechicles.id'];
    foreach ($fields as $field => $alias) {
        $selectFields[] = DB::raw("MAX($field) as $alias");
    }

    // Execute the query
    $getvehicle_data = DB::table('addvechicles')
        ->leftJoin('users', 'addvechicles.vendor_email', '=', 'users.email')
        ->leftJoin('shops', 'addvechicles.shop_id', '=', 'shops.id')
        ->leftJoin('bookings', 'addvechicles.vender_ID', '=', 'bookings.user_id')
        ->leftJoin('addvehical_byadmins', 'addvechicles.vehical_id', '=', 'addvehical_byadmins.id')
        ->where('addvechicles.vendor_email', $email)
        ->select($selectFields)
        ->groupBy('addvechicles.id')
        ->get();

    Log::info('Request Data:', ['email' => $email]);
    Log::info('Vehicle Data:', $getvehicle_data);

    return response()->json([
        'success' => true,
        'data' => addvechiclesResource::collection($getvehicle_data),
        'message' => 'getvehicle_data retrieved successfully.',
    ], 200);
}
Enter fullscreen mode Exit fullscreen mode

Top comments (0)