Laravel: Order a model by "Has One Of Many" relationship-CodePudding

I have a customer model that has many contacts. I defined a relationship to get the most recent contact of the customer using the "Has One Of Many" relationship in Laravel 8:

Models

class Customer extends Model
{
    use HasFactory;

    public function contacts() 
    {
        return $this->hasMany(Contact::class);
    }

    public function latestContact()
    {
        return $this->hasOne(Contact::class)->ofMany('contacted_at', 'max')->withDefault();
    }
}

class Contact extends Model
{
    use HasFactory;

    protected $casts = [
        'contacted_at' => 'datetime',
    ];

    public function customer()
    {
        return $this->belongsTo(Customer::class);
    }
}

Migration (contact model)

class CreateContactsTable extends Migration
{
    public function up()
    {
        Schema::create('contacts', function (Blueprint $table) {
            $table->id();
            $table->timestamps();
            $table->softDeletes();

            $table->foreignID('customer_id');
            $table->string('type');
            $table->dateTime('contacted_at');
        });
    }
}

In my view, I want to show all customers and order them by their latest contact. However, I can't figure out how to do that.

I tried to achieve it via the join method but then I obviously get various entries per customer.

$query = Customer::select('customers.*', 'contacts.contacted_at as contacted_at')
    ->join('contacts', 'customers.id', '=', 'contacts.customer_id')
    ->orderby('contacts.contacted_at')
    ->with('latestContact')

Knowing Laravel there must be a nice way or helper to achieve this. Any ideas?

CodePudding user response：

I think the cleanest way to do this is by using a subquery join:

$latestContacts = Contact::select('customer_id',DB::raw('max(contacted_at) as latest_contact'))->groupBy('customer_id');

$query = Customer::select('customers.*', 'latest_contacts.latest_contact')
         ->joinSub($latestContacts, 'latest_contacts', function ($join){
            $join->on([['customer.id', 'latest_contacts.customer_id']]);
        })
        ->orderBy('latest_contacts.latest_contact')
        ->get();

More info: https://laravel.com/docs/8.x/queries#subquery-joins

CodePudding user response：

I suspect there is an issue with your migration, the foreign key constraint is defined like this:

Check the documentation: https://laravel.com/docs/8.x/migrations#foreign-key-constraints

Method 1: define foreign key constraint

public function up()
{        
    Schema::create('contacts', function (Blueprint $table) {
        $table->id();            
        $table->foreignId('consumer_id')->constrained();          
                    
        $table->string('type');
        $table->dateTime('contacted_at');
        $table->timestamps();
        $table->softDeletes();
    });    
}

Method 2: define foreign key constraint

public function up()
{        
    Schema::create('contacts', function (Blueprint $table) {
        $table->id();                        
        $table->unsignedBigInteger('customer_id');                        
        $table->foreign('customer_id')->references('id')->on('customers');
                    
        $table->string('type');
        $table->dateTime('contacted_at');
        $table->timestamps();
        $table->softDeletes();
    });    
}