Level 74
Can you give an example of the data you have in the tables, and show the expected result?
The description you have given just makes me confused as to what you are trying to do.
Aug 18, 2023
12
Level 1
Eloquent Subquery with $this->id?
Hello Community,
I can't figure out how to achieve my goal:
My orders always have a main user and can have attached Users. All users have order products, which are connected to users and the order itself via an id for each.
Now I want to load all orders from a specific main user: $userID, where the payment has status 2,5 or 6, all order products for the main user and all attached users with their order products, where the order_id inside the orderProduct is the same as the current query-order_id.
It always only loads the order specific attached users but unfortunately all of their OrderProducts (also those who are connected to an earlier order).
Attached Users are connected to orders via a pivot table,
Any Idea how to fix this? Is there a possibility to use the "current order_id" which is queried inside the "order product filter subquery"?
$orders = Order::where(function ($query) use ($userID) {
return $query->where('store_user_id', $userID);
})->whereHas('payments', function ($query) {
return $query->where('payment_status_id', 2)
->orWhere("payment_status_id",5)
->orWhere("payment_status_id",6);
})->with(['orderProducts',
'payments',
// load attached users with order products, where the order product id is the same as the current order_id
'attachedUsersPivot' => function($query) {
$query->with('orderProducts')
->whereHas('orderProducts', function ($subQuery) {
$subQuery->whereColumn('order_products.order_id', 'auser_order.order_id');
});
},
'discounts',
'invoices',
'orderStatus',
'shippingStatus'])
->orderBy('id', 'DESC')
->get();```
## Inside the Attached User Model:
`public function orderProducts(): HasMany
{
return $this->hasMany(OrderProduct::class,"attached_user_id");
}`
In the end I want to pass the `$orders` variable to my view and iterate over each attachedUser and also retrieve his items via $attachedUser->orderProducts
Level 1
Sure:
AttachedUser To Order Pivot table:
id, created_at, updated_at, order_id, attached_user_id
'10', NULL, NULL, '10', '2'
'11', NULL, NULL, '10', '3'
OrderProducts table:
id, comment, quantity, order_id, attached_user_id, product_id
'23', NULL, '1', '10', '2', '5'
'24', NULL, '1', '10', '3', '5'
'25', NULL, '1', '10', '3', '5'
output ddd($orders)
Illuminate\Database\Eloquent\Collection {#1956 ▼
#items: array:4 [▼
0 => App\Models\Order {#1828 ▼
#connection: "mysql"
#table: "orders"
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
+preventsLazyLoading: true
#perPage: 15
+exists: true
+wasRecentlyCreated: false
#escapeWhenCastingToString: false
#attributes: array:17 [▶]
#original: array:17 [▶]
#changes: []
#casts: []
#classCastCache: []
#attributeCastCache: []
#dates: []
#dateFormat: null
#appends: []
#dispatchesEvents: []
#observables: []
#relations: array:7 [▼
"orderProducts" => Illuminate\Database\Eloquent\Collection {#1813 ▶}
"payments" => App\Models\Payment {#1858 ▶}
"attachedUsersPivot" => Illuminate\Database\Eloquent\Collection {#1833 ▼
#items: array:2 [▼
0 => App\Models\AttachedUser {#1857 ▼
#connection: "mysql"
#table: "attached_users"
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
+preventsLazyLoading: true
#perPage: 15
+exists: true
+wasRecentlyCreated: false
#escapeWhenCastingToString: false
#attributes: array:7 [▶]
#original: array:9 [▶]
#changes: []
#casts: []
#classCastCache: []
#attributeCastCache: []
#dates: []
#dateFormat: null
#appends: []
#dispatchesEvents: []
#observables: []
#relations: array:2 [▼
"pivot" => Illuminate\Database\Eloquent\Relations\Pivot {#1866 ▶}
"orderProducts" => Illuminate\Database\Eloquent\Collection {#1876 ▼
#items: array:5 [▼
0 => App\Models\OrderProduct {#1928 ▶}
1 => App\Models\OrderProduct {#1920 ▶}
2 => App\Models\OrderProduct {#1918 ▶}
3 => App\Models\OrderProduct {#1919 ▼
#connection: "mysql"
#table: "order_products"
#primaryKey: "id"
#keyType: "int"
+incrementing: true
#with: []
#withCount: []
+preventsLazyLoading: true
#perPage: 15
+exists: true
+wasRecentlyCreated: false
#escapeWhenCastingToString: false
#attributes: array:14 [▼
"id" => 22
"comment" => null
"quantity" => 1
"order_id" => 10
"attached_user_id" => 2
"product_id" => 5
it loads all 5 orderProducts although OrderProduct 0, 1 and 2 aren't connected to the given order.
I just want to have the order specific order products of each user
I also reformatted my query for better readability
Level 14
@dogma you structure seems wrong, how can an attached user load order products by itself without a order reference? I assume an attached user, like any other user can have many orders. So you would have to load attached_user -> orders -> products, but still this should be the same as order -> products no?
Level 1
@krisi_gjika I thought you might be able to access the currently queried order_id and compare it to the given orderProducts Id
No! Order->products only loads the "main users product"
public function orderProducts(): HasMany
{
return $this->hasMany(OrderProduct::class)->where("attached_user_id",'=',null);
}
Yes, attached Users can have many orders however I iterate through the orders and only load those users who are attached to the specific order via the pivot table.
Level 74
@dogma So let me get this straight.
- A user can have many orders
- An order can have many products
So the order has a user_id to connect it to the user. The order and product is then connected via a pivot table.
Am I correct so far?
If so, you are over complicating things.
A order belongs to one user, then you use the foreign_key user_id in the orders table to connect the order and user together.
A order can have multiple products, then you use a pivot to connect the products to the order, that pivot doesn't need the user_id, since that is connected to the order arleady.
I suggest giving these two a read before continuing.
Level 14
@dogma and the issue is you receive products belonging to other orders also, I assume? From your query try something like this?
Order::query()
->where('store_user_id', 10)
->whereHas('payments', function ($query) {
return $query->whereIn('payment_status_id', [2, 5, 6]);
})
->with([
'orderProducts',
'payments',
'attachedUsersPivot' => function($query) {
$query->with('orderProducts', function ($subQuery) {
$subQuery->whereColumn('order_products.order_id', 'auser_order.order_id');
});
},
'discounts',
'invoices',
'orderStatus',
'shippingStatus'
])
->orderBy('id', 'DESC')
->get();
Level 1
@krisi_gjika Unfortunately there is no output to this query for me, without any errors
Level 1
@Tray2 Thank you! I will take a look, however my product has to be connected to specific persons it is neccessary to differ between the "Main User" with his Products and the "Attached Users" with theirs. Both are connected to an order via the order_id and can be part of multiple orders.
Level 14
@dogma the error being? I only moved your whereHas condition
Level 1
@krisi_gjika There is none, just an empty collection
Level 74
@dogma So you got some kind of franchise relation, if so it should not be stored in the order.
The order belongs to a user and store, the store can then belong to what ever else, the products belongs to a store. Any order that has products, already belongs to a store, no need to add extra relations.
But I have no idea what you are after and why you are trying to make it so complex.
Level 1
@Tray2 My product is connected to a person. Not like an online shop order where there is one cart for everything I need to attach my cart items to specific persons. Thats why there is a main user and attached users. Which both have products. Attached users are connected to orders as well as a main user.
Right now I am looking for a solution to load all orders with the main user and all attached users who are connected to the specific order instance and furthermore the products which had been attached to the attached users in this specific order.
Since there is no way to reference to the current queryied order id I believe there might be a solution like:
-
Load all orders with all users that are attached to the order
-
Load all products of all attached users and compare the order_id with the given order's id.
-
works, however I am looking for 2.
Please or to participate in this conversation.