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

Bosstone's avatar

Show everything from 3 tables where not in 1 Table

Hi Folks,

sorry for that silly title. I try to explain what I want to achieve:

I am reprogramming a Voting Website. The user is uploading Projects, the judge is voting on them. As there are several judges, I have a "help table: Table Count" where - after a judge has voted on a project - his user_id and the project_id will be saved.

I have 4 tables

Table = User (with id)
Table = Project (with id and user_id) (the Project Table is connected via user_id to Table User)
Table = Image (with id, project_id) (there are the images stored - connected via project_id to Project)
Table = Count (with id, project_id, user_id) (this is the helper Table - connected to Project and User via project_id and user_id)

Class Count

class Count extends Model
{
  public function project()
    {
        return $this->belongsTo(\App\Project::class, 'project_id');
    }

    public function user()
      {
          return $this->belongsTo(\App\User::class, 'user_id');
      }
}

Class Project:

public function images()
    {
        return $this->hasMany(\App\Image::class, 'project_id');
    }

    public function user()
    {
        return $this->belongsTo(\App\User::class, 'user_id');
    }

    public function cat()
      {
          return $this->belongsTo(\App\Cat::class, 'cat_id');
      }

Class Image:

public function project()
    {
        return $this->belongsTo(\App\Project::class, 'project_id');
    }

Class User:

public function projects()
      {
          return $this->hasMany(\App\Project::class, 'user_id');
      }

I know, I can show all the projects (with the connected images) like this (in this case, all projects with stat = 2 will be shown):

$projects = Project::with(['images' => function($query){
        $query->where('state' , 0 );
      }])
      ->where('stat', '=', '2')
      ->whereHas('user', function ($query) use ($user) {
                        $query->where('id', '=', $user->id);

                    })->get();

What I want to achieve in the end is as follows:

I want to show all the projects (and connected images) from Table: Project where the combination of user_id and project_id is not in table Count

Can anyone advice me how to solve this issue?

Kind Regards,

Stefan

0 likes
3 replies
burlresearch's avatar
Level 40

It's a little confusing what you mean by "combination of user_id and project_id", but here is an Eloquent query that might get you started:

$pids = Count::pluck('project_id');
$uids = Count::pluck('user_id');

Project::whereNotIn('id', $pids)
    ->whereNotIn('user_id', $uids)
    ->with('images')
    ->get();

How are these results for what you're looking for?

1 like
Vilfago's avatar

It could be easier to consider your "count table" as the pivot table in a many-to-many relationship between projects and user.

https://laravel.com/docs/5.6/eloquent-relationships#many-to-many

And then, you just have to querying relationship absence with whereDoesntHave https://laravel.com/docs/5.6/eloquent-relationships#querying-relationship-absence

Something like :

$projects = App\Project::whereDoesntHave('counts', function ($query) use($user_id) {
    $query->where('user_id', '=', $user_id);
})->with('images')
->get();

Please or to participate in this conversation.