使用select时，关系不加载

Introduction
Hello. I'm currently building a FacebookFeedParser, and at the moment I'm trying to build a method in a Controller that lists the Facebook pages with the best post/like ratio from today to the user. To do that, I have built the following query in pure 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;

What I now wanted to do, is to transform this query into Laravel/Eloquent.

Current state
I have the following classes in my project

Controllers: PageController, PostController
Models: FacebookPost, FacebookPage, BaseModel

The FacebookPost and FacebookPage model are both defining their relations like this

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');
}

In the BaseModel, I have defined a scope, which will be used multiple times in the project

/**
 * 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() . '"');
}

And this is the query I've built in order to get those posts, with the filters

$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();

Problem
The problem I'm currently having, is, that, when I try to rebuild the above query, I'm not getting all fields I want. As soon as I add an select to the Builder, the relations array, with the index page is null. If I ommit the select method, I'm getting the FacebookPage, but I want to have those specific fields

Now I'm getting an object. I guess this is because I'm using the Eloquent Builder right? Isn't it somehow possible to only get the fields I want to have? The result I'm expecting should look like this (per row)

facebook_name    |    postCount    |    likes    |    likesPerPost

McDonalds             1000              500           0.5

I also tried it like this

$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();

Would I need to use the DB class instead of Eloquent? Or what would be the best solution for this problem?

Alternative solution

$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();

This, however, works. Would this be the correct solution for my use case? I'm just asking if it even make sense to use Eloquent here, since I'm not really trying to get an object, but data from multiple sources.

If you want with('page') to work when you specify the list of fields to fetch using select() you need to make sure that the foreign key is in that list, otherwise Laravel is not able to fetch related models. So in your case make sure that page_id is fetched from the database.