Insert records to either two tables or one table depending on if a record exists or not using Laravel query builder

Question

I'm trying to insert records to either two tables or one table depending on if a record exists or not.

First table Authors

ID | Name
1  | Joe
2  | Sam

Second table Books

ID | author_ID | Book
1  | 2         | Book1
2  | 2         | BookYYY
3  | 1         | BookABC

What I want to accomplish is to check if author exists first, if not insert author and his book and if it DOES exists insert just the book with the right author ID

Here is what I've attempted so far that doesn't seem to work.

$result = DB::table('authors')
            ->where('name',  $data['author_name'])
            ->where('username', $data['author_username'])->pluck('id');

if(is_null($result)){
    //Not in table add new author
    $id = DB::table('authors')->insertGetId(
        ['name' =>  $data['author_name'], 'username' => $data['author_username']]
    );
    //Add book
    DB::table('books')->insert(
        ['author_id' => '.$id.', 'name' => "Book777"]
    );
}
else{
    //Is in table insert just book
    DB::table('books')->insert(
        ['author_id' => '.$result.', 'name' => "Book777"]
    );
}

So I'm trying to add author with Book name "Book777" but if author does exists in DB get the author ID and insert just the book.

Thank you all for helping me with this! Appreciate any help.

Answer 1

Consider using ORM. With Eloquent you can change all your code to just this:

$author = Author::firstOrCreate(['name' => $data['author_name'], 'username' => $data['author_username']]);
$author->books()->create(['name' => 'Book777']);

With Query Builder you can do this:

$attributes = [
    'name' => $data['author_name'],
    'username' => $data['author_username']
];

$author = DB::table('authors')->where($attributes)->first();
$authorId = is_null($author) ? DB::table('authors')->insertGetId($attributes) : $author->id;
DB::table('books')->insert(['author_id' => $authorId, 'name' => "Book777"]);