Is this a pivot table or not?

Hi Mike,

I'm assuming you're working with a legacy data structure that's already in-place? Or you've inherited the codebase?

I'd be tempted to first take a look at the database structure and check (if you're using innodb storage engine) you've got the unique index setup on the pc_country, code column.

Secondly I'd check my foreign keys. So it looks like there should be a many 201502_postcode rows to one pc_country row. The link on that would be between the 201502_postcode country column and the pc_country code column.

So add these in, you can write a migration through Artisan. See http://laravel.com/docs/5.0/migrations and http://laravel.com/docs/5.0/schema#foreign-keys.

Next thing is in your Eloquent Model files, you'll want to define the relationships. So in your 201502_postcode model you'd have something like:

public function country(){
    return $this->belongsTo('\App\Models\PCCountry','code','country');
}

you'd also define the reverse relationship as:

public function postcode(){
    return $this->hasMany('\App\Models\201502Postcode','country','code');
}

You should probably do the same on the payments postcode column and the 201502_postcode table. This should be enough to tidy up your data structure if you haven't done that already. Having indexes will help speed up the queries.

To query the postcode table based on the date column, I might look to use the Repository pattern. I'm not overly familiar with Laravel's Pivot tables and having done quite a bit of work with Doctrine recently I'd rather take control over the query and write my own unit tests around it.

So the repository pattern basically would be a separate class which would contain database queries (unlike the Eloquent model which holds the record as an object). I won't go into too much detail but there's quite a few tutorials around on how to bootstrap that into your project, https://bosnadev.com/2015/03/07/using-repository-pattern-in-la...

A potential repository method might look like:

use App\Models\PCCountry;

class PostcodeRepository implements PostcodeRepositoryInterface {
    public function getPostcode(PCCountry $pccountry){
        $table_name = $pccountry->dataset.'_postcode';
        //@todo throw exception if table doesn't exist.
        return \DB::table($table)->where('country','=',$pccountry->code)->get();//will return an Eloquent\Collection
    }
}

Moderators