I've read that this is a no-no, but I'm wondering if this is ALWAYS the case. I've got a list of "dining tables" that can accommodate an array of seats.
我已经读到这是一个禁忌,但我想知道这是否总是如此。我有一张可以容纳一系列座位的“餐桌”清单。
You can see in the image that table_num 12 will accommodate 2 OR 3 seats.
您可以在图像中看到table_num 12将容纳2 OR 3个席位。
So, now my current solution is to query all the table records (according to the user_index for undisclosed reasons) then loop through the results looking for tables that can fit 3 people (if it happens to be three people looking for a table).
所以,现在我现在的解决方案是查询所有表记录(根据user_index出于未公开的原因),然后遍历结果,查找可以容纳3人的表(如果恰好有三个人正在寻找表)。
I do that by using the array_implode() method (in php) on the 'seats' column of my return data. I feel like this is an easier solution than creating a separate 'seats' table and then assigning a table_index and user_index to each seating possibility, and then having to run a second query to find the table_num in the original 'tables' table.
我通过在返回数据的“席位”列上使用array_implode()方法(在php中)来做到这一点。我觉得这比创建一个单独的“席位”表然后为每个座位可能性分配table_index和user_index更容易,然后必须运行第二个查询以在原始“表”表中找到table_num。
Basically, by using array_implode() I am able to circumvent a second query. But, I don't know if that is less taxing. Or, possibly I am missing out on some awesome query language (like relational table lingo?)
基本上,通过使用array_implode(),我可以绕过第二个查询。但是,我不知道这是否减税。或者,我可能错过了一些很棒的查询语言(比如关系表术语?)
3 个解决方案
#1
2
If you removed the seats column from this table and then had a new seats table with three columns: id, table_index, and num_seats, where table_index relates to index in your existing table (many-to-one as there may be multiple entires per table in seats). The you could select the tables you want like so:
如果您从此表中删除了席位列,然后有一个包含三列的新席位表:id,table_index和num_seats,其中table_index与现有表中的索引相关(多对一,因为每个表可能有多个entires在座位上)。您可以选择所需的表格,如下所示:
SELECT tables.* FROM tables
INNER JOIN seats ON tables.index = seats.table_index
WHERE seats.num_seats = ?
#2
4
Create a table that's called something like TableSeats. In it, you'd have a table_num and a seats column. Use the table_num column as a foreign key. Then, to find tables that seat 3 people, it'd be like so:
创建一个名为TableSeats的表。在其中,你有一个table_num和一个席位列。使用table_num列作为外键。然后,为了找到可容纳3个人的桌子,它会是这样的:
select
*
from
tables t
where
exists (select 1 from tableseats s where s.seats = 3)
The magic here is the exists clause. Certainly, you could also do an inner join, but I'd recommend exists here, because this allows you to find any tables that can seat at least 7 people by saying where s.seats >= 7, or even a table that can seat between 5 and 8 people with where s.seats between 5 and 8.
这里的魔力是exists子句。当然,你也可以做一个内连接,但我建议在这里存在,因为这可以让你找到任何可以容纳至少7个人的桌子,说s.seats> = 7,甚至可以坐的桌子5到8人之间,其中s.seats在5到8之间。
#3
4
Correct me if I am wrong, but this does not sound a list. It is more like a range (like, from 2 up to 3 seats). So you could have a better structure by replacing the seats field by the fields min_seats and max_seats. The queries are straight forward then.
如果我错了,请纠正我,但这不是一个清单。它更像是一个范围(例如,从2到3个座位)。因此,您可以通过字段min_seats和max_seats替换seat字段来获得更好的结构。那么查询是直截了当的。
#1
2
If you removed the seats column from this table and then had a new seats table with three columns: id, table_index, and num_seats, where table_index relates to index in your existing table (many-to-one as there may be multiple entires per table in seats). The you could select the tables you want like so:
如果您从此表中删除了席位列,然后有一个包含三列的新席位表:id,table_index和num_seats,其中table_index与现有表中的索引相关(多对一,因为每个表可能有多个entires在座位上)。您可以选择所需的表格,如下所示:
SELECT tables.* FROM tables
INNER JOIN seats ON tables.index = seats.table_index
WHERE seats.num_seats = ?
#2
4
Create a table that's called something like TableSeats. In it, you'd have a table_num and a seats column. Use the table_num column as a foreign key. Then, to find tables that seat 3 people, it'd be like so:
创建一个名为TableSeats的表。在其中,你有一个table_num和一个席位列。使用table_num列作为外键。然后,为了找到可容纳3个人的桌子,它会是这样的:
select
*
from
tables t
where
exists (select 1 from tableseats s where s.seats = 3)
The magic here is the exists clause. Certainly, you could also do an inner join, but I'd recommend exists here, because this allows you to find any tables that can seat at least 7 people by saying where s.seats >= 7, or even a table that can seat between 5 and 8 people with where s.seats between 5 and 8.
这里的魔力是exists子句。当然,你也可以做一个内连接,但我建议在这里存在,因为这可以让你找到任何可以容纳至少7个人的桌子,说s.seats> = 7,甚至可以坐的桌子5到8人之间,其中s.seats在5到8之间。
#3
4
Correct me if I am wrong, but this does not sound a list. It is more like a range (like, from 2 up to 3 seats). So you could have a better structure by replacing the seats field by the fields min_seats and max_seats. The queries are straight forward then.
如果我错了,请纠正我,但这不是一个清单。它更像是一个范围(例如,从2到3个座位)。因此,您可以通过字段min_seats和max_seats替换seat字段来获得更好的结构。那么查询是直截了当的。