join a table in two columns due to polymorphic relation

I have the following structure:

Buro->hasOne->Address->hasOne->City->hasOne->State Buro->hasOne->Category

and

Order->hasOne->Address->hasOne->City->hasOne->State Order->hasOne->Category

and

Category->hasMany->Orders and Category->hasMany->Buros

I have a "One-To-Many" polymorphic relation between addresses in one side and Buro/Orders in the other side. There is just One address for a buro and just one adddress for an order, therefor I couls use [0] to get the first the first and only one element in a collection.

Address class

class Address extends Model
{
...

    public function addressable()
    {
        return $this->morphTo();
    }
...
}

Büro class

class Buro extends Model
{
....
    public function addressable()
    {
        return $this->morphMany('App\Address', 'addressable');
    }
...
}

Order class

class order extends Model
{
....
    public function addressable()
    {
        return $this->morphMany('App\Address', 'addressable');
    }
...
}

Now I am building a form in the frontend with a SELECT field in order to select just from the Buros that operate in the same State where the order was placed.

Using ELOQUENT I did this:

$all_bueros = Buero::where('category_id', $order->category_id)
                    ->where('title', $order->client->addressable[0]->city->state->title) // <- Look at this "title" column name
                    ->pluck('title', 'id')->all();

The problem here is that the "Title" column name is not in "Buros" table but three tables away. Exactly in: buros.addresses.city.state.title

I have tried these syntaxes but no one worked:

->where('buros.addresses.city.state.title', $order->client->addressable[0]->city->state->title)   // as object notation

and

->where('buros->addressable[0]->cities->state->title', $order->client->addressable[0]->city->state->title)  // using the relation

I used the query builder but there is even more messy:

$buros = DB::table('buros')
                    ->join('categories', 'categories.id', '=', $order->category_id)
                    ->join('addresses', 'addresses.addressable_id', '=', $order->id, 'AND','addresses', 'addresses.addressable_type', '=', 'buros') //two points to join the table
                    ->join('cities', 'addresses.city_id', '=', 'cities.id')
                    ->join('states', 'cities.states', '=', 'states.id')
                    ->select('buros.title', 'buros.id')
                    ->where('states.title', '=', $order->client->addressable[0]->city->states->title)
                    ->get();

My problem is how to join a table in two points since there is a polymorphic relation. Because I need to join the Address Table in the "Buro-column" (addressable_type) and in the Buro-id column (addressable_id)

Any help will be appretiate

from Newest questions tagged laravel-5 - Stack Overflow https://ift.tt/2J2FePP
via IFTTT