Sep 2, 2022

Level 1

Please kindly help me "normalizing" this sample Laravel Project

Hello again :)

Yesterday, I started a thread in this forum which (looking back) was badly written by me.

I have now invested the time to create a working sample project that has 3 MySQL tables.

listings
tags
relations

Good News:

Everything works!

Problem:

I have NO idea, how to connect the 3 tables to each other.

Please tell me, what would be the best idea to allow users to search for listings that have the tags X, Y and Z ?

Should I use a route? Like: /listings/tags/X/Y/Z
Or should I go with a query string? /listings/?tags=X,Y,Z
Or maybe separated? /listings/?tag1=X&tag2=Y&tag3=Z

The last one looks bad, because it's limiting the amount of possible values, whereas the second guess is comma-separated and also weird.

Besides that: I am stuck. What should I do next? How should I link the tables?

Which route and controller should I add next?

What is the next step according to "best practice" principles?

Please keep in mind: I am totally new to Laravel and even after reading tons of documentation and watching many hours worth of videos, I am still a beginning!

Here is what I have done so far:

Step 1

Create a MySQL database

Step 2

Set up your .env file, especially:

APP_URL=

DB_CONNECTION=mysql
DB_HOST=127.0.0.1
DB_PORT=3306
DB_DATABASE=
DB_USERNAME=
DB_PASSWORD=

Step 3

php artisan make:migration create_listings_table
php artisan make:migration create_tags_table
php artisan make:migration create_relations_table

php artisan make:model Listing
php artisan make:model Tag
php artisan make:model Relation

php artisan make:controller ListingController

Step 4

database/migrations/*_create_listings_table.php

Code:

$table->string('title');
$table->longText('description');

Step 5

database/migrations/*_create_tags_table.php

Code:

$table->string('name');

Step 6

database/migrations/*_create_relations_table.php

Code:

$table->string('listing_id');
$table->string('tag_id');

Step 7

database/seeders/DatabaseSeeder.php

Code:

// At the top:

use App\Models\Tag;
use App\Models\Listing;
use App\Models\Relation;

and

// below:

Listing::create(['title' => 'aaa title', 'description' => 'aaa description']);
Listing::create(['title' => 'bbb title', 'description' => 'bbb description']);
Listing::create(['title' => 'ccc title', 'description' => 'ccc description']);
Listing::create(['title' => 'ddd title', 'description' => 'ddd description']);
Listing::create(['title' => 'eee title', 'description' => 'eee description']);
Listing::create(['title' => 'fff title', 'description' => 'fff description']);



Tag::create(['name' => 'green']);
Tag::create(['name' => 'yellow']);
Tag::create(['name' => 'blue']);
Tag::create(['name' => 'orange']);
Tag::create(['name' => 'purple']);



Relation::create(['listing_id' => '1', 'tag_id' => '3']);
Relation::create(['listing_id' => '1', 'tag_id' => '2']);

Relation::create(['listing_id' => '2', 'tag_id' => '1']);
Relation::create(['listing_id' => '2', 'tag_id' => '4']);
Relation::create(['listing_id' => '2', 'tag_id' => '5']);

Relation::create(['listing_id' => '3', 'tag_id' => '2']);
Relation::create(['listing_id' => '3', 'tag_id' => '4']);

Relation::create(['listing_id' => '4', 'tag_id' => '1']);
Relation::create(['listing_id' => '4', 'tag_id' => '2']);
Relation::create(['listing_id' => '4', 'tag_id' => '3']);

Relation::create(['listing_id' => '5', 'tag_id' => '3']);
Relation::create(['listing_id' => '5', 'tag_id' => '5']);

Relation::create(['listing_id' => '6', 'tag_id' => '2']);
Relation::create(['listing_id' => '6', 'tag_id' => '3']);
Relation::create(['listing_id' => '6', 'tag_id' => '4']);
Relation::create(['listing_id' => '6', 'tag_id' => '5']);

Step 8

php artisan migrate --seed

Step 9

routes/web.php

Code:

// At the top:
use App\Http\Controllers\ListingController;

and

// below
Route::get('/', [ListingController::class, 'index']);

Step 10

app/Http/Controllers/ListingController.php

Code:

// At the top:
use App\Models\Listing;

and

// below

public function index()
{
	return view('listings.index', 
	[
	'listings' => Listing::latest()->paginate(6)
	]);
}

Step 11

Create the directory:

resources/views/listings/

Step 12

Create the following file:

resources/views/listings/index.blade.php

Step 13

resources/views/listings/index.blade.php

Code:

@foreach ($listings as $listing)

{{ $listing->title; }}<br>
{{ $listing->description; }}<br>

<br><hr><br>

@endforeach

Step 14

Open the website in the browser

Thank you!

Yours

Ronaldo

UPDATE

Just to be sure there is no confusion as to what I am asking:

So far, I only managed to SHOW all listings.

Now, I would love to filter them by tags.

The tables already exist and the data has been seeded.

I "only" need help with the final step (final route + final controller change + filtering logic)

sr57

3 years ago

Level 39

@ronaldo100

I "only" need help with the final step ...

Not easy to explain in some words reading your firsts questions, so let's begin by clarifying some points and defining the first next step.

I have NO idea, how to connect the 3 tables to each other.

Me too :-) ,it's your data and you are the only one who should know. To help you clarify this point have a look on the on "Entity Relation Diagram" and define yours.

That said, it seems you have a OneToMany relation between Listing and Tag , so your your table relation is no more that the pivot table. Have a look in Laravel Eloquent Model

what would be the best idea to allow users to search for ...

None of them, routes are not a way to search for, .... you search with a query and you enter the parameter via a view

First steps

Create the model (depending on your ER diagram)
Create a view where a user can enter/choose 3 tags to search.

rodrigo.pedra

3 years ago

Level 56

I "only" need help with the final step (final route + final controller change + filtering logic)

I think you were almost there

Step 1

Or should I go with a query string? /listings/?tags=X,Y,Z

I'd go with a query string, but using the array syntax:

/listings/?tags[]=X&tags[]=Y&tags[]=Z

This way PHP and Laravel will already return tags as an array of string.

Step 2

In your route, add a call to a local scope:

public function index()
{
    return view('listings.index', [
        'listings' => Listing::latest()->withTags(request('tags', []))->paginate(6)
    ]);
}

Step 3

Ensure your Listing model has both a relationship defined to the Tag model, and add the relevant local scope.

public function tags() {
    return $this->belongsToMany(Tag::class, 'relations')
        ->using(Relation::class);
}

public function scopeWithTags($builder, array $tags)
{
    if (count($tags) > 0) {
        $builder->whereHas('tags', fn ($query) => $query->whereIn('name', $tags));
    }
}

1 like

Please or to participate in this conversation.