In our site, our products are t-shirts, and we
want to sell each of them in all possible sizes and colors. The code that populates the product_attribute table
uses the INSERT INTO command to insert a number of records produced by a SELECT query:
INSERT INTO `product_attribute` (`product_id`, `attribute_value_id`)
In our case, the SELECT query that generates the data to be inserted into product_attribute is a cross join.
This type of JOIN makes a Cartesian product between two data sets. The result is a list that contains all the possible
combinations between the records of the first data set and the records of the second data set.
For example, the Cartesian product between {1, 2, 3} and {a, b, c}, which is mathematically written as {1, 2, 3} ?—
{a, b, c}, is the following set of data: { {1, a}, {1, b}, {1, c}, {2, a}, {2, b}, {2, c}, {3, a}, {3, b}, {3, c} }.
In our case, if we make a Cartesian product between the IDs of the existing products and the IDs of the existing
attribute values, we obtain a list formed of (product_id, attribute_value_id) elements, which is exactly
the list we want to add to the product_attribute table. The syntax to implement this cross join operation with
MySQL highlighted:
INSERT INTO `product_attribute` (`product_id`, `attribute_value_id`)
SELECT `p`.`product_id`, `av`.`attribute_value_id`
FROM `product` `p`, `attribute_value` `av`;
This is certainly a nice way of creating many records into our database with minimal effort.
Pages:
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280