简介
你好我目前正在构建一个FacebookFeedParser,目前我正在尝试在Controller中构建一个方法,列出从今天起对用户具有最佳帖子/点赞比例的Facebook页面。为此,我在纯SQL 中构建了以下查询
SELECT pa.facebook_name, COUNT(po.id) AS postCount, SUM(likes) AS likes, SUM(likes)/COUNT(po.id) AS likesPerPost
FROM facebook_posts po
INNER JOIN facebook_pages pa ON pa.id = po.facebook_page_id
WHERE CONVERT_TZ(`facebook_created_time`, 'UTC', 'Europe/Berlin') > '2015-07-16 00:00:00'
GROUP by facebook_page_id
ORDER BY SUM(likes)/COUNT(po.id) DESC;
我现在想做的是将这个查询转换为Laravel/Eloquent。
当前状态
我在我的项目中有以下课程
控制器:PageController
、PostController
型号:FacebookPost
、FacebookPage
、BaseModel
FacebookPost
和FacebookPage
模型都像这个一样定义它们的关系
FacebookPage
/**
* Defines the relation between FacebookPage and FacebookPost
* One page can have multiple posts
*
* @see FacebookPage
* @see FacebookPost
*
* @return 'Illuminate'Database'Eloquent'Relations'HasMany
*/
public function posts()
{
return $this->hasMany(FacebookPost::class);
}
FacebookPost
/**
* Defines the association of this object with FacebookPage
* One facebook_post belongs to one facebook_page
*
* @return 'Illuminate'Database'Eloquent'Relations'BelongsTo
*/
public function page()
{
return $this->belongsTo(FacebookPage::class, 'facebook_page_id');
}
在BaseModel中,我定义了一个范围,它将在项目中多次使用
/**
* Query scope to get the database values for today
*
* @param $query
* @return mixed
*/
public function scopeToday($query)
{
return $query->whereRaw('CONVERT_TZ(`'. $this->createDateColumn .'`, "UTC", "'. env('APP_TIMEZONE') .'") > "' . Carbon::today()->toDateTimeString() . '"');
}
这是我为获得这些帖子而构建的查询,带有过滤器
$posts = App'Models'FacebookPost::with('page')
->selectRaw('COUNT(id) AS postCount, SUM(likes) AS likes, SUM(likes)/COUNT(id) AS likesPerPost')
->today()
->groupBy('facebook_page_id')
->orderByRaw('SUM(likes)/COUNT(id) DESC')
->get();
问题
我目前遇到的问题是,当我尝试重建上面的查询时,我没有得到我想要的所有字段。一旦我将select
添加到Builder
,索引为page
的relations
数组就为空。如果我提交了select
方法,我会得到FacebookPage,但我希望有那些特定的字段
现在我得到了一个对象。我想这是因为我在用Eloquent Builder
吧?难道不可能只得到我想要的田地吗?我期望的结果应该是这样的(每行(
facebook_name | postCount | likes | likesPerPost
McDonalds 1000 500 0.5
我也试过像这个
$posts = App'Models'FacebookPost::with(['page' => function($query) {
$query->select('facebook_name');
}])
->selectRaw('COUNT(id) AS postCount, SUM(likes) AS likes, SUM(likes)/COUNT(id) AS likesPerPost')
->today()
->groupBy('facebook_page_id')
->orderByRaw('SUM(likes)/COUNT(id) DESC')
->get();
我需要使用DB
类而不是Eloquent吗?或者这个问题的最佳解决方案是什么?
替代解决方案
$pages = DB::table('facebook_posts')
->select(DB::raw('facebook_pages.facebook_name, COUNT(facebook_posts.id) AS postCount, SUM(likes) AS likes, ROUND(SUM(likes)/COUNT(facebook_posts.id)) AS likesPerPost'))
->join('facebook_pages', 'facebook_posts.facebook_page_id', '=', 'facebook_pages.id')
->whereRaw('CONVERT_TZ(`'. $this->createDateColumn .'`, "UTC", "'. env('APP_TIMEZONE') .'") > "' . Carbon::today()->toDateTimeString() . '"')
->groupBy('facebook_page_id')
->orderByRaw('ROUND(SUM(likes)/COUNT(facebook_posts.id)) DESC')
->get();
然而,这是有效的。这是我的用例的正确解决方案吗?我只是想问在这里使用Eloquent是否有意义,因为我并不是真的想得到一个对象,而是来自多个来源的数据。
如果您想在使用select((指定要获取的字段列表时使用with('page'(age_id。