EDIT: I just realized that I was responding to the comments and didn't really answer your original question, so I updated my examples below.
If you are using Laravel and artisan fully to build your database and track migrations, here is what you need to do for the 3 tables. You want the affiliations table to hold the id of the user and the id of the company.
First, create 3 database migrations in your command line:
php artisan make:migration create_users_table --table="users"
php artisan make:migration create_companies_table --table="companies"
php artisan make:migration create_affiliations_table --table="affiliations"
You will now have 3 migration files in your /database/migrations folder.
The files will look like this:
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateTableUsers extends Migration
{
public function up()
{
Schema::create('users', function (Blueprint $table) {
$table->increments('id')->unsigned();
$table->string('email')->unique();
$table->string('password', 60);
//more fields @see https://laravel.com/docs/master/migrations#creating-columns
$table->rememberToken();
$table->timestamps();
$table->softDeletes();
});
}
public function down()
{
Schema::drop('users');
}
}
--
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateTableCompanies extends Migration
{
public function up()
{
Schema::create('companies', function (Blueprint $table) {
$table->increments('id')->unsigned();
$table->string('name', 100);
//more fields @see https://laravel.com/docs/master/migrations#creating-columns
$table->timestamps();
$table->softDeletes();
});
}
public function down()
{
Schema::drop('companies');
}
}
--
<?php
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;
class CreateTableAffiliations extends Migration
{
public function up()
{
Schema::create('affiliations', function (Blueprint $table) {
$table->integer('user_id')->unsigned();
$table->integer('company_id')->unsigned();
$table->primary(['user_id', 'company_id']);
});
}
public function down()
{
Schema::drop('affiliations');
}
}
Now run artisan one more time in your console to create the tables.
php artisan migrate
You have several options at this point when you query your database. For example, a raw SQL query might look like:
SELECT c.* FROM affiliations a JOIN companies c ON (a.company_id=c.id) WHERE a.user_id=1;
or
SELECT u.* FROM affiliations a JOIN users u ON (a.user_id=u.id) WHERE a.company_id=1;
However, it's probably better to use eloquent models since you are using Laravel. I like to create Db models in my App/Db folder. For example, this is what the /App/Db/Companies.php may look like.
<?php
namespace App\Db;
use Illuminate\Database\Eloquent\Model;
use Illuminate\Database\Eloquent\SoftDeletes;
class Companies extends Model {
use SoftDeletes;
public function affiliations() {
return $this->hasMany('App\Db\Affiliations', 'company_id', 'id');
}
}
THIS IS JUST AN EXAMPLE, AND NOT TESTED.
Refer to https://laravel.com/docs/master/eloquent-relationships#defining-relationships
You would make your other Db table models (Affiliations & Users) too, and in your application, a query might look like this:
class CompanyController extends Controller {
public function search($id) {
$company = Companies::find($id);
$company->load('affiliations.users');
foreach ($company->affiliation as $aff) {
echo $aff->user->name;
}
}
}
You could switch that around too where you find the users that have an affiliation with that company id. More on querying relations: https://laravel.com/docs/master/eloquent-relationships#querying-relations
There are several ways to achieve your desired result, so really read up on those eloquent relationships! It seems like a lot of work for something so simple but when you are building larger applications it's very handy.
Pertaining to your original question, the route for this could look like:
Route::get('/search/company/{id}', 'CompanyController@search');