Question

0

146

Views

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 answers

Answer question

0

Answer question

Find remote jobs

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

146

MySQL Query doesn't check for condition

3 answers

0

0

0

Find remote jobs

Andres GPT