Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

johnvoncolln's avatar

Eloquent query comparing two related models

Let's say I have an order model with two address models, shippingAddress and billingAddress. I want to select all orders where the street_address in both shippingAddress and billingAddress are the same.

Order::whereHas('billingAddress',  function(Builder $query) {
		$query->where('street_address', function(Builder $query) {
					// raw statement?
}
}

stumbling on how to get the related shipping_address model

0 likes
15 replies
MohamedTammam's avatar

If I understood correctly. That might be your structure.

$order // The order model you have
$order->shippingAddress // Shipping address for the order
$order->billingAddress// Billing address for the order

And you want to get all orders that has the same street value in both shippingAddress and billingAddress

Then you can do

Order::whereHas('shippingAddress', fn($q) => $q->where('street_address', $order->shippingAddress->street_address)
	->whereHas('billingAddress', fn($q) => $q->where('street_address', $order->billingAddress->street_address)
	->get();
johnvoncolln's avatar

@MohamedTammam In your example, your first whereHas is on the Order model, which is correct, but your second whereHas is chained on the where statement within the whereHas on the shippingAddress model. You're trying to retrieve a billingAddress from the shippingAddress.

And also, where is $order coming from?

MohamedTammam's avatar

@johnvoncolln No, both whereHas conditions for the orders query.

The $order is coming from the assumption that I mentioned at the beginning of my answer.

johnvoncolln's avatar

@MohamedTammam This is a query to retrieve many models. We want to get ALL orders that have the same street address for billing and shipping - so there is no single $order to reference from

Tray2's avatar

Do you have a sigle address table, and you use foreign keys to handle the shipping and billing address?

If so you could just do

SELECT * 
FROM orders
WHERE billing_address_id = shipping_address_id;

Or you can add a generated column to the table that compares the two columns, and if the match give you a Y.

Then you can just

$orders = Order::where('same_billing_shipping', 'Y')->get();
johnvoncolln's avatar

@Tray2 shippingAddress and billingAddress are in the same table and do share parent_id, but I'm unable to add any columns to the DB. This is an older magento database that I'm building tools using laravel

Tray2's avatar

@johnvoncolln I would suggest moving the application to a fresh database with a updated schea, but that takes time to do, and is hard to sell to the ppl paying for your services.

johnvoncolln's avatar

@Tray2 Thanks - the Magento site is still active and this is a temporary situation that doesn't have to be great. I've about got @kevinbui 's suggestion working, but then my computer decided to turn off

kevinbui's avatar

If you store all addresses in a addresses table, that might be possible with two subquery selects:

return Order::addSelect(['shipping_street_address' => Address::select('street_address')
    ->whereColumn('order_id', 'orders.id')
    ->where('type', 'shipping')
    ->limit(1)
])
->addSelect(['billing_street_address' => Address::select('street_address')
    ->whereColumn('order_id', 'orders.id')
    ->where('type', 'billing')
    ->limit(1)
])
->whereColumn('shipping_street_address', 'billing_street_address')
->get();
johnvoncolln's avatar

@kevinbui This almost works, but ->whereColumn('shipping_street_address', 'billing_street_address') throws it off. These fields don't exist on the main model, so it can query them

kevinbui's avatar

@johnvoncolln hmm, what error message do you see? what about whereRaw or havingRaw?

->whereRaw('shipping_street_address = billing_street_address')

// Or
->havingRaw('shipping_street_address = billing_street_address')
johnvoncolln's avatar

@kevinbui it says those fields don't exist. I'm thinking for this that maybe a join is better. I've actually gone a different route, but still using your suggestion of the addSelect to show the data, but have it highlighted conditionally on the view.

kevinbui's avatar

@johnvoncolln Talking about joins, this is what I have in mind:

return Order::selectRaw('orders.*, shipping_addresses.street_address as shipping_street_address, billing_addresses.street_address as billing_street_address')
    ->join('addresses as shipping_addresses', function ($join) {
        return $join->on('addresses.order_id', '=', 'orders.id')
            ->where('type', '=', 'shipping');
    })
    ->join('addresses as billing_addresses', function ($join) {
        return $join->on('addresses.order_id', '=', 'orders.id')
            ->where('type', '=', 'billing');
    })
    ->whereRaw(DB::raw('shipping_street_address = billing_street_address'))
    ->get();

Please or to participate in this conversation.