phpmyadmin Question posted in
The official documentation can be found here.

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

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

enter image description here

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

Promo Code table

enter image description here

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',
    ];
}
Tags:

2

Answers


  1. 0

    I think there is something wrong with your table relation. Based your concept, instead of using promo_code column, you should add a promo_code_id column that have reference to id in Promo Code table (foreign key).

    In your model, you can utilize an Eloquent hasOne() relationship to Booking model such as

    class Booking extends Model
{
    public function promoCode()
    {
        return $this->hasOne(PromoCode::class, 'id', 'promo_code_id');
    }

    ...

}

    PromoCode is a model for the Promo Code table.

    Then, you can get the Promo Code data by using Eloquent relation

    public function foo()
{
    $promoCode = Booking::first()->promoCode;
}
    Login or Signup to reply.
  2. 0

    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,
    ];
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search