Question

0

147

Vistas

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 Respuestas

Responde la pregunta

0

Responde la pregunta

Encuentra empleos 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

147

MySQL Query doesn't check for condition

3 Respuestas

0

0

0

Encuentra empleos remotos

Andres GPT