Relationship help

Published 1 week ago by madsynn

I'm using L5 and I'm trying to figure out how to do the relationships for the following tables any help would be appreciated.

clinics locations zipcodes

clinics are over a list of zip codes

locations are attached to some of the zip codes assigned to the clinic but not all.

clinic 1 has 10 zip codes assigned to it. clinic 1 has 3 locations 1 locations are attached to 3 of the zipcodes attached to the clinic zip codes 3 out of 10 assigned zip codes.

Thanks in advance for your help.

Best Answer (As Selected By madsynn)
robrogers3

@madsynn

yep. we are inline. I was wondering about that 'no zipcode' thing you had above.

@corban we mostly line up.

and the key is being able to search for a clinic via zipcodes as corban notes.

Location is easy:

class Location
{
  public function clinic()
  {
    return $this->belongsTo(Clinic::class);
  }

  //or see optional
  public function zipcodes()
  {
    return $this->hasMany(Zipcode::class);
  }
}

Zipcode You can leverage hasManyThrough to get the clinics that are in a locations zipcodes.

class Zipcode
{
  public function location()
  {
    return $this->belongsTo(Location::class);
  }

  //A clinic does not have a direct connection to clinics but Eloquent can 
  //Do that work for you via hasManyThrough
  public function clinics()
  {
    return $this->hasManyThrough(Clinic::class, Location::class);
  }

}

as in:

    $foundClinics = Zipcode::with('clinics')->whereIn('zip', [3232,323,3223])->get()->transform(function($zip) {return $zip->clinics;})->all();
        

Clinic can go a few ways:

you can have the inverse of the above to get the contained zipcodes.


class Clinic
{
  public function locations()
  {
    return $this->hasMany(Location::class);
  }

  //or see optional
  public function zipcodes()
  {
    return $this->hasMany(Zipcode::class);
  }

  public function locationZipcodes()
  {
    return $this->hasManyThrough(Zipcode::class, Location::class);
  }

}

NOTE: that what we have here means that you CANNOT directly associate a zipcode with a clinic. i.e. there is no clinic_id on the zipcode model.

if that's something you want let us know.

robrogers3

I wish you listed things out clearer.

like: Tables:

  • clinics
  • locations
  • zipcodes

and

  • clinic1 has 10 zipcodes assigned to it,
  • clinic1 has 3 locations
  • 1 locations (location1 singular??) are associated with 3 zipcodes
  • attached to the clinic (1 or X ?) zip codes 3 out of 10 assigned zip codes. (i'm not sure what this means. clinic X are associated with 3 (out of 10) zipcodes?) <-- you need clarify this!

Also, if you have any schemas you should post them.

Anywho, from what I can understand.

So you should have 3 models here: Clinic Location ZipCode

A Location hasMany(ZipCode) <-- this means A location likely contains one or more zipcodes

A ZipCode belongsTo(locations) <-- this means, a zipcode is associated with ONE location.

A clinic hasMany(Locations) <-- this means, a clinic can service multiple locations A Clinic hasMany(Zipcodes) <-- this means a clinic can service multiple zipcodes

A Zipcode belongsTo (Clinic) <-- this means only one ZipCode can be associated with a clinic. (attached to)

So if this is correct, then it looks like this, yeah?

CLINIC 1 Location 1 Zipcode 1 Zipcode 2 Location 2 Zipcode3 Location 3 'No ZipCode'

which means: A Clinic services one or many locations. A Location can comprise of many zipcodes

Finally, note that just because say a Location hasMany zipcodes, doesn't mean it must have at least one. there could be none.

Does this sound right?

Let me know.

madsynn

@robrogers3

You almost guessed it right sorry for not being clear enough. The part that is a little off is this line: "A Zipcode belongsTo (Clinic) <-- this means only one ZipCode can be associated with a clinic. (attached to)"

A Zipcode belongsTo clinic but is attached to one Location.

As for the schema, I have not created them yet because I'm trying to determine the best way to structure them.

Here is a better example:

The owner Steve has a business called steves family health clinic. So the clinic is Steves family health clinic. Now The business = clinic services 20 zip codes in that area before he goes into someone else's territory. The Clinic has 3 location in 3/20 zipcodes.

so id a person searching for service in their own zip code searches for a clinic in one of the 20 zip codes it will pull up the clinics 3 locations even if it's not in the zip codes with no location attached.

Hope this clears it up a little.

What I have built so far you can check out here.

http://www.laravelsd.com/share/OMviLu

Thanks

madsynn
madsynn
1 day ago (5,910 XP)

@robrogers3 you still around?

robrogers3

yeah. humm. I thought I replied to your last reply. weird. cause I had it all written out.

lemme see if I can find it.

robrogers3

Ok.

Some more clarifications:

you say:

so id a person searching for service in their own zip code searches for a clinic in one of the 20 zip codes it will pull up the clinics 3 locations even if it's not in the zip codes with no location attached.

please rewrite this it's very confusing: "even if it's not in the zip codes with no location attached." How can clinic be found if it doesn't have an associated zipcode?

But check this out: i think the formatting screwed this up. The idea is the Clinic services Locations and Locations are comprised (usually) of zipcodes.

CLINIC 1
  Location 1
    Zipcode 1 Zipcode 2
  Location 2
     Zipcode3
  Location 3
     No ZipCode
    
CLINIC 2
  Location 22
    Zipcode 21  Zipcode 22
  Location 23
    Zipcode 23
  Location 24
     No Zipcode

This means, you could say, that a clinic is a SuperSet of Locations, and Locations 'can be' a SuperSet of Locations

Sound about right?

Note: given the idea of searching by zipcodes, if there are no zipcodes associated to a Location which is associated with a clinic. That's weird.

Also one more question: Can a clinic be in more than one Location?

madsynn

@robrogers3

The clinics can have many zip codes which are comprised of all the zip codes of the area they cover.

Locations will only have 1 zip code because locations are physical addresses for service from that clinic.

CLINIC 1
Covers: Zipcodes 1-20
With 3 locations:
    Location 1
        has a physical address and Zipcode 1 
    Location 2
            has a physical address and Zipcode2
    Location 3
            has a physical address and ZipCode3
    
CLINIC 2
Covers: Zipcodes 20-50
With 3 locations:
    Location 22 
            has a physical address and Zipcode 22
    Location 23
            has a physical address and Zipcode 23
    Location 24
            has a physical address and  Zipcode 24

view schema screenshot here: https://s.nimbus.everhelper.me/share/1260032/79noieuxsn6hzbehh11p

So far my relationships are as follows.

clinic hasMany zipcodes clinic hasMany locations location belongsTo clinic location hasOne zipcode zipcodes belongToMany clinics zipcode belongsTo location

Please let me know your thoughts.

Thank you

Corban
Corban
7 hours ago (10,350 XP)

So if I understand correctly: You have a clinic, let's call it Clinic 1.

  • Clinic 1 cover a lot of residential areas which are defined by their ZIP code.
  • Each ZIP code belongs to only one clinic, so if 55222 code is attached to Clinic 1 it can't be attached to any other clinic.
  • Each ZIP code is part of the ZIP codes of a location.
  • That means that ZIP code 55222 is part of LaracastLand, which divides it's are in ZIP codes.

With that in mind I believe you want to know how to retrieve which clinics belong to LaracastLand and in which location is clinic 1 located, right?

Then I think the approach should be like this:

  • A clinic hasManyZIP codes.
  • A ZIP code belongsTo a clinic, but a ZIP code also belongsTo a Location.
  • A location hasManyZIP codes, *but *, because this relationship, also has many clinics, which are related using a hasManyThroughrelationship.

Using that relationship you can retrieve the locations of a clinic using ZIP codes. Let me know if that suits your needs. Check hasManyThrough relations from the documentation

robrogers3

@madsynn

yep. we are inline. I was wondering about that 'no zipcode' thing you had above.

@corban we mostly line up.

and the key is being able to search for a clinic via zipcodes as corban notes.

Location is easy:

class Location
{
  public function clinic()
  {
    return $this->belongsTo(Clinic::class);
  }

  //or see optional
  public function zipcodes()
  {
    return $this->hasMany(Zipcode::class);
  }
}

Zipcode You can leverage hasManyThrough to get the clinics that are in a locations zipcodes.

class Zipcode
{
  public function location()
  {
    return $this->belongsTo(Location::class);
  }

  //A clinic does not have a direct connection to clinics but Eloquent can 
  //Do that work for you via hasManyThrough
  public function clinics()
  {
    return $this->hasManyThrough(Clinic::class, Location::class);
  }

}

as in:

    $foundClinics = Zipcode::with('clinics')->whereIn('zip', [3232,323,3223])->get()->transform(function($zip) {return $zip->clinics;})->all();
        

Clinic can go a few ways:

you can have the inverse of the above to get the contained zipcodes.


class Clinic
{
  public function locations()
  {
    return $this->hasMany(Location::class);
  }

  //or see optional
  public function zipcodes()
  {
    return $this->hasMany(Zipcode::class);
  }

  public function locationZipcodes()
  {
    return $this->hasManyThrough(Zipcode::class, Location::class);
  }

}

NOTE: that what we have here means that you CANNOT directly associate a zipcode with a clinic. i.e. there is no clinic_id on the zipcode model.

if that's something you want let us know.

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