Join head table with position table with laravel

Question

我有以下结构：

(Laravel版本5.4)

我有一个头表(系列)，以及一个与相关职位(职位)的表。posts表有三个值，它们决定了帖子是否应该显示，甚至不显示。系列表也包含此信息。这些列是：'published‘(布尔值)、'published_from’(日期时间)和'published_to‘(日期时间)。

要显示帖子、系列和帖子，必须发布(published = 1)，并且实际日期/时间必须大于或等于系列和帖子的published_from值，并且实际日期/时间也必须小于或等于published_to值。

为了检查是否应该显示位置，我编写了一个雄辩的作用域：

public function scopePublishedRestriction($query)
        {
            $date = Carbon::today();
            $today = date('Y-m-d', $date->getTimestamp());

            $query->accessible()
                ->join('learning_series', function ($join) use ($today) {
                    $join->on($this->table . '.learning_serie_id', 'learning_series.id')
                        ->where('learning_series.published', 1)
                        ->whereDate('learning_series.published_from', '<=', $today)
                        ->where(function ($join) use ($today) {
                            $join->orWhere(function ($join) use ($today) {
                                $join->whereDate('learning_series.published_to', '>=', $today);
                            })
                                ->orWhere('learning_series.published_to', '0000-00-00 00:00:00');
                        });
                })
                ->where($this->table . '.published', 1)
                ->where($this->table . '.published_from', '<=', $today)
                ->where(function ($query) use ($today) {
                    $query->orWhere(function ($query) use ($today) {
                        $query->whereDate($this->table . '.published_to', '>=', $today);
                    })
                        ->orWhere($this->table . '.published_to', '0000-00-00 00:00:00');
                });

            return $query;
        }

创建的SQL语句是正确的，

select * from `brshop_docs_learning_posts` 
inner join `brshop_docs_learning_series` on `brshop_docs_learning_posts`.`learning_serie_id` = `brshop_docs_learning_series`.`id` 
and `brshop_docs_learning_series`.`published` = ? 
and date(`brshop_docs_learning_series`.`published_from`) <= ? 
and ((date(`brshop_docs_learning_series`.`published_to`) >= ?) or `brshop_docs_learning_series`.`published_to` = ?) 
    where `brshop_docs_learning_posts`.`visible` = ? 
    and `brshop_docs_learning_posts`.`active` = ? 
    and `brshop_docs_learning_posts`.`published` = ? 
    and `brshop_docs_learning_posts`.`published_from` <= ? 
    and ((date(`brshop_docs_learning_posts`.`published_to`) >= ?) or `brshop_docs_learning_posts`.`published_to` = ?) 
    and `brshop_docs_learning_posts`.`deleted_at` is null

我认为，集合的合并是错误的。例如，这个集合中的每个帖子都有该系列的ID。因此，存在于系列表和posts表中的每一列都具有系列表的值。

感谢您的帮助;-)

马库斯

Answer 1

我用whereHas()解决了这个问题

    public function scopePublishedRestriction($query)
    {
        $date = Carbon::today();
        $today = date('Y-m-d', $date->getTimestamp());

        $query->accessible()
            ->whereHas('serie', function($query) use($today){
                $query->where('learning_series.published', 1)
                    ->whereDate('learning_series.published_from', '<=', $today)
                    ->where(function ($query) use ($today) {
                        $query->orWhere(function ($query) use ($today) {
                            $query->whereDate('learning_series.published_to', '>=', $today);
                        })
                            ->orWhere('learning_series.published_to', '0000-00-00 00:00:00');
                    });
            })
            ->where($this->table . '.published', 1)
            ->where($this->table . '.published_from', '<=', $today)
            ->where(function ($query) use ($today) {
                $query->orWhere(function ($query) use ($today) {
                    $query->whereDate($this->table . '.published_to', '>=', $today);
                })
                    ->orWhere($this->table . '.published_to', '0000-00-00 00:00:00');
            });

        return $query;
    }