Scope And Relationship on Same Model

Question

I have a table called payments which contains a field called Vendor ZIP.

I have a table called 201502_postcodes and my "join" in this case is the postcode field in this table.

How do I return field values in this 201502_postcodes table using Eloquent?

My Models are;

<?php namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class Payment extends Model {

public function postcodeExtract()
{
    return $this->belongsTo('App\Models\PostcodeExtract', 'postcode', 'Vendor ZIP');
}

_

<?php namespace App\Models;

use Illuminate\Database\Eloquent\Model;

class PostcodeExtract extends Model {

protected $connection = 'postcodes';

public function scopeFromTable($query, $tableName) 
{
    return $query->from($tableName);
}

public function payment()
{
    return $this->hasMany('App\Models\Payment', 'Vendor ZIP', 'postcode');
}

So, I have a scope on this model because the 201502 part of my table name is a variable (in that, a new one comes in every quarter).

In my controller... I have no idea what to put. I don't know how to get both scope and relationship to work. How can I write a query that will take a postcode/zip and output one of the fields from the (do I refer to them as "methods"?) postcode extract table?

It is not a duplicate of this question Laravel 4: Dynamic table names using setTable() because relationships are not involved or discussed on that question.

--- UPDATE ---

If I am to use getTable - would it go something like this...

class PostcodeExtract {

  public function setTableByDate($selected_tablename)
  {
    $this->table = $selected_tablename;

    // Return $this for method chaining
    return $this;
  }

  public function getTable()
  {
    if (isset($this->table))
        $this->setTableByDate($this->table);

    return $this->table;
  }
}

And then I would use it in my controller like;

$selected_tablename = 201502_postcode //created by some other controller
$postcode_extract = new PostcodeExtract;
$data = $postcode_extract->setTableByDate($selected_tablename)->get()->toArray();

The Carbon stuff isn't really relevant. I have a lookup to get those tablenames the fact the prefix with a date like value shouldn't mean it's treated like a date.

Answer 1

There are a couple of things going on here.

scopeFromTable() is redundant

Laravel employs magic methods to handle calls to undefined methods. Calling from() on the model will actually call from() on the models internal Query object (assuming you didn't define a method called 'from' on the model itself). It's worth reading the __call and __callStatic methods on the Model class.

relationships use getTable()

Another aspect of the Laravel is the concept of convention over configuration. This basically means that the framework assumes some things so that you don't have to define every detail. In regards to table naming convention, it will naturally use a table name derived from the class name.

// Uses table 'foos'
class Foo {}

There are a few ways to change this behavior. First, you can define a 'table' data member like this.

class Foo {
    protected $table = 'bars';
}

If you need a more dynamic behavior, then you can redefine the getTable method.

class Foo {
    public function getTable()
    {
        // return your special table name based on today's date
    }
}

Ultimately the models and their relationships refer to getTable to figure out what the table names should be.

your use cases

If you only ever need to query the current table, then I would suggest redefining getTable.

If you need to query both current and past tables, then I suggest pairing a new method along side redefining getTable

class Foo {
    public function setTableByDate(\DateTime $date)
    {
        $this->table = // generate table name from $date

        // Return $this for method chaining
        return $this;
    }

    public function getTable()
    {
        if (isset($this->table))
            $this->setTableByDate(\Carbon\Carbon::now());

        return $this->table;
    }
}

With this in place, you don't have to worry about the table name in your controller or anywhere else unless you need to query past records.

setting the table by date per user

$foos = Foo::setTableByDate($user->some_date)->where(...)->get();