Question

0

154

Visualizações

MySQL Query doesn't check for condition

I have a query like this:

    select * from `research_purchases` left join `company_research_articles`
 on `research_purchases`.`researchable_id` = `company_research_articles`.`id` 
and `research_purchases`.`researchable_type` = 'Modules\Analystsweb\Entities
\CompanyResearchArticle'

The research_purchases table structure is like this:

It is not filtering the "Modules\Analystsweb\Entities\CompanyResearchArticle" part and giving me the entire result. This is the part where I am stuck.

Originally I want to run a Laravel query. The query is like this:

$sales = DB::table('research_purchases')->select(DB::raw('count(research_purchases.id) as sales_count, research_purchases.created_at'))->leftJoin('company_research_articles', function ($join) {
                                        $join->on('research_purchases.researchable_id', '=', 'company_research_articles.id')
                                            ->where('research_purchases.researchable_type', '=', 'Modules\Analystsweb\Entities\CompanyResearchArticle');
                                    })
                                    ->where('research_purchases.created_at', '>', Carbon::now()->subMonths(11))
                                    ->groupBy(DB::raw("MONTH(research_purchases.created_at)"))
                                    ->get();

Any suggestion would be appreciated. Thank you.

over 4 years ago · Santiago Trujillo

3 Respostas

Responde à pergunta

0

Responde à pergunta

Encontrar trabalhos remotos

Answer 1 · 2022-05-26T17:08:36.925Z

ohh dear you should put this in where condition

if you want left join all data on where condition of right table then you put it with join on

select * from `research_purchases` left join `company_research_articles`
 on `research_purchases`.`researchable_id` = `company_research_articles`.`id` 

where  `research_purchases`.`researchable_type` = 'Modules\Analystsweb\Entities
\CompanyResearchArticle'

Answer 2 · 2022-05-26T17:08:36.934Z

I cannot post a comment yet, but don't you mean WHERE instead of AND in your query ?

Answer 3 · 2022-05-26T17:08:36.941Z

Please try whereColumn .

$sales = DB::table('research_purchases')->select(DB::raw('count(research_purchases.id) as sales_count, research_purchases.created_at'))->leftJoin('company_research_articles', function ($join) {
               $join->on('research_purchases.researchable_id', '=', 'company_research_articles.id');
        })->whereColumn([
                    ['research_purchases.researchable_type', '=', 'Modules\Analystsweb\Entities\CompanyResearchArticle'],
                    ['research_purchases.created_at', '>', Carbon::now()->subMonths(11)]
        ])->groupBy(DB::raw("MONTH(research_purchases.created_at)"))
                             ->get();

or both the where in side the closer

$sales = DB::table('research_purchases')->select(DB::raw('count(research_purchases.id) as sales_count, research_purchases.created_at'))->leftJoin('company_research_articles', function ($join) {
                                        $join->on('research_purchases.researchable_id', '=', 'company_research_articles.id')
                                        ->where(
                    'research_purchases.researchable_type', '=', 'Modules\Analystsweb\Entities\CompanyResearchArticle')
                             ->where('research_purchases.created_at', '>', Carbon::now()->subMonths(11))
                )
})->groupBy(DB::raw("MONTH(research_purchases.created_at)"))
                                    ->get();

0

154

MySQL Query doesn't check for condition

3 Respostas

0

0

0

Encontrar trabalhos remotos

Andres GPT