Back

SubQuery for Related Model


I am trying to pull all Profiles only if there is a record in the ProfileAttributes table and as a test i am smilpy hard-coding the id so my code is in profile_controller:

$q = Profile::query();

    	$attribute_id = '5e65e9d4-ee60-11e6-994d-40167eb29fbd'; // blond hair

    	$q->searchHairColor('5e65e9d4-ee60-11e6-994d-40167eb29fbd');


		$data = $q
		->where('completed', true)
		->with(['State', 'City', 'Country', 'Agency'])
		->orderBy('created_at', 'desc')
		->orderBy('state_id', 'asc')
		->orderBy('name', 'asc')
		->paginate(24); // ->keyBy('id')

    	return $this->setPaginationObj($data);

Profile Model Scope

public function scopeSearchHairColor($query, $attribute_id)
  {
   
      $query->with(['ProfileAttributes'])
        ->whereIn('attribute_id', function ($q) use ($attribute_id) {
          $q->where('profile_attributes.attribute_id', '=', "$attribute_id");
      });
  }

This results in the following error:

SQLSTATE[HY000]: General error: 1096 No tables used (SQL: select count(*) as aggregate from profiles where attribute_id in (select * where profile_attributes.attribute_id = 5e65e9d4-ee60-11e6-994d-40167eb29fbd) and completed = 1)

Any help woould be greatly appreciated,

Thanks

Dave

eaponiente replied 5 months ago

Have you tried eager loading with constraint?

$attribute_id = '5e65e9d4-ee60-11e6-994d-40167eb29fbd';

$q = Profile::with(['ProfileAttributes' => function($q) use($attribute_id){
      $q->where('attribute_id', $attribute_id)
}])->where('completed', true)
    ->with(['State', 'City', 'Country', 'Agency'])
    ->orderBy('created_at', 'desc')
    ->orderBy('state_id', 'asc')
    ->orderBy('name', 'asc')
    ->paginate(24)
denjaland replied 5 months ago

I think you need to build your subquery. $q is new there...

So try this:

public function scopeSearchHairColor($query, $attribute_id) {
  $query->with(['ProfileAttributes'])
        ->whereIn('attribute_id', function ($q) use ($attribute_id) {
          $q->select('id')
            ->from('profile_attributes')
            ->where('attribute_id', '=', "$attribute_id");
      });
  }

Didn't test it, but since you're doing whereIn, the $query that's being passed, is probably just a new instance of the query builder. It's - as you say - a subquery, hence has no relation to the outer query imo.


Sign in to participate in this thread!



We'd like to thank these amazing companies for supporting us