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);
}
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);
}
Top comments (0)