Laravel过滤产品按季节价格折扣

Question

Laravel get season price with discount and filter product by price ASC.

I have season table with this

+----+------------+------------+------------+-------+------+----------+------------+------------+------------+
| id | base_price | startDate  | endDate    | cost  | type | Duration | product_id | created_at | updated_at |
+----+------------+------------+------------+-------+------+----------+------------+------------+------------+
|  1 |     100.00 | 1537390800 | 1538773200 | 95.00 | day  |        2 |          9 | NULL       | NULL       |
|  2 |     100.00 | 1537390800 | 1538773200 | 85.00 | day  |        3 |          9 | NULL       | NULL       |
|  3 |     100.00 | 1537390800 | 1538773200 | 75.00 | day  |        4 |          9 | NULL       | NULL       |
|  4 |     100.00 | 1537390800 | 1538773200 | 70.00 | day  |        5 |          9 | NULL       | NULL       |
+----+------------+------------+------------+-------+------+----------+------------+------------+------------+

and product table

+----+----------------------------+-----------+-----+-------+---------+--------------+---------------------+---------------------+---------------------+
| id | alias                      | status    | img | price | user_id | published_at | deleted_at          | created_at
 | updated_at          |
+----+----------------------------+-----------+-----+-------+---------+--------------+---------------------+---------------------+---------------------+
|  8 | toyota-corolla-1-6-elegant | draft     | 18  | 30    |       1 | 2018-08-14   | NULL                | 2018-08-14 15:06:12 | 2018-08-20 14:58:18 |
|  9 | test                       | published |     | 0     |       1 | 2018-08-23   | 2018-09-10 19:44:29 | 2018-08-23 14:45:18 | 2018-09-10 19:44:29 |
+----+----------------------------+-----------+-----+-------+---------+--------------+---------------------+---------------------+---------------------+

as you can see we have three possible options

1) if we did not have at season table price for selected days we use the price from product table

2) if we have price in base_price but not have but there is no discount we use base_price

3) if we select 2 days (and got into date range) we use field cost in this case 95$

I tried this code, but it shows only one product, at a time when I need to show all products with a dynamic price.

$products_obj->join('ec_season', function ($join) use($days) {
            $join->on( 'ec_products.id', '=', 'ec_season.product_id' )
                 ->where( 'ec_season.cost', DB::raw( "(
    select min(ec_season.cost)
    from ec_season
    where  `ec_season`.`product_id` = `ec_products`.`id`  and `ec_season`.`type` = 'day' and `Duration` <= '3'
)
" ) );
        });

I use laravel 5.6. can you please help me with this?

Can I via MySQL make new field with final cost (final_cost)? and it can be sorted by final_cost

Edit 1

new code but it still return duplicates product and show final_cost" => "ec_season.cost" instead of price

$products_obj->leftJoin('ec_season', 'ec_season.product_id', '=', 'ec_products.id')         
                     ->select('*',
                DB::raw("COALESCE('ec_season.cost', 'ec_season.base_price', 'products.price') as final_cost"))
                                     ->where('ec_season.type', 'day')
                                     ->where('ec_season.Duration', '<=', 3)
                                     ->orderBy('final_cost', 'desc');

Edit 2 or it is easier to choose all products, and calculate for each the right price, after this sort collection by price, and do another sql request, that it gives out in that order as the indicated id. or it will be slower than all this done with one sql query? if for example we have 100 products

Answer 1

Something like this might be better, not sure if I've captured all your requirements though. Also untested.

// use a left join to select all products, not just those with a related entry in the seasonal table
$products_obj->leftJoin('ec_season', 'ec_season.product_id', '=', 'ec_products.od')
    // maybe need to update the * to only select columns you need and prevent collisions
   // but just showing my idea which is to use COALESCE
    ->select('*', DB::raw("COALESCE('ec_season.cost', 'ec_season.base_price', 'products.price') as final_cost")
    ->where('ec_season.type', 'day')
    ->where('ec_season.Duration', '<=', 3)
    ->orderBy('final_cost', 'desc');

COALESCE MySQL Function