Multiple Order By and Order by nearest value-CodePudding

I'm trying to find the logic or method should i do to find nearest value

Storage data like this

ID	Loc_id	item_id	batch	exp_date	qty	pallet	location_type
21	M-16-10	1	092021	2024-08-16	8	1001	PICK
22	M-16-10	1	092021	2024-08-16	52	1002	PICK
23	K-15-60	1	092021	2024-08-17	42	1003	RACK
24	K-17-50	1	092021	2024-08-18	32	1004	RACK
25	K-20-60	1	092021	2024-08-19	33	1005	RACK
26	K-24-60	1	092021	2024-08-20	42	1006	RACK
27	K-26-40	1	092021	2024-08-21	22	1007	RACK
28	K-27-20	1	092021	2024-08-22	32	1008	RACK
29	K-32-40	1	092021	2024-08-23	12	1009	RACK

Order data like this

id	outstanding	item_id
1	42	1

And code like this

$xsql = Storage::select('storages.*')
                    ->selectRaw("min(qty) as min_qty")
                    ->where('item_id', $outstanding->item_id)
                    ->whereRaw('(qty-pick) <= ?',[$outstanding->outstanding])
                    ->whereRaw('qty-pick>0')
                    ->orderBy('exp_date', 'asc')
                    ->orderByRaw('abs((qty-pick)-"'.$outstanding->outstanding.'")')
                    ->groupBy('id')
                    ->first();

The query if i dump

SELECT
    * 
FROM
    storages 
WHERE
    item_id = 1 
    AND qty - pick > 0 
    AND qty - pick <= 42 
GROUP BY
    id 
ORDER BY
    exp_date ASC,
    abs( ( qty - pick ) - 42 )

If I execute with this code, I get wrong result Current result

ID	Loc_id	item_id	batch	exp_date	qty	pick	put	pallet	location_type
23	K-15-60	1	092021	2024-08-17	42	0	0	1003	RACK

The first row that system show was Storage ID 23 not 21 (exp date storage id 21 is nearly exp than storage id 23), how to combine order by exp_date asc and order by nearest value from the outstanding value?

my expected result was like this

ID	Loc_id	item_id	batch	exp_date	qty	pick	put	pallet	location_type
21	M-16-10	1	092021	2024-08-16	8	0	0	1001	PICK

CodePudding user response：

One possibility would be to use LIMIT:

  SELECT * 
    FROM storages 
   WHERE item_id = 1 AND qty - pick BETWEEN 1 AND 42
ORDER BY exp_date ASC
   LIMIT 1

This would be a solution where the table with the outstanding value is joined:

  SELECT storages.* 
    FROM storages 
    JOIN outstanding USING (item_id)
   WHERE outstanding.item_id = 1 
     AND qty - pick BETWEEN 1 AND outstanding.outstanding
ORDER BY exp_date ASC
   LIMIT 1

And this would give you the result for all rows in outstanding:

   SELECT storages.* 
     FROM storages 
     JOIN outstanding USING (item_id)
    WHERE qty - pick BETWEEN 1 AND outstanding.outstanding
 GROUP BY outstanding.item_id
 ORDER BY exp_date ASC

See example sqlfiddle