Clash Royale CLAN TAG#URR8PPP

Laravel Display table product if not exist on quantity table

In PHP-MySQL I can create this query...

$sql = "SELECT * FROM product";
$result = $conn->query($,con, $sql);
while($row =mysql_fetch_array($result))
$sqlquantity = "SELECT * FROM quantity where branchid='4' and productid='$row['productid']'";
$resultquantity = $conn->query($,con, $sqlquantity);
if (mysqli_num_rows($resultquantity) == 0)
echo $row['productname']. "not available in branch"

else
echo $row['productname']. "available in branch"




But how can I do this using Laravel?

I have 3 tables

+-----------------+ +-----------------+ +----------------+
| product table | | quantity table | | branch table |
+-----------------+ +-----------------+ +----------------+
| id | | productid | | id |
| productname | | branchid | | branchname |
+-----------------+ | quantity | +----------------+
+-----------------+


My problem is that I am trying to create a model, view and controller where I can display all the products that is not available yet on each branch base on the quantity table. Can anyone help?

model

view

controller

products

branch

quantity

Product model

public function quantity()

return $this->hasMany('AppQuantity', 'productid', 'id');



Quantity model

public function product()

return $this->belongsTo('AppProduct', 'productid', 'id');



Branch Model

public function products()

return $this->hasMany('AppQuantity', 'branchid', 'id');



What I am trying to create is that if I view the branch I can add those product quantity table if the product does not exist.

branch

product

quantity

product

2 Answers
2

You could also try this one...

Please check many-to-many relationship at the Official Docs for better explanation. .

You don't need to create a Quantity model because it serves as a pivot or joining table (not an entity) between Product and Branch model. Since you have custom pivot table name, which is quantity you need to pass it to the 2nd argument or else Eloquent will automatically create a table name for you which is branch_product (alphabetical). 3rd and 4th arguments are foreign keys of current model and the joining model respectively.

Quantity

pivot

Product

Branch

quantity

branch_product

Product model

public function branches()

return $this->belongsToMany('AppBranch', 'quantity', 'productid', 'branchid')
->withPivot('quantity'); //additional pivot column



Branch model

public function products()

return $this->belongsToMany('AppProduct', 'quantity', 'branchid', 'productid')
->withPivot('quantity');



Product Controller

$products = Product::all();

if($products)
$x = 0;

foreach ($products as $prod)

$products[$x] = $prod->branches()
// ->wherePivot('productid', '=', $prod->id)
->wherePivot('branchid', '=', 4)
->wherePivot('quantity', '=', 0)
->get();
$x++;



return response(['products' => $products],200);
//get all the products in branch 4 with quantity = 0


Then you could do the conditional if to determine if it is available or not.

if

You could use whereHas() method like :

whereHas()

Product::whereHas( 'Quantity', function($sQuery)
$sQuery->where('branchid', 4);
)->get();


By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.