Level 5
@snapey sorry for tagging you.
Kindly suggest me an idea
Jun 18, 2019
29
Level 5
Get data from 3 tables with relationship laravel
Guys i have a problem in my hotel management system project.
i have a product_type table (parent table) with columns
id
type_name
product table (child table)
id
product_type_id(fk)
product_name
order_table with columns
id
product_id(fk)
qty
rate
total
here whats my doubt is i need to fetch records from order_table. but the record must be grouped by the product_type with the total . how can i achieve it.
the product_type and product has relationship
this is my product model
public function product_type()
{
return $this->belongsTo('App\ProductType', 'product_type_id');
}
this is my product_type model
public function product()
{
return $this->hasMany('App\Product', 'product_type_id');
}
this is my query,
$order = Order::where ('hotel_id',Auth::user()->hotel_id)->get();
i need to fetch records based the product_type wise. it mus be grouped based on product_type.
Kindly some one help please.
Level 122
its probably a good case for a join, orders to product. You could also join product_type but thats not strictly necessary
In your thinking change to 'group by product_type_id' then you dont need to worry about the extra table
Could you swap your query to get all products with orders ?
Level 5
@snapey thank you soo much for your response..
i tried with joins,
see this
// in this below query what iam doing is fetching records from from order table where payment status of purchaseToStore must be '1' and then i gave the join query..
$all = Order::where('hotel_id', Auth::user()->hotel_id)->
whereHas('PurchaseToStore', function ($query) use ($request)
{
$query->latest()->where('payment_status', 1)->whereBetween('payment_date', [$request->from_date, $request->to_date]);
})
->leftJoin('products', 'specific_purchase_bills.product_id', '=', 'products.id') ->
leftJoin('product_types', 'products.product_type_id', '=', 'product_types.id')->get();
//after getting the records $all from order table, i tried to add the total amount with product_type_id. after that i added the overall total.
$pro_cat = [];
$total_purchase = 0;
foreach ($all as $key => $al)
{
$pro_cat[$al->product_type_id]["total_amount"] = 0;
}
foreach ($all as $key => $al) {
$pro_cat[$al->product_type_id]["total_amount"] += $al->total_amount;
$total_purchase = 0;
foreach ($pro_cat as $item) {
$total_purchase += $item['total_amount'];
}
}
when i give dd($all); the result is
Collection {#681 ▼
#items: array:47 [▼
0 => SpecificPurchaseBill {#682 ▼
#fillable: array:16 [▶]
#connection: "mysql"
#table: null
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
#perPage: 15
+exists: true
+wasRecentlyCreated: false
#attributes: array:25 [▶]
#original: array:25 [▼
"id" => "12"
"hotel_id" => "1"
"login_user_id" => "1"
"purchase_order_id" => "1377"
"bill_no_id" => "359"
"d_date" => "2019-06-01"
"product_id" => "228"
"product_type" => "VEGETABLES"
"qty" => "13"
"total_rate" => "195"
"rate_per_kg" => "15"
"gst_percent" => "0"
"gst_amt" => "0"
"disc_percent" => "0"
"disc_amt" => "0"
"total_amount" => "195"
"created_at" => "2019-03-21 11:58:40"
"updated_at" => "2019-03-21 11:58:40"
"product_name" => "KEERAI KATTU"
"product_type_id" => "12"
"product_unit" => "kg"
"product_minimum_qty" => "50"
"product_rate" => "7"
"product_rate_type" => "changeable"
"note" => null
]
#casts: []
#dates: []
#dateFormat: null
#appends: []
#events: []
#observables: []
#relations: []
and when i dd($pro_cat); the result is
array:14 [▼
12 => array:1 [▼
"total_amount" => 6295
]
26 => array:1 [▼
"total_amount" => 1250
]
13 => array:1 [▼
"total_amount" => 340
]
27 => array:1 [▼
"total_amount" => 1700
]
14 => array:1 [▼
"total_amount" => 315
]
37 => array:1 [▼
"total_amount" => 4056
]
11 => array:1 [▶]
32 => array:1 [▶]
9 => array:1 [▶]
29 => array:1 [▶]
2 => array:1 [▶]
1 => array:1 [▶]
31 => array:1 [▶]
20 => array:1 [▶]
]
the above coding working fine. what i expect i got the result.
but the problem is, the product_type_id is displayed in the view file.
this is my view file
@foreach($pro_cat as $productType => $details)
@isset($details['total_amount'])
<tr>
<td>{{ $productType }}</td>
<td></td>
<td>{{ $details['total_amount'] }} </td>
<td>{{ number_format(($details['total_amount']/ $total_income) * 100, 2) }}
%</td>
</tr>
@endisset
@endforeach
Refer the screenshot for my output image: https://imgur.com/fR4sqeS
see the product_type_id is displayed. i need the product name.. how to do this??
and what i answered is what i did?? else i made the code tooo complicated??
Level 5
@snapey Kindly give me an idea please...
Level 35
Why not use the GroupBy functionality of Eloquent, assuming your Order model has a product relationship and your Product model a product_type relationship.
Order::with(['product.product_type' => function($query){
$query->groupBy('id');
}])->get();
Level 122
add a select and specify just the columns you need in your final dataset and reference the table name and column eg product_type.type_name
Also, i noticed having created the join, you did not group the results?
Level 5
@snapey thank you for your response. but it is grouping by ts product_type_id
actually i can get everthing. just i need to display the name of product-type instead product_type_id..
if i change
my view into
@foreach($pro_cat as $productType => $details)
@isset($details['total_amount'])
<tr>
<td>{{ $productType->product_type->product->type }}</td> //as per my product and product_type model
<td></td>
<td>{{ $details['total_amount'] }} </td>
<td>{{ number_format(($details['total_amount']/ $total_income) * 100, 2) }}
%</td>
</tr>
@endisset
@endforeach
but it shows error, undefined error product_type
i can get the product_type_id by using the query
$all = Order::where('hotel_id', Auth::user()->hotel_id)->
whereHas('PurchaseToStore', function ($query) use ($request)
{
$query->latest()->where('payment_status', 1)->whereBetween('payment_date', [$request->from_date, $request->to_date]);
})
->leftJoin('products', 'order.product_id', '=', 'products.id') ->
leftJoin('product_types', 'products.product_type_id', '=', 'product_types.id')->get();
but the product_type name???
Level 122
you joined the tables, so why would the name not be present?
Level 5
@snapey that only i cant understand..
actually when i give dd($all) records from order table and records from product table are displayed, but records from product_type table is not displayed..
let me shrink the query,
$all = Order::leftJoin('products', 'orders.product_id', '=', 'products.id') ->
leftJoin('product_types', 'products.product_type_id', '=', 'product_types.id')->get();
the above is the actual query
these are my tables orders, products and product_types
now the dd() displays the records of order and product table. not the product_type table.
whats the problem.. kindly suggest
Level 5
@mthomas thank you for your response..
sorry i didnt notice your response.. let me try it and reply
Level 5
i tried
$all=Order::with(['products.product_types' => function($query){
$query->groupBy('id');
}])->get();
dd($all);
it shows an error
Call to undefined relationship [products] on model [App\Order].
Level 5
what hapen??
Level 1
Order::with('product.product_type')->get()->groupBy('product.product_type.type_name');
Level 35
@ABDULBAZITH - As mentioned in my earlier comment. Assuming you have created the relationship on your order model. The comments shows you don't have the following relationship:
// In your order model
public function products()
{
return $this->hasMany(Product::class);
}
// In your product model
// Or type is even better but then you need to update the eloquent query accordingly
public function product_type()
{
return $this->belongsTo(ProductType::class);
}
Level 5
can u suggest me based on my code.
mine is working well. the problem is just the
product_type_name is not coming. just it getting
product_type_id.
i will explain my code,
$all = Order::leftJoin('products', 'orders.product_id', '=', 'products.id') ->
leftJoin('product_types', 'products.product_type_id', '=', 'product_types.id')->get();
it is fetching records what i expected.
dd($all).
Collection {#685 ▼
#items: array:47 [▼
0 => Order{#686 ▼
#fillable: array:16 [▶]
#connection: "mysql"
#table: null
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
#perPage: 15
+exists: true
+wasRecentlyCreated: false
#attributes: array:25 [▼
"id" => "12"
"hotel_id" => "1"
"login_user_id" => "1"
"purchase_order_id" => "1377"
"bill_no_id" => "359"
"d_date" => "2019-06-01"
"product_id" => "228"
"product_type" => "VEGETABLES"
"qty" => "13"
"total_rate" => "195"
"rate_per_kg" => "15"
"gst_percent" => "0"
"gst_amt" => "0"
"disc_percent" => "0"
"disc_amt" => "0"
"total_amount" => "195"
"created_at" => "2019-03-21 11:58:40"
"updated_at" => "2019-03-21 11:58:40"
"product_name" => "KEERAI KATTU"
"product_type_id" => "12"
"product_unit" => "kg"
"product_minimum_qty" => "50"
"product_rate" => "7"
"product_rate_type" => "changeable"
"note" => null
]
#original: array:25 [▶]
#casts: []
#dates: []
#dateFormat: null
#appends: []
#events: []
#observables: []
#relations: []
in the above code just i picked the values and mention below
// these are values from Order table
"id" => "12"
"hotel_id" => "1"
"login_user_id" => "1"
"purchase_order_id" => "1377"
"bill_no_id" => "359"
"d_date" => "2019-06-01"
"product_id" => "228"
"product_type" => "VEGETABLES"
"qty" => "13"
"total_rate" => "195"
"rate_per_kg" => "15"
"gst_percent" => "0"
"gst_amt" => "0"
"disc_percent" => "0"
"disc_amt" => "0"
"total_amount" => "195"
"created_at" => "2019-03-21 11:58:40"
"updated_at" => "2019-03-21 11:58:40"
//these are values from products table
"product_name" => "KEERAI KATTU"
"product_type_id" => "12"
"product_unit" => "kg"
"product_minimum_qty" => "50"
"product_rate" => "7"
"product_rate_type" => "changeable"
"note" => null
so based on the above product_type_id, the values are added.
i used these below codes for adding based on product_type. why i used these because, i had a doubt in my old project. so someone in this forum suggested these codes, but that old project need such type of code. i copy pasted the same code and used here. i think these is too complicated code. how i can do this??
i followed this link only: https://laracasts.com/discuss/channels/laravel/need-a-logical-help-in-viewblade-file
$pro_cat = [];
$total_purchase = 0;
foreach ($all as $key => $al)
{
$pro_cat[$al->product_type_id]["total_amount"] = 0;
}
foreach ($all as $key => $al) {
$pro_cat[$al->product_type_id]["total_amount"] += $al->total_amount;
$total_purchase = 0;
foreach ($pro_cat as $item) {
$total_purchase += $item['total_amount'];
}
}
i was so confused about the array structure of laravel
i know about get() and paginate(). simple foreach is enough.
but when it comes to groupby, sum or other something the values are inside the array structre. i can get the values by checking it in dd(); but i dont know how to displya it in blade file
thats my biggest problem. kindly suggest a solution for this question and also share me some links of array structure of laravel in blade file.
in this question everything is clear. just i need the product_type name instead of id.
Kindly reply pleaseee
Level 122
you have product_type of 12?
and product_types table has type_name column?
can you check the generated sql?
Level 5
@snapey yes yes i have product_type "12"
and the product_type table have columns
id
product_type (this is the product_type_name)
the problem is product_type records are not fetched. y?
Level 122
ok, so this is different to the very first posting.
you have two tables in the join with the same column name - only one can be present in the collection
Level 5
@snapey no two tables have same column name. see
product_types table (parent table) with columns
id
type_name( for naming convention i changed it like this. but originally this column name is product_type)
products table (child table)
id
product_name
product_type_id (fk)
product_unit
product_minimum_qty
product_rate
product_rate_type
note
orders table with columns
id
hotel_id
login_user_id
purchase_order_id
bill_no_id
d_date
product_id (fk from products table)
qty
total_rate
rate_per_kg
gst_percent
gst_amt
disc_percent
disc_amt
total_amount
created_at
updated_at
these are my columns. then whats the problem. i have mentioned all the columns.
my product_type model
public function product()
{
return $this->hasMany('App\Product', 'product_type_id');
}
my product model
public function product_type()
{
return $this->belongsTo('App\ProductType', 'product_type_id');
}
my order model
public function product()
{
return $this->belongsTo('App\Product', 'product_id');
}
so what can i do??
let me ask another doubt.
if u have the same tables with same columns, means, what you would query to add the same product_type items??
and how will u display it in the view file??
kindly please reply.. dont hesitate
Level 122
if you use join then the collection cannot contain two columns with the same name
In these cases, you must use select() and then give columns aliases
Level 5
@snapey but i dont have two columns with same name..
once again check my columns as i mentioned above.. where there is two columns with same name???
Level 122
but you wrote
and the product_type table have columns
id
product_type (this is the product_type_name)
Level 5
@snapey yes.
i have the product_type table with columns
id and
product_type (this was the name of product type) // this is the only column. there is no other column. then how come it was two times
here where the names are duplicated??
Level 122
look. make sure there are no duplicated column names and then run the query again, and dump the results.
I cannot see any reason why your product type name would not show other than duplicate column name.
Level 5
@snapey ok i understood.
can you suggest me one idea please.
if u havesame columns with same fields means what your query will be.
just kindly give me a sql query which combines three tables and sum based on the product_type.
and how to display it in view file. pleaseeee
Level 122
I mocked up some tables, and models, then tested with the following;
Order::select('product_types.type_name')
->selectRaw("sum(orders.total) as totalForType")
->join('products','orders.product_id','products.id')
->join('product_types', 'product_type_id','product_types.id')
->groupBy('type_name')
->get();
it produces
[
{
"type_name": "SERVICES",
"totalForType": "1000"
},
{
"type_name": "VEGETABLES",
"totalForType": "1025"
}
]
Any help?
1 like
Level 5
@snapey thank you sooooo much.
shall i say one thing you are really great..!!!!
exactly what i expected i got it..
thank you thank you soooooooooooo muchhhhh.....
after the query what i did is in my view.blade file
@foreach($all as $productType => $details)
@isset($details['totalForType'])
<tr>
<td>{{ $details->product_type }}</td>
<td></td>
<td>{{ $details['totalForType'] }} </td>
<td>{{ number_format(($details['totalForType']/ $total_income) * 100, 2) }}
%</td>
</tr>
@endisset
@endforeach
it worked perfectly..
thank you thank youu...
without any hesitation you replied me. even in your last reply also u mentioned any help? really thats great..
Level 122
thats great.
Level 5
@snapey i know this is wrong to tag you.
but i had asked another doubt in this same format. u might understood my this problem, so it is easy for you to understand that question also thats why iam tagging you sorry for this
kindly possible refer this link:
Please or to participate in this conversation.