Querying records with relationship using author, book and rating table

Suppose there are three tables - authors, books and rating. In this post, we will see some example codes of using relationships and some filtering records also from these tables.

Step 01: create fresh laravel project

composer create-project laravel/laravel query_records_with_relationship

Step 02: Setting Up scaffolding migration of Author with artisan command

php artisan make:model Author -m

Step 03: Setting Up scaffolding migration of Book with artisan command

php artisan make:model Book -m

Step 04: Setting Up scaffolding migration of Rating with artisan command

php artisan make:model Rating -m

Step 05: Specifying migration definition authors

public function up()
{
    Schema::create('authors', function (Blueprint $table) {
        $table->id();
        $table->string('name');
        $table->timestamps();
    });
}

Step 06: Specifying migration definition for books

public function up()
{
	Schema::create('books', function (Blueprint $table) {
		$table->id();
		$table->foreignId('author_id')->constrained();
		$table->string('title');
		$table->timestamps();
	});
}

Step 07: Specifying migration definition for ratings

public function up()
{
	Schema::create('ratings', function (Blueprint $table) {
		$table->id();
		$table->foreignId('book_id')->constrained();
		$table->integer('rating');
		$table->timestamps();
	});
}

Step 08: Setting up relationship in Author model in App\Models\Author.php

public function books()
{
	return $this->hasMany(Book::class);
} 

Step 09: Setting up relationship in Book model in App\Models\Book.php

public function author()
{
    return $this->belongsTo(Author::class);
} 

public function ratings()
{
   return $this->hasMany(Rating::class);
}

Step 10: Seeding Author, Book and Rating in DatabaseSeeder.php

// at the top
use Faker\Generator;

public function run()
{
	$faker = app(Generator::class);
	
	for( $i=0; $i < 10 ; $i++ ) {
		\App\Models\Author::create([
			'title' => $faker->text(50),
		]);
	}
	
	$authorIds = \App\Models\Author::pluck('id');
	
	for( $i=0; $i < 100 ; $i++ ) {
		\App\Models\Book::create([
			'author_id' => $authorIds->random(), 
			'title' => $faker->text(50),
		]);
	}
	
	$bookIds = \App\Models\Book::pluck('id');

	for( $i=0; $i < 100 ; $i++ ) {
		\App\Models\Rating::create([
			'book_id' => $bookIds->random(), 
			'rating' => rand(1,5)
		]);
	} 	
} 

Step 11: Create BookController using artisan command

php artisan make:controller BookController

Step 12: Now creating list of methods ( six ) inside BookController.php to illustrate examples of using relationship to query records

Defining routes in web.php

Route::get('/', [BookController::class, 'filterWithRelationship']);
Route::get('/hasOneBook', [BookController::class, 'hasOneBook']);
Route::get('/hasNotAnyBook', [BookController::class, 'hasNotAnyBook']);
Route::get('/hasMoreThanFiveBooks', [BookController::class, 'hasMoreThanFiveBooks']);
Route::get('/getAuthorWithRatedBooks', [BookController::class, 'getAuthorWithRatedBooks']);
Route::get('/getAuthorWithBookTitle', [BookController::class, 'getAuthorWithBookTitle']);

Back in BookController.php adding following six methods

function filterWithRelationship() {

	// querying books by certain author by his name but only with some title containing something in it 
	$author = Author::where('name', 'Franco Ebert')->first();
	$books = $author->books()->where('title', 'like', '%lu%')->pluck('title')->dd();

}

function hasOneBook() {
	// querying authors who have at least 1 book in database
	Author::has('books')->get()->dd();

}

function hasNotAnyBook() {	
	// querying author does not have any book
	Author::doesntHave('books')->get()->dd();

} 

function hasMoreThanFiveBooks() {
	// querying authors who have more than five books
	Author::has('books', '>', 5)->get()->dd();
}

function getAuthorWithRatedBooks() {
	// querying authors that have rated books ( at least 1 rating )
	Author::has('books.ratings')->get()->dd();
	
}


// filtering child records/ relation with where statement to get parent records
function getAuthorWithBookTitle() {
	// querying authors based on given book title on books records
	Author::wherehas( 'books', function ( $query ){
		$query->where('title', 'like', '%ab%');
	})->get()->dd();
}