条件插入? [英] Conditional Insert?

问题描述

这是我的另一个问题的后续:分隔的 id 字符串作为字段还是单独的表?

This is a kind of follow-up to my other question: Delimited string of ids as a field or a separate table?

我有一个数据库，其中包含一个产品表、一个类别和一个类似于productsInCategories"(在另一个问题中描述).productsInCategories 表有两个字段:productId 和 categoryId.

I have a database containing a table of products, one of categories, and one like "productsInCategories"(described in the other question). The productsInCategories-table has two fields: productId and categoryId.

当我想从一个类别中获取所有产品时，我会做类似的事情:

When I want to get all the products from a category I do something similar to:

SELECT *
FROM Products
INNER JOIN ProductsInCategories
ON Products.Id=ProductsInCategories.productId

它运行良好，我认为这是正确的方法吗?

Its working well and I think it's the right way to do it?

无论如何，我不确定如何以最佳方式插入新产品.客户端发送一个新产品的属性，以及它应该添加到的类别的 id.服务器在 Products-table 上执行一个 INSERT 查询，也在 ProductsInCategories 上执行一个 INSERT 查询，它插入新创建的产品的 id 和客户端指定的 category-id.这样做的问题是用户当然可以发送自定义数据，并指定一个不属于他的类别.

Anyway, I'm unsure of how to insert new products the best way. The client sends properties of a new product, and an id of the category it should be added to. The server executes an INSERT query on the Products-table, and also one on the ProductsInCategories where it inserts the id of the newly created product and category-id the client specified. The problem with this is that a user of course could send custom data, and specify a category not belonging to him.

我是否应该先做一个 SELECT 并获取用户指定的类别的用户 ID，并检查它是否与当前用户的 ID 匹配?或者 ProductsInCategories 还应该包含一个 userId 字段(似乎是多余的?)?或者最好，有没有办法在 SELECT 查询中验证这一点?

Should I first do a SELECT and get the userID of the category which the user specified, and check if it matches with the id of the current user? Or should the ProductsInCategories also contain a field for userId(seems redundant?)? Or preferably, is there a way to verify this in the SELECT query?

推荐答案

我想出了一种在插入查询中放置一种条件的方法.方法如下:

I worked out a way to put a kind of conditional in the insert-query. This is how:

INSERT INTO productsInCategories (categoryId, productId)
SELECT id,@0 FROM Categories WHERE userId=@1 AND id=@2

@0=Last insert id (the product inserted prior to this query)
@1=The user id
@2=The category id supplied by the client

如果客户端提供了一个受惊的类别 ID，那么 select 语句将找不到任何记录(除非该类别由同一用户拥有，以防万一我们不关心)并且不会插入任何内容.然后我们将只检查插入了多少行(我使用的 WebMatrix.Data.Database 的执行函数返回受影响的记录数作为一个 int，然后将是 0)，如果没有则回滚.

If the client supplies a spooked category-id then no record will be found by the select statement(unless that category is owned by the same user, which in case we don't care about) and nothing will be inserted. Then we'll just check how many rows were inserted(the execute-function of WebMatrix.Data.Database which I use returns the count of records affected as an int which will be 0 then) and if none then roll back.

带有嵌套 SELECT 的 INSERT 看起来很有趣，至少对我来说是这样.没有Values()"，第二个不相关的参数在它中间.我还没有真正查过任何关于它的东西，也没有任何事实，但我认为这是因为 select-query 基本上返回一个 Values()-object 所以我们必须省略它，这就是为什么我们不能在选择查询之后也放置额外的参数.相反，我们将它们作为常量传递给 select-query，因此它只会将它们放在它返回的 Values-object 中.

The INSERT with the nested SELECT looks funny, at least to me. With no "Values()" and the second unrelated parameter is in the middle of it. I haven't really looked up anything about that and don't have any facts but I think that's because the select-query basically returns a Values()-object so we'll have to omit it, and that's why we can't put additional parameters after the select-query too. Instead we pass them to the select-query as constants so it'll just put them in the Values-object it returns.

这篇关于条件插入?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！