Databases
StackOverflow2013
Postsdbo
POMySQL - find field with comma separated value by all combinations of a set of numbers
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POMySQL - find field with comma separated value by all combinations of a set of numbers
    primarykey
    data
    text
    <p>We have a product table in MySQL where each row has a field with a comma separated value of options wich combine to become this product. In the scheme below you can see both tables. The field with comma separated values is option_ids, this can for example look like 2,5,6,23 or 4,2,76. These numbers are options wich exist in the option table.</p> <p>Product:<br> <code>|product_id|name|description|option_ids|price|</code></p> <p>Option:<br> <code>|option_id|type_id|label|group|pos|count|price|</code></p> <p>The input we get is an array of unique options. We wanna find every product that has this set of options or a subset of it in option_ids.</p> <p>The way we do this now is by generating a powerset of the array of options so that we have all possible combinations of the array of options. Then we generate a mysql query wich queries for all these combinations in 'OR WHERE' statements.</p> <p>The problem here is that the powerset grows exponentially, so when we have an array of 16 unique options the powerset gives an array of 65536 possibilities. We had to increase the memory usage of php for this to work properly but now we run into MySQL problems because the query gets so enormous.</p> <p>Is there another way to tackle this problem?</p> <p>I hope I've given enough information, if not please do ask!</p> <p>EDIT: A third table will get us the same problem because we need to find subsets to... It's not said that an array of options IS ONE product, it can be one product but it can also be 2 or 3 products. For example: options 1,2 make product A, options 2,3 make product B, and options 4,5,6 make product C. If we have option array [1,2,4,5,6] we would want to find product A and C.</p>
    singulars
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PTQuestion
    1. USuser1569081
    1. USuser1569081
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POMySQL - find field with comma separated value by all combinations of a set of numbers
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POMySQL - find field with comma separated value by all combinations of a set of numbers
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload