我目前正在构建一个新闻网站,该网站具有发布内容、将内容分类和投票等功能。
我已经到了为我的用户制作主页的阶段,这将显示最新的帖子,但我想让我的用户能够按类别排序。
这是我当前的查询
SELECT p.*,
(SELECT Count(id)
FROM comments AS c
WHERE c.post = p.id) AS commentCount,
v.type AS vote_type,
(SELECT Count(id)
FROM votes AS vo
WHERE vo.post_id = p.id) AS totalVotes
FROM posts AS p
LEFT JOIN tagged AS t
ON p.id = t.post_id
LEFT JOIN votes AS v
ON v.post_id = p.id
WHERE (v.user_id = 1 OR v.user_id IS NULL)
AND t.tag_id = 7
ORDER BY timestamp DESC
LIMIT :limit OFFSET :offset
该查询有以下代码部分,使其返回仅分配(标记)类别id为7
的帖子(如果没有这两个部分,该查询将成为我通常用于最新帖子的基本查询)
LEFT JOIN tagged AS t
ON p.id = t.post_id
AND t.tag_id = 7
我想知道是否有一种方法可以让基本查询没有类别代码,然后在需要时输入类别代码,而不是两个查询?有没有一种方法可以使查询"动态"。
我想实现其他排序功能,但我觉得创建一堆(稍微)不同的副本是低效的。
是的,人们总是这样做。您可以动态设置参数,例如下面的第一个示例,也可以动态构建SQL语句。我认为最好的做法是:1。确保它不会变得太复杂。当你有一个对每个人来说都是一切的查询时,看看构建一个查询生成器或多个查询。2.将动态SQL语句与动态参数相结合。
准备您的声明:
SELECT p.*,
(SELECT Count(id)
FROM comments AS c
WHERE c.post = p.id) AS commentCount,
v.type AS vote_type,
(SELECT Count(id)
FROM votes AS vo
WHERE vo.post_id = p.id) AS totalVotes
FROM posts AS p
LEFT JOIN tagged AS t
ON p.id = t.post_id
LEFT JOIN votes AS v
ON v.post_id = p.id
WHERE (v.user_id = 1 OR v.user_id IS NULL)
AND t.tag_id = :tagid
ORDER BY timestamp DESC
LIMIT :limit OFFSET :offset
现在绑定您的元素(伪代码):
if( ! isset( $someValue ) ) $someValue = 7; // I am just giving it a default value for
// an example. You can do whatever.
pdo->bindParam( ':tagid', $someValue, parameterType such as PDO::PARAM_INT);
您可以继续这样构建,也可以分解更大的SQL准备语句。举个例子:
if( $categoryCodeNeeded === true ) // add another and statement to where portion of your sql statement
$select += " AND tag_id = 7 ";
编辑以回答评论中的问题:
您提供的代码:SELECT p.*现在被分配给一个变量,或者它在另一段代码中,看起来像这样:
$statement = $dbHandler->prepare('SELECT p.* FROM posts p blah blah blah' );
如果是第二种情况,那么您可以将该行代码更改为:
$statement=$dbHandler->prepare($yourOwnSelectVariable);
然后,您可以随心所欲地构建变量。
$yourOwnSelectVariable = "SELECT p.* FROM posts p";
if( $categoryCodeNeeded )
$yourOwnSelectVariable += " WHERE tag_id = :tagid ";
$statement = $dbHandler->prepare( $yourOwnSelectVariable );
if( ! isset( $tagId ) ) $tagId = 7;
$statement->bindParam( ':tagid', $tagId, PDO::PARAM_INT);