Best way to exclude results from large db query in Laravel 5.2

Question

I use Laravel 5.2 to create a large stock of the product, and I am having problems with the query that the products will show. Here is an example:

I have many products - more than 20 thousand, to be exact in my database. Most of them I want to show every day, but about 500-1000 I need to display only on certain days of the week.

Currenly I reviewed the following methods, each of which has its drawbacks:

• Create a product_availability table where each row will contain product_domains, days of the week, and available (true / false) columns. However, with this structure, I would have 7 rows giving information for 1 product, and the whole table would contain ~ 140,000 rows, which I don’t think is a good idea, because most products will be available every day anyway and only 500 -1000 will not be.

• My second assumption was to simply add an “availability” column to the product table, which would contain a JSON string in the format:

  {"mo": "true", "tu": "false", "we": "false", "th": "true", "fr": "false", "sa": "false", " su ":" true "}

Then I will make my request as follows:

$weekday = date('N');
        switch ($weekday) {
            case '1':
                $builder->where('$availability->mo', 'true');
                break;
            case '2':
                $builder->where('$availability->tu', 'true');
                break;
            case '3':
                $builder->where('$availability->we', 'true');
                break;
            case '4':
                $builder->where('$availability->th', 'true');
                break;
            case '5':
                $builder->where('$availability->fr', 'true');
                break;
            case '6':
                $builder->where('$availability->sa', 'true');
                break;
            case '7':
                $builder->where('$availability->su', 'true');
                break;
        }

IMO , .

3. , , , , , . , , "" , if, - , , .

, :

, , ( )?

Answer 1

, - ( ).

product_availability - :

| id | product_id | mon | tue | wed | thu | fri | sat | sun |
| 1  |      13403 |   1 |   0 |   1 |   0 |   1 |   0 |   0 |

- :

SELECT
  products.*
FROM
  products
LEFT JOIN
  product_availability 
ON
  product_availability.product_id = products.id
WHERE
  # show if no entry here (i.e. always to be shown)
  product_availability.id IS NULL 
  OR
  # only want to show specific days
  product_availability.id IS NOT NULL AND CASE
    WHEN DAYOFWEEK(CURDATE()) = 1 THEN `sun` = 1
    WHEN DAYOFWEEK(CURDATE()) = 2 THEN `mon` = 1
    WHEN DAYOFWEEK(CURDATE()) = 3 THEN `tue` = 1
    WHEN DAYOFWEEK(CURDATE()) = 4 THEN `wed` = 1
    WHEN DAYOFWEEK(CURDATE()) = 5 THEN `thu` = 1
    WHEN DAYOFWEEK(CURDATE()) = 6 THEN `fri` = 1
    WHEN DAYOFWEEK(CURDATE()) = 7 THEN `sat` = 1
  END

. product_availability, ( , ) , 1.

product_availability.

: http://sqlfiddle.com/#!9/c2bbf1/3

, :)

---

: , :

  ->leftJoin('product_availability', 'products.id', '=', 'product_availability.product_id')
  ->whereNull('product_availability.id')
  ->orWhere(function ($query) {
    $query
      ->whereRaw('(product_availability.id IS NOT NULL AND CASE
        WHEN DAYOFWEEK(CURDATE()) = 1 THEN `sun` = 1
        WHEN DAYOFWEEK(CURDATE()) = 2 THEN `mon` = 1
        WHEN DAYOFWEEK(CURDATE()) = 3 THEN `tue` = 1
        WHEN DAYOFWEEK(CURDATE()) = 4 THEN `wed` = 1
        WHEN DAYOFWEEK(CURDATE()) = 5 THEN `thu` = 1
        WHEN DAYOFWEEK(CURDATE()) = 6 THEN `fri` = 1
        WHEN DAYOFWEEK(CURDATE()) = 7 THEN `sat` = 1
      END)');
  });

DB:: raw-