in my Laravel app I have 3 tables : users, documents and type_documents, the user have multiple documents and document have one type_document 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 : I use Laravel version 8 Answer Have

how join laravel tables to get non intersected data

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|

​x
 
| 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);

 
$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

Answer

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// 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// 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();

 
<?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!

Advertisement

Answer