{"id":193,"date":"2014-12-26T08:47:00","date_gmt":"2014-12-26T06:47:00","guid":{"rendered":"https:\/\/celilsemi.erkiner.com\/blog\/?p=193"},"modified":"2020-05-26T09:02:15","modified_gmt":"2020-05-26T07:02:15","slug":"search-sets-within-sets-in-mysql","status":"publish","type":"post","link":"https:\/\/celilsemi.erkiner.com\/blog\/search-sets-within-sets-in-mysql\/","title":{"rendered":"Search Sets within Sets in MySQL"},"content":{"rendered":"\n

The Problem;<\/strong><\/h2>\n\n\n\n

Let’s describe a MySQL database system which is going to used for searching the product we can make with the materials we have.<\/p>\n\n\n\n

Table of Products<\/p>\n\n\n\n

+-------+-------------+\n|  id   |   product   |\n+-------+-------------+\n|   1   |   product1  |\n+-------+-------------+\n|   2   |   product2  |\n+-------+-------------+\n|   3   |   product3  |\n+-------+-------------+\n|   4   |   product4  |\n+-------+-------------+<\/pre><\/div>\n\n\n\n
Table of Materials<\/p>\n\n\n\n
+-------+--------------+\n|  id   |   material   |\n+-------+--------------+\n|   1   |   material1  |\n+-------+--------------+\n|   2   |   material2  |\n+-------+--------------+\n|   3   |   material3  |\n+-------+--------------+\n|   4   |   material4  |\n+-------+--------------+\n|   5   |   material5  |\n+-------+--------------+\n|   6   |   material6  |\n+-------+--------------+<\/pre><\/div>\n\n\n\n
Table of Material needs for Products (Many To Many)<\/p>\n\n\n\n
+-------+-------------+-------------+\n|  id   |  productid  |  materialid |\n+-------+-------------+-------------+\n|   1   |      1      |      1      |\n+-------+-------------+-------------+\n|   2   |      1      |      2      |\n+-------+-------------+-------------+\n|   3   |      1      |      3      |\n+-------+-------------+-------------+\n|   4   |      1      |      4      |\n+-------+-------------+-------------+\n|   5   |      2      |      1      |\n+-------+-------------+-------------+\n|   6   |      2      |      2      |\n+-------+-------------+-------------+\n|   7   |      2      |      3      |\n+-------+-------------+-------------+\n|   8   |      3      |      3      |\n+-------+-------------+-------------+\n|   9   |      3      |      4      |\n+-------+-------------+-------------+\n|  10   |      3      |      5      |\n+-------+-------------+-------------+\n|  11   |      4      |      5      |\n+-------+-------------+-------------+\n|  12   |      4      |      6      |\n+-------+-------------+-------------+<\/pre><\/div>\n\n\n\n
These are just examples of the database structure. With these kinds of tables and data, the system needs to perform a search. And the real database will be more complex and crowded.<\/p>\n\n\n\n
Example Search;<\/p>\n\n\n\n
+----------------------------------------------+--------------------------+\n|                   search                     |          result          |\n+----------------------------------------------+--------------------------+\n|  material1, material2, material3, material4  |    product1, product2    |\n+----------------------------------------------+--------------------------+\n|        material1, material2, material3       |          product2        |\n+----------------------------------------------+--------------------------+\n|  material3, material4, material5, material6  |    product3, product4    |\n+----------------------------------------------+--------------------------+\n|             material5, material6             |          product4        |\n+----------------------------------------------+--------------------------+\n|  material2, material3, material4, material5  |          product3        |\n+----------------------------------------------+--------------------------+\n|        material1, material2, material3       |          product2        |\n+----------------------------------------------+--------------------------+\n|        material4, material5, material6       |          product4        |\n+----------------------------------------------+--------------------------+\n|             material1, material2             |             -            |\n+----------------------------------------------+--------------------------+<\/pre><\/div>\n\n\n\n
In words, the search will give the result with the product names based on the material names we give as input. <\/p>\n\n\n\n
The Solution;<\/h2>\n\n\n\n
This is an example of a “set-within-sets” query. The most flexible way of answering it is to use aggregation with a having<\/code> clause:<\/p>\n\n\n\n
SET @LISTOFMATERIALS = 'material1,material2,...' <\/pre><\/div>\n\n\n\n
The variable @LISTOFMATERIALS<\/code> has a list of materials to start the search with. It is a comma-separated string of material names such as 'material1,material2,...'<\/code> with no spaces after the comma.<\/p>\n\n\n\n
SELECT p.* \nFROM   tableofmaterialneeds tomn \n       JOIN products p \n         ON tomn.productid = p.productid \n       JOIN tableofmaterials tom \n         ON tomn.materialid = tom.materialid \nGROUP  BY p.productid \nHAVING sum(find_in_set(tomn.material, @LISTOFMATERIALS) = 0) = 0; <\/pre><\/div>\n\n\n\n
The find_in_set()<\/code> returns 0<\/code> only when a material is not in the set. The sum()<\/code> counts the number of materials, and the = 0<\/code> is saying that there are none.<\/p>\n\n\n\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
The Problem; Let’s describe a MySQL database system which is going to used for searching the product we can make with the materials we have. Table of Products Table of Materials Table of Material needs for Products (Many To Many) These are just examples of the database structure. With these kinds of tables and data, […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"site-sidebar-layout":"default","site-content-layout":"default","ast-site-content-layout":"default","site-content-style":"default","site-sidebar-style":"default","ast-global-header-display":"","ast-banner-title-visibility":"","ast-main-header-display":"","ast-hfb-above-header-display":"","ast-hfb-below-header-display":"","ast-hfb-mobile-header-display":"","site-post-title":"","ast-breadcrumbs-content":"","ast-featured-img":"","footer-sml-layout":"","ast-disable-related-posts":"","theme-transparent-header-meta":"default","adv-header-id-meta":"","stick-header-meta":"","header-above-stick-meta":"","header-main-stick-meta":"","header-below-stick-meta":"","astra-migrate-meta-layouts":"default","ast-page-background-enabled":"default","ast-page-background-meta":{"desktop":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"ast-content-background-meta":{"desktop":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"tablet":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""},"mobile":{"background-color":"var(--ast-global-color-5)","background-image":"","background-repeat":"repeat","background-position":"center center","background-size":"auto","background-attachment":"scroll","background-type":"","background-media":"","overlay-type":"","overlay-color":"","overlay-opacity":"","overlay-gradient":""}},"footnotes":""},"categories":[8],"tags":[36,35,16],"class_list":["post-193","post","type-post","status-publish","format-standard","hentry","category-back-end","tag-algorithm","tag-mysql","tag-server"],"aioseo_notices":[],"_links":{"self":[{"href":"https:\/\/celilsemi.erkiner.com\/blog\/wp-json\/wp\/v2\/posts\/193","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/celilsemi.erkiner.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/celilsemi.erkiner.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/celilsemi.erkiner.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/celilsemi.erkiner.com\/blog\/wp-json\/wp\/v2\/comments?post=193"}],"version-history":[{"count":1,"href":"https:\/\/celilsemi.erkiner.com\/blog\/wp-json\/wp\/v2\/posts\/193\/revisions"}],"predecessor-version":[{"id":194,"href":"https:\/\/celilsemi.erkiner.com\/blog\/wp-json\/wp\/v2\/posts\/193\/revisions\/194"}],"wp:attachment":[{"href":"https:\/\/celilsemi.erkiner.com\/blog\/wp-json\/wp\/v2\/media?parent=193"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/celilsemi.erkiner.com\/blog\/wp-json\/wp\/v2\/categories?post=193"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/celilsemi.erkiner.com\/blog\/wp-json\/wp\/v2\/tags?post=193"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}