Inverse of "Has Many Through"

I have three tables set up like so:

payments
 - id 
 - booking_id

bookings
 - id
 - property_id

properties
 - id

I need to be able to access properties from payments, so that each individual payment can be filtered by a property. E.g. $200 by John Doe for an apartment in New York.

Payment model:

public function booking()
{
        return $this->belongsTo(Booking::class);
}
// named location to avoid confusion
public function location()
{
        return $this->booking->property;
}

Booking model:

public function property()
{
        return $this->belongsTo(Property::class);
}

It is of my understanding that the other way around (accessing payments from properties) could be done with hasManyThrough.

But I can't do that the opposite way, right? When trying to access location() method I'm getting the error "Trying to get property 'property' of non-object".

My query looks like this:

return $query->whereHas('location', function ($query) use ($value) {
                $query->where('id', $value);
            });

Any help appreciated!

cwhite

3 years ago

Level 19

@zenith707

There's two ways to go about it:

You can use the staudenmeir/belongs-to-through package.
You can define hasOneThrough in reverse order.

For 2) you would need to define the relationship as follows:

// Payments
public function property(): HasOneThrough
{
        return $this->hasOneThrough(
            Property::class,
            Booking::class,
            'id',
            'id',
            'booking_id',
            'property_id',
        );
}

works the same for HasManyThrough as well.

Also, you can clean up the query:

return $query->whereRelation('property', 'id', $value);

2 likes

proxymurder

9 months ago

Level 1

Hello, I dont know if any one has had this issue before, seems like this thread is almost two years old... but I declared a relationship how the latest comment sugested based on the mysql query errors; it looks like...

    public function user()
    {
        return $this->hasOneThrough(
            User::class,
            Order::class,
            'id',           // Foreign key on the cars table (car_id in owners)
            'id',           // Foreign key on the cars table (links to mechanics)
            'order_id',     // Local key on the owners table
            'user_id'
        );;
    }

How ever im getting error;

                                                                              QueryException   
  SQLSTATE[HY000]: General error: 1 ambiguous column name: id (Connection: sqlite, SQL: select * from "order_details" where exists (select * from "users" inner join "orders" on "orders"."user_id" = "users"."id" where "order_details"."order_id" = "orders"."id" and "id" = 01980f47-b3de-7305-9ce0-5049b11c5bf2 and "id" = 01980f47-b3de-7305-9ce0-5049b11c5bf2))

query seems to be good but dont know how to address the issue with the ambigous column id

Glukinho

9 months ago

Level 30

@proxymurder what is your tables structure?

You are enumerating keys in wrong order, the right seems to be:

User::class,
Order::class,
'user_id',
'order_id',
'id',
'id'

If your tables/columns/models are named conventional way, keys defining is unnecessary and relation will work with defined model class names:

return $this->hasOneThrough(User::class, Order::class);

https://laravel.com/docs/12.x/eloquent-relationships#has-one-through-key-conventions

proxymurder

9 months ago

Level 1

funny enough if I write my scope as:

     Models\OrderDetail::addGlobalScope(function (Builder $q) {
            $q->whereHas('user', function (Builder $q) {
            		$q->where( $q->qualifyColumn('id'), '=', Auth::id());
        		});
        });

I still get error:

QueryException [HY000]: General error: 1 ambiguous column name: id (Connection: sqlite, SQL: select * from "order_details" where exists (select * from "users" inner join "orders" on "orders"."user_id" = "users"."id" where "order_details"."order_id" = "orders"."id" and "users"."id" = 01980f66-2009-72b5-b3ea-3d5a1162c040 and "id" = 01980f66-2009-72b5-b3ea-3d5a1162c040))

Please or to participate in this conversation.