DISTINCT in laravel ELOQUENT issue

Question

In my laravel application I have two tables called, apps and app_payments.I wanted to join the two tables and I did it as mentioned in the below laravel ELOQUENT. In the joined table, I have one column to store user ids (user_id). The following ELOQUENT returns me multiple rows for certain user

$sites=DB::table('app_payments')
        ->join('apps', 'app_payments.app_id', '=', 'apps.appId')
        ->select('app_payments.*', 'apps.*')
        ->where ('apps.appId','=',$id)
        ->get();
return view('payments.show',compact('sites'))
            ->with('i', (request()->input('page', 1) - 1) * 100);

But I only need to select one row for one (specific) user id, I know I might have to use DISTINCT there but, struggling to use it in this laravel ELOQUENT.

How can I modify above ELOQUENT to achieve that?

user_2289926 · Answer

The code you posted is using the Query Builder (DB::table('app_payments')->...), not Eloquent (AppPayment::where(...)->...->get())

What is your aim to get from those tables? Continuing doing it with the Query Builder directly or with Eloquent, expanding the Query Builder?

In Eloquent, you would setup the relations between app and payments in the Model:

public function app()
{
  $this->belongsTo(App::class, 'appId');
}
public function user()
{
  $this->belongsTo(User::class);
}

Something like

AppPayment::where('appId', $id)->with(['app', 'user'])->get()

would return all Payments with the corresponding App and User.

Of course, when you expect to only get one payment per user, this will not yet do the job. To me, that sounds like you better query the other way around, but that decision depends on what data you actually need.

For the last payment, this would be something like:

// App\Models\User.php
...
public function payments()
  return $this->hasMany(AppPayment::class)
    ->with(['app'])
    ->orderByDesc('created_at');
...

// query anywhere
$users = User::with(['payments', 'payment.app'])
  ->whereHas('payments', function (Builder $query) use($app_id){
    $query->where('app_id', $app_id);
  })
  ->get()

// latest payment:
foreach($users as $user){
  $latestPayment = $user->payments[0];
  ...
}

Code untested though... And it still needs a trick to only (!) get the latest payment of a specific app, I lost that point out of sight...

DISTINCT in laravel ELOQUENT issue

1 Answers