How to select statement for showing data

Question

i have table in mysql like this

-----------------------------
|id|warehouse_id|item_id|qty|
---|------------|-------|---|
|1 | 1          | 1     |10 |
|2 | 1          | 2     |23 |
|3 | 2          | 1     |45 |
|4 | 2          | 2     |66 |
|5 | 1          | 3     |44 |

How using select statement to show table what i want like this

------------------------------------------
|id|item_id|qty warehouse 1|qty warehouse 2
---|-------|---------------|-------------
|1 | 1     | 10            |45         |
|2 | 2     | 23            |66         |
|3 | 3     | 44            |0          |

i was trying using eloquent laravel like this

ItemStockModel::get()

thank you

You should use Join query for this. https://laravel.com/docs/7.x/queries#joins — Shailesh Matariya, May 29 '20 at 16:20
for several warehouse in mysql you should manage the result server side .. (not by sql) — ScaisEdge, May 29 '20 at 16:24
@scaisEdge how if i have only two warehouse, please give me example using sql or eloquent laravel — Encang Cutbray, May 29 '20 at 16:31
@user8210918 yes, you can join same table multiple times in laravel also. — Shailesh Matariya, May 29 '20 at 16:36
@ShaileshMatariya please give me example using sql or eloquent laravel — Encang Cutbray, May 29 '20 at 16:43

score 0 · Answer 1 · answered May 29 '20 at 16:33

0

If you have only 2 warehouse in sql you could try

select distinct item_id, t1._qty qty_warehouse1, t2.qty qty_warehouse1
from my_table m
left  join  (
    select item_id, qty
    from my_table
    where warehouse_id = 1
) t1 on t1.item_id = m.item_id
left  join  (
    select item_id, qty
    from my_table
    where warehouse_id = 2
) t2 on t2.item_id = m.item_id

answered May 29 '20 at 16:33

ScaisEdge

131,976
10
91
107

owh thanks you, I appreciate it. is it possible if i am using eloquent laravel – Encang Cutbray May 29 '20 at 16:38
i'm not in eloquent but I assume you can use a raw query – ScaisEdge May 29 '20 at 17:01

score 0 · Answer 2 · answered May 29 '20 at 17:03

What you are asking is to break the first normal form and this is bad practice.

The solution is to have a one to many relation in the table so eloquent can query the result and create the object with the relations you want. Adapt your situation to this solution here

$item->qty_by_warehouses;

if you cannot modify the table then

you must get all the items id, Loop each one and create an "each_id" object and add to a Result list.

query each id to get warehouse_id and qty and add them to a list add this list to each_id object.

This will get you the same result as if you have had the eloquent with the one to many relation.

How to select statement for showing data

2 Answers2