Skip to content

Instantly share code, notes, and snippets.

@lennartvdd

lennartvdd/sqlbuilder_dimension_categories.php

Last active April 28, 2017 11:51
Show Gist options
  • Save lennartvdd/b349c7a35c895e1568db052148d37de5 to your computer and use it in GitHub Desktop.
Save lennartvdd/b349c7a35c895e1568db052148d37de5 to your computer and use it in GitHub Desktop.
Download ZIP
Simple Stand-alone SQL Query Builder written in PHP to help categorize objects by their dimensions
Raw
sqlbuilder_dimension_categories.php
<?php
/*
Author: Lennart van den Dool
Date: 2017-03-10
Purpose: Query Builder categorize objects by dimension (assuming cubiods)
If you run this script in the command line it will output some SQL that might help you get what you want.
This assumes the cuboid rotation is limited to the 6 permutations of l x b x h .
If the cubiod can be oriented otherwise (i.e. diagonally) the number of possible
rotations is infinate and there is no solution.
*/
const COL_LENGTH = "length";
const COL_WIDTH = "width";
const COL_HEIGHT = "heigth";
const TMP_TABLE = "tmp_dimension_categories";
const SRC_TABLE = "product";
$dimensionSet = [
"15x20x30" => [15,20,30],
"50x30x30" => [50,30,30],
"100x50x50" => [100,50,50],
"other" => [9999999999,9999999999,9999999999],
];
$weightSet = [
"15x20x30" => 15,
];
echo "-- this statement creates a temporary table used for grouping the result." . PHP_EOL;
echo "CREATE TABLE ".TMP_TABLE." (
id INT(11) NOT NULL AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
length INT(11) NOT NULL,
width INT(11) NOT NULL,
height INT(11) NOT NULL,
weight INT(11) NULL DEFAULT NULL
);";
echo PHP_EOL;
echo "-- fill the temporary table with data" . PHP_EOL;
foreach ($dimensionSet as $name => $v) {
if (isset($weightSet[$name])) {
echo "INSERT INTO ".TMP_TABLE." (name, length, width, height, weight) VALUES ('$name', {$v[0]}, {$v[1]}, {$v[2]}, {$weightSet[$name]});";
} else {
echo "INSERT INTO ".TMP_TABLE." (name, length, width, height) VALUES ('$name', {$v[0]}, {$v[1]}, {$v[2]});";
}
echo PHP_EOL;
}
//make correlated subquery to get only first relation to fit it in.
echo PHP_EOL;
echo "-- select data (modify query to suit your needs)" . PHP_EOL;
$condition = buildCondition("p", "c");
$subQuery = "
SELECT
id
FROM
".TMP_TABLE." c
WHERE
$condition
ORDER BY c.id ASC
LIMIT 1";
echo "SELECT
firstCat.name, count(*)
FROM
".SRC_TABLE." AS p
INNER JOIN ".TMP_TABLE." AS firstCat
WHERE
firstCat.id = ($subQuery)
GROUP BY
firstCat.id;";
echo PHP_EOL;
echo PHP_EOL;
echo "-- clean up temporary table" . PHP_EOL;
echo "DROP TABLE ".TMP_TABLE.";";
/// functions
function permutations(array $elements)
{
if (count($elements) <= 1) {
yield $elements;
} else {
foreach (permutations(array_slice($elements, 1)) as $permutation) {
foreach (range(0, count($elements) - 1) as $i) {
yield array_merge(
array_slice($permutation, 0, $i),
[$elements[0]],
array_slice($permutation, $i)
);
}
}
}
}
function buildCondition($productAlias, $categoryAlias)
{
$dimensions = ["$categoryAlias.length", "$categoryAlias.width", "$categoryAlias.height"];
$permutations = permutations($dimensions);
$parts = [];
foreach ($permutations as $v) {
$parts [] = "($productAlias.".COL_LENGTH." <= {$v[0]} AND $productAlias.".COL_WIDTH." <= {$v[1]} AND $productAlias.".COL_HEIGHT." <= {$v[2]})" . PHP_EOL;
}
$c = "-- dimensions condition" . PHP_EOL;
$c .= " (" . trim(implode(" OR ", $parts)) . ")" . PHP_EOL;
$c .= " -- weight condition" . PHP_EOL;
$c .= " AND (c.weight IS NULL OR p.weight <= c.weight)";
return $c;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment