Get table records except the ones that has foreign key values in another table

Question

I have two tables:

goods:  id | name

imported_goods: id | good_id | amount

imported_goods has foreign key values from table goods, And what i'm trying to do is:

Display all goods records Except the records that has foreign key values in imported_goods.

Example (CSV):

goods

1,orange
2,apple
3,bannana
4,mango

imported_goods

1,1,20 kg
2,2,40 kg
3,3,60 kg

Expected Result:

4,mango

And all other records discarded

Good Model

class Good extends Model
{        
     /**
     * Get the Imported Goods for this Good.
     */
    public function imported_goods()
    {
        return $this->hasMany('App\Models\Imported_good','good_id');
    }
}

Imported_Good Model

class Imported_good extends Model
{
    /**
     * Get the Good info for this Imported Good.
     */
    public function good()
    {
        return $this->belongsTo('App\Models\Good','good_id');
    }
}

Show us some sample table data and the expected result too. ([mcve]) — jarlh, Mar 30 '20 at 14:01
Does this answer your question? [Find rows in A that don't have an associated row in B, where the FK is on B?](https://stackoverflow.com/questions/6190409/find-rows-in-a-that-dont-have-an-associated-row-in-b-where-the-fk-is-on-b) — Rob Streeting, Mar 30 '20 at 16:26
@Rob Streeting Yeah it does, But its answers are raw SQL queries where here i want it in eloquent relationship, Because i'm using Laravel. El_vanja Answer is great, But its not really benefiting from the Eloquent Engine and Nether that Question Thread. — Makdous, Mar 30 '20 at 21:49

score 1 · Answer 1 · answered Mar 30 '20 at 14:19

1

You can utilize a subquery:

SELECT * FROM X WHERE id NOT IN (SELECT x_id FROM Y);

Or you can perform a join:

SELECT X.* FROM X LEFT JOIN Y ON X.id = Y.x_id WHERE y.x_id IS NULL;

If you don't have to worry about performance, I'd suggest the first option (it's more readable and easier to understand). If you need to, consider that joins are generally faster than subqueries.

answered Mar 30 '20 at 14:19

El_Vanja

3,660
4
18
21

Is it possible using Eloquent Models? – Makdous Mar 30 '20 at 14:27
Of course. Just follow the syntax for [subqueries](https://stackoverflow.com/questions/27064678/how-to-create-a-subquery-using-laravel-eloquent) or [joins](https://laravel.com/docs/5.8/queries#joins). – El_Vanja Mar 30 '20 at 14:29

score 1 · Accepted Answer · answered Mar 30 '20 at 16:57

1

If you followed good practice and recommendation of naming convention between table names, model names, keys, fields etc (like here for example), you can do it just with:

// Parent.php model 
public function children()
{
    return $this->hasMany(Child::class);
}

// than somewhere in controller
$parents = Parent::doesntHave('children')->get();

This is generic example. If you share actual names and model classes code as much as table structure instead of some x/y placeholders, I'll be able to tell more for your particular case.

answered Mar 30 '20 at 16:57

Tpojka

6,996
2
29
39

I added real Table Names & Columns with their respected Models and relationships, I used **X** & **Y** just to make it simpler and not too complicated. – Makdous Mar 30 '20 at 22:01
No i don't have a valid way to test this, But i'll try it later and see if it works. – Makdous Mar 30 '20 at 22:08
I'm almost certain it'd work from your shown code. Let me know if doesn't. – Tpojka Mar 30 '20 at 22:10

Get table records except the ones that has foreign key values in another table

2 Answers2