将原始查询连接更改为laravel雄辩 [英] Change raw query join into laravel Eloquent

问题描述

我有两个模型：

$modelMain = "SearchPages"; //table_name : search_pages
$modelSites = "Site"; // table_name : sites

我最初有一个下面的查询，只能从一个模型获得 $ modelMain ：

I initially had a below query for getting count from only one model $modelMain:

$records = $modelMain::
        select(DB::raw("COUNT(DISTINCT {$columnRecordedOn}) as records_count"))
        ->groupBy($columnRecordedOn, $columnSiteId)
        ->get();

现在，我需要加入第二个模型 $ modelSites whoese表名是网站，以便检查网站中的状态列，如果它的1。

Now, I need to join it with 2nd model $modelSites whoese table name is sites in order to check status column in sites if its 1.

我将查询修改为：

$records = $modelMain::
    select(DB::raw("COUNT(DISTINCT {$columnRecordedOn}) as records_count"))
    ->join('sites','search_pages.site_id','=','sites.site_id') //added this
    ->where('sites.status','=','1') // and this
    ->groupBy($columnRecordedOn, $columnSiteId)
    ->get();

所有工作正常，但您可以看到我正在使用表名网站直接在 join（）和 where（）而不是使用模型名称我相信

All working fine but as you can see I am using table name sites directly in join() and in where() instead I must use model name I believe.

如何将此查询转换为适当的Laravel雄辩？

How can I convert this query to proper Laravel Eloquent?

感谢任何帮助。 >

Thanks for any help.

推荐答案

基本上您可以使用with方法获取相关模型

Basically you can use "with" method to get related model

网站模型

class Site extends Eloquent {

    public function searchpages()
    {
    return $this->hasMany('SearchPage','site_id','site_id');
    }

}

SearchPage Model

SearchPage Model

class SearchPage extends Eloquent {

    public function site()
    {
    return $this->belongTo('Site','site_id','site_id');
    }

}

$ records = SearchPage :: with （'site'） - > get（）;

$records = SearchPage::with('site')->get();

根据您的需要

$records = SearchPage::
       select(DB::raw("COUNT(DISTINCT {$columnRecordedOn}) as records_count"))
    ->whereHas('site' => function($q){
                                        $q->where('status','1');
                                    })
    ->groupBy($columnRecordedOn, $columnSiteId)
    ->get();

这篇关于将原始查询连接更改为laravel雄辩的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！