Level 56
I don't think you can solve this only with one SQL, as you want to combine different groups of records from table_attribute_values but don't know beforehand how many groups are there.
I ended up with a solution that builds a query for each group dynamically based on each group's attributes count.
I am assuming you have the tables with the same names and column names as you described on your original post.
I tried adding comments inline to the code to explain what is happening.
<?php
// ./routes/web.php
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Query\JoinClause;
use Illuminate\Support\Facades\Route;
// Models are listed inline here for brevity
// Move each Model to its own file
class Group extends Model
{
// custom table name as Laravel would guess
// this model's table is named "groups"
protected $table = 'table_groups';
// Cannot name it "attributes" as base model
// has a protected property named "attributes"
// So we would have problems when trying
// to read this relation results
public function groupAttributes()
{
return $this->hasMany(GroupAttribute::class);
}
}
// Named this model to be consistent with
// the relation on the Group model. See the comment
// on the relation definition regarding the reason
class GroupAttribute extends Model
{
// custom table name as Laravel would guess
// this model's table is named "attributes"
protected $table = 'table_attributes';
}
Route::get('/', function () {
// Fetch all groups
return Group::query()
// eager load a group's attributes,
// to avoid extra calls
->with([
'groupAttributes' => function ($relation) {
$relation->orderBy('id');
},
])
->orderBy('id')
// execute the query
->get()
// Loop through each group to fetch the combination
// based on each group's attribute count.
// This will end issuing N+2 queries to the database:
// One for fecthing the groups, one for fetch all
// groups' attributes, and N for each group
// attribute's total combination.
->flatMap(function (Group $group) {
// Start the base query
$builder = Group::query()->toBase()
->join('table_days', 'table_groups.id', '=', 'table_days.group_id')
->join('table_quantities', 'table_groups.id', '=', 'table_quantities.group_id')
->where('table_groups.id', $group->getKey()) // only for this group
->orderBy('table_days.day')
->orderBy('table_quantities.quantity');
// Loop through each attribute
$columns = $group->groupAttributes
->map(function (GroupAttribute $attribute) use ($builder) {
// We are going to make a join to the
// `table_attribute_values` table for each
// attribute id using a different table alias
$tableAlias = 'attributes_' . $attribute->getKey();
// Add the join restricting to the current attribute
$builder->join(
"table_attribute_values AS {$tableAlias}",
function (JoinClause $join) use ($tableAlias, $attribute) {
$join->on('table_groups.id', '=', "{$tableAlias}.group_id");
$join->where("{$tableAlias}.attribute_id", $attribute->getKey());
}
);
// return the desired column from the aliased table
return "{$tableAlias}.title";
})
// concatenate all table aliases to be used in a query
->implode(', ');
// add the columns, note we don't need
// to use GROUP BY in this query, as each combination
// is done by joining several times filtering by different
// `attribute_id` values
$builder->select([
$builder->raw('table_groups.id AS group_id'),
$builder->raw("CONCAT_WS('-', table_days.day, table_quantities.quantity, {$columns}) AS combinations"),
$builder->raw('NULL AS price'),
]);
// execute the combination query
return $builder->get()->toArray();
});
});
As said in a comment in the code, this approach will end issuing N+2 queries.
- One to fetch the groups
- One to fetch each group's attributes
- One for each group's attribute to fetch the combinations
That means for one group this code will make 3 queries on the database. For 2 groups it will end making 4, for 10 groups will make 12, and so one.
This is not an ideal scenario, and I hope you will use this code on an artisan command or scheduled job that is run periodically and saves the results for easier retrieval.
In case you have several groups, don't use this code to generate reports or other data by user request. Run from time to time, cache the results (save to the database, for example), and read the generated combinations from this saved data.
For reference I will list below the generated queries from your sample data.
1 - Group query
SELECT *
FROM `table_groups`
ORDER BY
`id` ASC
Results
+--+-----+
|id|title|
+--+-----+
|1 |rug |
+--+-----+
2 - Attributes query
SELECT *
FROM `table_attributes`
WHERE
`table_attributes`.`group_id` IN (1)
ORDER BY
`id` ASC
Results
+--+--------+-----+
|id|group_id|title|
+--+--------+-----+
|1 |1 |Color|
|2 |1 |Size |
+--+--------+-----+
3 - Combinations query (generated dynamically)
SELECT
`table_groups`.`id` AS `group_id`,
CONCAT_WS('-', `table_days`.`day`, `table_quantities`.`quantity`, `attributes_1`.`title`, `attributes_2`.`title`) AS `combinations`,
NULL AS `price`
FROM
`table_groups`
INNER JOIN `table_days`
ON `table_groups`.`id` = `table_days`.`group_id`
INNER JOIN `table_quantities`
ON `table_groups`.`id` = `table_quantities`.`group_id`
INNER JOIN `table_attribute_values` AS `attributes_1`
ON `table_groups`.`id` = `attributes_1`.`group_id`
AND `attributes_1`.`attribute_id` = 1
INNER JOIN `table_attribute_values` AS `attributes_2`
ON `table_groups`.`id` = `attributes_2`.`group_id`
AND `attributes_2`.`attribute_id` = 2
WHERE
`table_groups`.`id` = 1
ORDER BY
`table_days`.`day` ASC,
`table_quantities`.`quantity` ASC
Results
+--------+-------------------+-----+
|group_id|combinations |price|
+--------+-------------------+-----+
|1 |1-100-Red-Small |NULL |
|1 |1-100-Black-Small |NULL |
|1 |1-100-Black-Medium |NULL |
|1 |1-100-Red-Medium |NULL |
|1 |1-100-Yellow-Small |NULL |
|1 |1-100-Yellow-Medium|NULL |
|1 |1-200-Red-Small |NULL |
|1 |1-200-Black-Medium |NULL |
|1 |1-200-Yellow-Small |NULL |
|1 |1-200-Yellow-Medium|NULL |
|1 |1-200-Black-Small |NULL |
|1 |1-200-Red-Medium |NULL |
|1 |1-300-Yellow-Medium|NULL |
|1 |1-300-Black-Small |NULL |
|1 |1-300-Red-Small |NULL |
|1 |1-300-Black-Medium |NULL |
|1 |1-300-Yellow-Small |NULL |
|1 |1-300-Red-Medium |NULL |
|1 |1-400-Black-Small |NULL |
|1 |1-400-Red-Medium |NULL |
|1 |1-400-Red-Small |NULL |
|1 |1-400-Yellow-Small |NULL |
|1 |1-400-Black-Medium |NULL |
|1 |1-400-Yellow-Medium|NULL |
|1 |2-100-Black-Medium |NULL |
|1 |2-100-Yellow-Medium|NULL |
|1 |2-100-Red-Medium |NULL |
|1 |2-100-Black-Small |NULL |
|1 |2-100-Yellow-Small |NULL |
|1 |2-100-Red-Small |NULL |
|1 |2-200-Red-Small |NULL |
|1 |2-200-Yellow-Medium|NULL |
|1 |2-200-Yellow-Small |NULL |
|1 |2-200-Black-Medium |NULL |
|1 |2-200-Red-Medium |NULL |
|1 |2-200-Black-Small |NULL |
|1 |2-300-Black-Small |NULL |
|1 |2-300-Red-Medium |NULL |
|1 |2-300-Yellow-Small |NULL |
|1 |2-300-Black-Medium |NULL |
|1 |2-300-Yellow-Medium|NULL |
|1 |2-300-Red-Small |NULL |
|1 |2-400-Red-Small |NULL |
|1 |2-400-Yellow-Small |NULL |
|1 |2-400-Yellow-Medium|NULL |
|1 |2-400-Black-Small |NULL |
|1 |2-400-Black-Medium |NULL |
|1 |2-400-Red-Medium |NULL |
|1 |3-100-Red-Medium |NULL |
|1 |3-100-Yellow-Small |NULL |
|1 |3-100-Black-Small |NULL |
|1 |3-100-Yellow-Medium|NULL |
|1 |3-100-Red-Small |NULL |
|1 |3-100-Black-Medium |NULL |
|1 |3-200-Yellow-Small |NULL |
|1 |3-200-Red-Small |NULL |
|1 |3-200-Black-Small |NULL |
|1 |3-200-Red-Medium |NULL |
|1 |3-200-Black-Medium |NULL |
|1 |3-200-Yellow-Medium|NULL |
|1 |3-300-Black-Medium |NULL |
|1 |3-300-Black-Small |NULL |
|1 |3-300-Yellow-Small |NULL |
|1 |3-300-Red-Medium |NULL |
|1 |3-300-Yellow-Medium|NULL |
|1 |3-300-Red-Small |NULL |
|1 |3-400-Black-Small |NULL |
|1 |3-400-Yellow-Small |NULL |
|1 |3-400-Yellow-Medium|NULL |
|1 |3-400-Black-Medium |NULL |
|1 |3-400-Red-Small |NULL |
|1 |3-400-Red-Medium |NULL |
+--------+-------------------+-----+
Hope it helps.