从数据库获取趋势帖子 - symfony 查询构建器 - MySQL [英] Get trending posts from database - symfony query builder - MySQL

问题描述

我正在尝试进行一个查询，该查询必须组合来自 2 个不同表的信息，但我在逻辑上遇到了问题.

I'm trying to make a query which has to combine information from 2 different tables and I'm having trouble with the logic.

这是我的表格，我排除了一些不相关的信息

Here are my tables,I'm excluding some of the irrelevant info

post(groupId, dateCreated)

post(groupId, dateCreated)

postbump(userId, postId)

postbump(userId, postId)

我正在尝试获取热门"帖子.换句话说，我需要获得

I'm trying to get Posts which are "trending". In other words, I need to get the posts which are

1. 在给定的组中，(由 $groups 数组提供)

1. In the given groups,(provided by $groups array)

在给定日期之后，(由 $date 提供)

After a given date,(provided by $date)

按他们的颠簸数量排序

这是我正在构建的功能，它只会获取新帖子，但它可能有助于了解我所追求的内容.请注意，我拥有的代码的当前存储库是 post 存储库(对于那些不熟悉 symfony 查询构建器的人，它基本上意味着它会自动从 post 表中选择).

This is my function that I am building off of which only gets new posts but it may help provide an understanding of what I am after. Note that the current repository of the code I have is the post repository(for those not familiar with symfony query builder it basically means that it automatically selects from the post table).

public function getNewHomeFeedPosts($groups){
    $qb = $this->repository->createQueryBuilder('x');
    $qb->select('x');
    $qb->where('x.groupId IN (:groups)');
    $qb->setParameter('groups',$groups);
    $qb->orderBy('x.dateCreated','DESC');
    return $qb->getQuery()->getResult();

}

这是我到目前为止所拥有的，我真正的问题在于按每个帖子的颠簸数量排序.我认为在某些时候必须使用连接.

This is what I have so far, My real issue lies in ordering by the amount of bumps each post has. I'm thinking that a join is going to have to be utilized at some point.

public function getTrendingHomeFeedPosts($groups, $date){
    $qb= $this->repository->createQueryBuilder('x');
    $qb->select('x');
    $qb->where('x.groupId IN (:groups) AND x.dateCreated < :dateCreated');
    $qb->setParameter('groups',$groups);
    $qb->setParameter('dateCreated', $date);
    //todo
    return $qb->getQuery()->getResult();
}

如果您不熟悉 querybuilder 语法，那么 SQL 中的答案就可以了.

If you aren't familiar with querybuilder syntax then an answer in SQL would be fine.

推荐答案

我不懂 symfony，所以我的答案是在 sql 中.您需要加入post 和postbump 表，按postid 分组并计算碰撞的数量并过滤分组和日期:

I do not know symfony, so my answer is in sql. You need to join the post and postbump tables, group by postid and count the number of bumps and filter on groups and the date:

select p.postid, p.groupid, p.datecreated, count(*) as postbumps
from post p
left join postbump pb on p.postid=pb.postid
where p.groupid in (...) and p.datecreated<...
group by p.postid, p.groupid, p.datecreated
order by count(*) desc

还请注意，您不能简单地将 in 运算符中的参数数组绑定为单个值 - 除非 symfony 的查询构建器使用字符串操作进行参数设置而不是准备好的语句.

Pls also note that you cannot simply bind an array of parameters within the in operator as a single value - unless symfony's query builder uses string operations for parameter setting and not prepared statements.

这篇关于从数据库获取趋势帖子 - symfony 查询构建器 - MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！