php - laravel - 给定可变用户输入生成组合

Question

╔═══╦══════════════╦═════════════╗
║   ║id            ║name         ║
╠═══╬══════════════╬═════════════╣
║   ║ 1            ║a1           ║
║   ║ 2            ║b1           ║
║   ║ 3            ║b2           ║
║   ║ 4            ║c1           ║
║   ║ 5            ║c2           ║
╚═══╩══════════════╩═════════════╝

我正在使用 laravel 和 mysql
考虑这张表。我想生成输入用户指定的所有组合。例如，如果用户输入 (a,b) 我的代码应该生成 (a1,b1) , (a1,b2)
如果用户输入 (b,c) 我的代码应该生成 (b1,c1), (b1,c2), (b2,c1), (b2,c2)

到目前为止，我有以下查询:

DB::select(DB::raw("select t1.id as t1_id, t1.name as t1_name,
t2.id as t2_id, t2.name as t2_name,
t3.id as t3_id, t3.name as t3_name, 
from (select * from table where name like 'a%') as t1 
cross join (select * from table where name like 'b%') as t2 
cross join (select * from table where name like 'c%') as t3"));

但是这个查询仅限于我知道用户输入的情况，例如在这种情况下用户输入 3 个变量，即 (a,b,c)

如何使查询动态化，以便它根据用户输入自动调整。

Answer 1

所以，这是一个伪代码，并不特定于 laravel，但希望它能让你明白我的意思。

每个输入都作为单独的 POST 出现

$input1 = $_POST[input1]
$input2 = $_POST[input2]
$input3 = $_POST[input3]

构建查询的三个变量

$fullQuery = '';
$topQuery = '';
$bottomQuery = '';

IF (isset($input1) AND !empty($input1) AND isset($input2) AND !empty($input2))
{
$topQuery =  "select t1.id as t1_id, t1.name as t1_name,
t2.id as t2_id, t2.name as t2_name";


$bottomquery = " from (select * from your_table where name like '$input1%') as t1 
cross join (select * from your_table where name like '$input2%') as t2"; 
}

IF (isset($input1) AND !empty($input1) AND isset($input2) AND !empty($input2) AND isset($input3) AND !empty($input3))
{

$topQuery.=  " ,t3.name AS t3_name"


$bottomQuery.= " CROSS JOIN (SELECT * FROM your_table WHERE NAME LIKE '$input3%') AS t3"

}

$fullQuery = $topQuery . $bottomQuery . ";";