CODEWITHSUNDEEP
laravel-7
Komail Fayazbakhsh
Komail Fayazbakhsh

Reputation: 384

get data from intermediate table in laravel

i have 2 model(SaleInvoice and Product) with many to many relation

in SaleInvoice model :

public function products()
    {
        return $this->belongsToMany(Product::class, 'sale_invoice_product', 'saleInvoice_id', 'product_id')->withPivot('count');
    }

in Product Model:

    public function saleInvoices()
    {
        return $this->belongsToMany(SaleInvoice::class, 'sale_invoice_product', 'product_id', 'saleInvoice_id');
    }

this is the example of data that recorded in sale_invoice_product table(intermediate table)

id | saleInvoiceId | product_id | count
1  |      1500     |      1     | 3
2  |      1500     |      3     | 2
3  |      1500     |      4     | 4
4  |      1501     |      1     | 1
5  |      1501     |      4     | 1

how can i access to data of product and sale invoice from this table like below(in json mode for api request)

product_id | product_name | count | saleInvoice | date
     1             LG         3        1500        2020-05-12
     3             SONY       2        1500        2020-05-13
     4             OT         4        1500        2020-05-17
     1             LG         1        1501        2020-05-19
     4             OT         1        1501        2020-05-22

i want to return a json file in SaleInvoiceController with top format

Upvotes: 0

Views: 410

Answers (2)

Ahmed Ghonim
Ahmed Ghonim

Reputation: 4780

You can try one of these methods

  • Building a model for sale_invoice_product table with relations to SaleInvoice and Product. Then manually construct the JSON in your controller
  • Build an SQL View and a Model for it

Solution 1: Building a model to the intermediate table and manually constructing the JSON

Let's say you built a model called SaleInvoiceProduct that has product() relation to the Products table and saleInvoice() relation to the SaleInvoices table. In your controller you can do this

$resultInvoiceProducts = [];
$allSaleInvoiceProducts = SaleInvoiceProduct::all();
foreach ($allSaleInvoiceProducts as oneSaleInvoiceProduct) {
     $tempSaleInvoiceProduct = new stdClass();
     $tempSaleInvoiceProduct->product_id = oneSaleInvoiceProduct->product_id;
     $tempSaleInvoiceProduct->product_name = oneSaleInvoiceProduct->product->name;
     $tempSaleInvoiceProduct->saleInvoiceId = oneSaleInvoiceProduct->saleInvoiceId;
     $tempSaleInvoiceProduct->data = oneSaleInvoiceProduct->saleInvoice->date;
     array_push($resultInvoiceProducts, $tempSaleInvoiceProduct);
}

Solution 2: Using SQL Views

You can create an SQL View that uses Joins to construct the data you need

   DROP VIEW IF EXISTS vSaleInvoiceProduct;
   CREATE VIEW vSaleInvoiceProduct AS
   SELECT sp.product_id,
          sp.saleInvoiceId,
          sp.`count`,
          p.product_name,
          s.`date`
   FROM SaleInvoiceProduct sp
   LEFT JOIN SaleInvoices s on sp.saleInvoiceId = s.saleInvoiceId
   LEFT JOIN Products p on sp.product_id = p.product_id

Then you can create a Laravel model for this View just like you would do for any table, call the ::all() method on it and directly return the results with json()

Upvotes: 1

Mehrdad Saami
Mehrdad Saami

Reputation: 131

Your work was good, Just enough make a API resource for this model and send attributes as you want, For accessing to pivot table you could use $product->pivot->count.

Upvotes: 2

Related Questions