How do I select a value from different tables in Query Builder Laravel

So i’m trying to get “promo_type” value from mysql table to excel. However i need to use another table in order to get the value of this “promo_type”.

So this is the main table booking, which contains the value of the promo code. Like shown below a user has a promo code of “1000”. Booking Table

Now in this other table, this is where the promo codes are made and saved.

Promo Code table

But i need to access the “promo_type” variable to be able to export it to excel. How can i do this? I was thinking of seleting the value of the promo code from the booking table and comparing it to the promo code table and then seleting the “promo_type” column. But i have no idea how to translate this to query builder syntax.

This is the code im using to export the data to excel. So i need to return it in a suitable way in order to export the value to excel.

public function collection()
{

    return Booking::select(
        'id',
        'place_id',
        'payer_name',
        'user_fullname',
        'user_email',
        'user_phone',
        'user_no_of_guest',
        'user_no_of_babies',
        'user_checkin',
        'user_checkout',
        'is_approved',
        'user_promo',
        'user_payment_type',
        'user_booking_tracking_id',
        'created_at',
        Booking::raw('(created_at +  INTERVAL 2 HOUR) AS created_at'),
        'paid_amount'
    )
        ->whereRaw('"' . $this->date . '" between `user_checkin` and `user_checkout`')
        ->get();
}



public function headings(): array
{
    return [
        'ID',
        'Place ID',
        'Payer Name',
        'Full Name',
        'Email',
        'Phone',
        'Number of adults',
        'Number of babies',
        'Arrival Time',
        'Checkout Time',
        'Approval',
        'Promo',
        'Payment Type',
        'Tracking Id',
        'Created At',
        'Total Amount',
    ];
}

Answer

I would not use select for this, i would always use Eloquent approach. This is way more pragmatic and the expected way to do it.

You need to have a relationship to be able to do this, add PromoCode relationship to the Booking.php class.

class Booking
{
    public function promoCode()
    {
        return $this->belongsTo(PromoCode::class, 'user_promo', 'promocode');
    }
}

Now you can use the functions from Laravel Excel to query and map your data correctly.

public function collection()
{
    return Booking::whereRaw('"' . $this->date . + '" between `user_checkin` and `user_checkout`')->get();
}

public function map($booking): array
{
    return [
        // your other fields
        $booking->promoCode->promo_type,
    ];
}