msassa

Querying related models

Posted 2 years ago by msassa

I have the following problem: I need to select users who meet these conditions:

  • be active
  • belong to a particular group
  • belong to a specific category

And here are the two conditions that are giving me problems:

1# that are within a radius of 200kms

2# that the category is not overdue

For the 1 # each user has a related hasMany zone (Model Zone) where has lat and lng

For the 2 # each user has a relationship with the category, and that relationship has a start date and a duration

Today I am doing it in 2 parts, first I select all the users who fulfill the first conditions, And then I make a foreach checking the other two:

$filterSellerByCat = function ($q) use ($category)
{
    $q->where('category_id', '=', $category);
};

$sellers = User::with(['groups', 'zones', 'categories' => $filterSellerByCat])
            ->where('activated', '=', '1')
            ->whereHas('groups', function($q){
                    $q->where('id', '=', '3');
                })
            ->whereHas('categories', $filterSellerByCat)
            ->get();


foreach ($sellers as $seller) {
    if($seller->zones[0]->distance()<200 and $seller->categories[0]->pivot->created_at > Carbon::now()->subMonths($seller->categories[0]->pivot->duration)){
        # MAKE SOME STUFF
    }
}

In the Zone model, I created a distance method, which returns the distance from a given point and that zone:

public function distance()
{
    $sql = DB::raw("SELECT ((6378 * acos( cos( radians(POINT_LAT) ) * cos( radians( users_zones.lat ) ) * cos( radians( users_zones.lng) - radians(POINT_LNG) ) + sin( radians(POINT_LAT) ) * sin( radians( users_zones.lat ) ) ) ) ) AS distance FROM users_zones WHERE users_zones.id = {$this->id}");
    $distance = DB::select($sql);
    return $distance[0]->distance;
}

Obviously is not the right way because when you have 10000 users that meet the first condition, the foreach it will take too long and consume too much resources.

Someone could suggest me a better solution. I was trying to do everything in the first query ($sellers), but I have not succeeded. Thank you very much

Please sign in or create an account to participate in this conversation.