skip to Main Content

in my Laravel app I have 3 tables : users, documents and type_documents, the user have multiple documents and document have one type_document

| Documents       | 
| --------        |
| id              | 
| file            | 
| type_document_id| 
| user_id         | 

| type_documents | 
| --------       |
| id             | 
| name           | 

| users          | 
| --------       |
| id             | 
| name           | 
| type_document_d| 

I want select the types that are not used in documents table for the current user with eloquent
I try with this, but it give me the used type_documents :

$document_types = TypeDocument::leftJoin('docments' , function ($join) {
                    $join->on('type_documents.id', '=', 'douments.type_document_id')
                         ->where('documents.user_id', auth()->id());
                })
                ->applyFilters($request->all())
                ->latest()
                ->paginateData($limit);

I use Laravel version 8

3

Answers


  1. Check this solution

    TypeDocument::whereNotIn('id', function($query){
    
        $query->select('type_document_id')
        ->from(with(new Documents)->getTable())
        ->where('type_document_id', $query->id)
        ->where('user_id', auth()->id());
            
    })->get();
    
    Login or Signup to reply.
  2. Have you tried whereNotExists() or whereNotIn() instead of leftJoin()?

    eg:

    <?php
    // whereNotIn
    $types = TypeDocument::whereNotIn('id', function($query) {
        $query->select('type_document_id')
              ->from('documents')
              ->where('user_id', auth()->id);
    })->get();
    
    <?php
    // whereNotExists
    $types = TypeDocument::whereNotExists(function($query) {
        $query->select(DB::raw(1))
              ->from('documents')
              ->whereRaw('type_documents.id = documents.type_document_id')
              ->where('user_id', '=', Auth::user()->id);
    })->get();
    
    <?php
    // leftJoin
    $types = TypeDocument::leftJoin('documents', function($join) {
        $join->on('type_documents.id', '=', 'documents.type_document_id')
             ->where('user_id', '=', Auth::user()->id);
    })->whereNull('documents.type_document_id')->get();
    

    try any of these!

    Login or Signup to reply.
  3. Using Eloquent relation defined between TypeDocument::class and User::class like this:

    TypeDocument.php

    public function users()
    {
        return $this->belongsToMany(User::class, 'documents', 'type_document_id', 'user_id')->withPivot('file');
    }
    

    The you can easily get the type document not linked to a specific user like this

    TypeDocument::whereDoesntHave('users', function($userQuery) {
            $userQuery->where('users.id', '=', auth()->id());
        })  
        ->applyFilters($request->all())
        ->latest()
        ->paginateData($limit);
    

    ————–EDIT————–

    IF you want to use two step relation, you can define it like this:

    TypeDocument.php

    public function documents()
    {
        return $this->hasMany(Document::class);
    }
    

    Document.php

    public function user()
    {
        return $this->belongsTo(User::class);
    }
    

    And use the relations to query it like this:

    TypeDocument::whereDoesntHave('documents.user', function($userQuery) {
            $userQuery->where('users.id', '=', auth()->id());
        })  
        ->applyFilters($request->all())
        ->latest()
        ->paginateData($limit);
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search