Using a scope with a relationship, but only on first() of related model?

It depends on how you define "first" customer. By default, mysql will return the rows in order of insertion.

So you could do something like this

$query->whereHas('user', function($q) use ($customer) {
        $q->where('name', 'LIKE', '%'.$customer.'%')->limit(1);
    }, 1);

@rickshawhobo I mean like how calling

$user = User::where('name', 'Bob')->first();

returns the first. Which I do believe is sorted by order of insertion into the database.

I tried the code you provided but for some reason that still returns any store where it has a user with the name of Bob, not a store where it's very first user has the name of Bob.

plucking the 'first in the database' is a really iffy way of doing it. The order of the records returned should not rely on the order in which they are stored.

Choose by created_at date in conjunction with a sort order, or by a flag in another column such as 'initial_customer'

The use case seems strange so I'm not sure how best to advise, but I think the flag approach is best because you can do a additional where clause.

    $query->whereHas('user', function($q) use ($customer) {
        $q->where('name', 'LIKE', '%'.$customer.'%')
        ->where('initial_customer',1);
    });

In my example I'm missing an orderBy clause, which would cause the sub-select to always return at least one Bob.

Something like this would work, assuming you want to return the first Bob by its auto incremented id

$query->whereHas('user', function($q) use ($customer) {
        $q->where('name', 'LIKE', '%'.$customer.'%')->orderBy('id')->limit(1);
    }, 1);

It's a weird use-case for sure. Why would you care if the first customer is named Bob? Maybe that was just example?

Actually strike that, an orderBy won't work either. Hmmm. I don't have a good solution for you then, sorry.

If I use created_at is there some way to pick the oldest one in the scope?

I like the flag idea but I can't implement it based on my actual use case and how dynamically the data changes. Thanks though!

For another example maybe a guest can make a reservation, this reservation has many service_dates, and each service_date has a 'room_number' column.

I'd like to return all reservations where the guests first service_date is in room_number 101. But what I'm getting back (and that I don't want) is all reservations where any of the service_dates were in room_number 101.

public function scopeFilterByRoomNumber($query, $room_number)
{
    $query->whereHas('service_dates', function($q) use ($room_number) {
        $q->where('room_numer', '=', '101');
    });
}

I don't want guests whose room_number was 101 on the 2nd night or 3rd night, just their first night. As an example.

Turns out I was able to do it with 2 scopes lol

scopeFirstCustomer($query)
{

    return $query->whereHas('user', function ($q) {
        $q->orderBy('id')->limit(1);
    }, 1);
}

scopeCustomerNamed($query, $name)
{
    return $query->whereHas('user', function ($q) use ($name) {
        $q->where('name', 'like', '%{$name}%');
    });
}

$stores = Store::firstCustomer()->customerNamed("Bob")->get();

This assumes you want to order by user's id which is not exactly a good idea. You might want do order by some relevant pivot data.