How to use left join limit 1 in Laravel?-CodePudding

I have a model:

class DistributorContacts extends Model
{
    public function contacts()
    {
        return $this->belongsToMany(Contacts::class);
    }
}

I use this like:

$distributors = DistributorContacts::with("contracts")->whereIn("user_id", $this->id)->get();

Problem is that I need to get only one row from "contracts" ordered by date.

How to do that?

CodePudding user response：

Add the query to a function.

$distributors = DistributorContacts::with([
    'contacts' => function($query) {
        $query->orderBy('created_at', 'DESC')->limit(1);
    }
])->whereIn("user_id", $this->id)->get();

CodePudding user response：

If you need to get only one row from "contracts" ordered by date.

You can use has-one-of-many relation. So

    class DistributorContacts extends Model
{
    /**
     * Get the DistributorContact's most recent Contacts.
     */
    public function latestContacts()
    {
        return $this->belongsToMany(Contacts::class)->latestOfMany();
    }

    /**
     * Get the DistributorContact's oldest Contacts.
     */
    public function oldestContacts()
    {
        return $this->belongsToMany(Contacts::class)->oldestOfMany();
    }
}

So in while Quering you can do something like this.

For Latest Contact

$distributors = DistributorContacts::with("latestContacts")->whereIn("user_id", $this->id)->get();

For Oldest Contact

$distributors = DistributorContacts::with("oldestContacts")->whereIn("user_id", $this->id)->get();