Laravel 10 ORM: Fetching Promoted Accounts with History Count Less than Plan Target

I have three database tables in my project: promoted_account_plans, promoted_accounts, and promoted_account_histories. The structure and relationships are as follows:

Table: promoted_account_plans (id, name, target)

id (Primary Key) name target

Table: promoted_accounts (id, name, promoted_account_plan_id)

id (Primary Key) name promoted_account_plan_id (Foreign Key referencing promoted_account_plans.id)

Table: promoted_account_histories (id, name, promoted_account_id)

id (Primary Key) name promoted_account_id (Foreign Key referencing promoted_accounts.id)

The relationships between the tables are as follows:

PromotedAccount belongs to PromotedAccountPlan. PromotedAccount has many PromotedAccountHistory records.

I need help with Laravel ORM to fetch data from the PromotedAccount table where the count of related PromotedAccountHistory records is less than the PromotedAccountPlan target.

krisi_gjika

2 years ago

Level 14

Something like:

PromotedAccount::query()
    ->select('promoted_accounts.*')
	->join('promoted_account_plans as pap', 'pap.id', '=', 'promoted_accounts.promoted_account_plan_id')
    ->where('pap.target', '>=', function (Builder $query) {
        $query->selectRaw('count(`id`)')
           ->from('promoted_account_histories')
           ->whereColumn('promoted_account_histories.promoted_account_id', 'promoted_accounts.id')
		   ->limit(1)
    });

Please or to participate in this conversation.