Laravel Relationship / JOIN not working

I may be missing an Eloquent relationship point with this, but if I were doing it with raw SQL, that would be my query;

SELECT [I have lots of columns I won't list them all here] 
FROM payments as t1 
LEFT JOIN selected_postcodes_34894839483_1 as t2 ON t1.`Vendor ZIP` = t2.postcode

      

selected_postcodes_xxxxxxx_x just contains a list of postcodes.

My controller is like this:

$temp_table_data = new TempTable;
$payments = $temp_table_data
    ->setTempTable("selected_postcodes_{$timestamp_string}_{$userid}")
    ->payment()->get()->toArray();

      

At the time, I thought that part of this (s) payment () might return all associated payment data. I don't think this is correct, but I don't know what else to try.

I have my models:

class Payment extends Model {
  public function tempTable()
  {
    return $this->belongsTo('App\Models\TempTable', 'postcode', 'Vendor ZIP');
  }

      

and

class TempTable extends Model {
  public function setTempTable($selected_tablename){
    $this->table = $selected_tablename;
    return $this;
   }

  public function getTable(){
     if (isset($this->table))
       $this->setTempTable($this->table);
       return $this->table;
    }

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

      

I tried to just see if the TempTable model is working in its own right - forgetting about the join / relation;

$data = $temp_table_data->setTempTable("selected_postcodes_{$timestamp_string}_{$userid}")->get()->toArray();

      

Works ... I am returning the proper collection. I also tried extracting the space in Vendor ZIP

and it didn't help.

--- --- UPDATE

This is the error I am getting:

SQLSTATE[42S22]: Column not found: 1054 Unknown column 'payments.Vendor ZIP' in 'where clause' 
(SQL: select * from `payments` where `payments`.`Vendor ZIP` is null and `payments`.`Vendor ZIP` is not null)

      

--- SECOND UPDATE ---

CREATE TABLE `selected_postcodes_1431076067_1` (
`id` int(11) unsigned NOT NULL AUTO_INCREMENT,
`postcode` varchar(7) DEFAULT NULL,
`updated_at` datetime DEFAULT NULL,
`created_at` datetime DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1581 DEFAULT CHARSET=latin1;

      

I am not the owner of this table, so I probably cannot change the field names to remove spaces (if necessary);

CREATE TABLE `payments` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`Input RAC` varchar(6) CHARACTER SET utf8 DEFAULT NULL,
`Input LPC Code` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`Input UIN Code` varchar(6) CHARACTER SET utf8 DEFAULT NULL,
`Fiscal Year` varchar(5) CHARACTER SET utf8 DEFAULT NULL,
`Contract Number` varchar(14) CHARACTER SET utf8 DEFAULT NULL,
`Foreign Ind` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Valued or Running` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Recoverable Ind` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Contract Title` varchar(93) CHARACTER SET utf8 DEFAULT NULL,
`Contract Start Date` date DEFAULT NULL,
`Current Contract End Date` date DEFAULT NULL,
`IPT or Team Short Title` varchar(8) CHARACTER SET utf8 DEFAULT NULL,
`BLB No DEFFORM 57` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`Senior Comm Officer Title` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`P2P Contract Exemption` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Current Total Contract Value` int(8) DEFAULT NULL,
`Vendor Code` int(5) DEFAULT NULL,
`Current Vendor Name` varchar(39) CHARACTER SET utf8 DEFAULT NULL,
`Vendor Site Code` int(1) DEFAULT NULL,
`Vendor Reg No` int(7) DEFAULT NULL,
`NCage` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`P2P Ind` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`SME Ind` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW1 - Concept` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW2 - Assessment` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW3 - Demonstration` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW4 - Manufacture` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW5 - In-Service` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW6 - Disposal` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW7 - Project Support` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW8 - Ext Assistance` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW9 - Hire/Lease` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW10 - MAC` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW11 - Provision of Services` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW12 - Defence Estates` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`TOW13 - Provision of Utilities` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Ext Assist Cat` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`SIC Code PFI` int(4) DEFAULT NULL,
`SIC` int(5) DEFAULT NULL,
`SIC Version` int(4) DEFAULT NULL,
`SIC Group Cleansed Flag` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`SIC Group Uncleansed` int(2) DEFAULT NULL,
`SIC Group Cleansed` int(2) DEFAULT NULL,
`Type01- Int Collab/MOU` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Type02 - Partnering` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Type03 - PFI` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Type04 - Enabling Pan Govt` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Type05 - Framework Tech Services` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Type06 - Enabling MOD Only` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EndCust1 - Navy` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EndCust2 - MOD` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EndCust3 - Army` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EndCust4 - OGD/Agency` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EndCust5 - Air Force` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EndCust6 - Other Govts` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Prov1 - Int Prop Rights` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Prov2 - Defcon 176A` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Prov3 - Interim Payments` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Prov4 - Liquidated Damages` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Prov5 - Defform 47SC` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Prov6 - Defcon 528` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Prov7 - Earned Value Mgmt` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Prov8 - Express Guarantee` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Prov9 - Cttr Log Support` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Prov10 - Gain Share` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Competitive Ind` int(1) DEFAULT NULL,
`VOP Indicator` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`NCP1 - DEFCON 127` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`NCP2 - DEFCON 643` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`NCP3 - DEFCON 648` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`NCP4 - DEFCON 653` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`NCP5 - Govt Prof Formula Rates Apply` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`NCP6 - Target Cost Arrangement` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`CEA1 - Being Negotiated` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`CEA2 - Anticipated But Deferred` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`CEA3 - Concluded` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`CEA4 - Not Anticipated` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`CEA5 - N/A` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`CEARef1 - Standard Agreement` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`CEARef2 - Omnibus Agreement` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`CEARef3 - Master Agreement` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`CEARef4 - International MOU` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`CEARef5 - Subcontract CEA 1` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`CEARef6 - Subcontract CEA 2` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`CEARef7 - Subcontract CEA 3` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`Loan Item1 - CWI` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Loan Item2 - CWA` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Loan Item3 - CSI` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Loan Item4 - CEI` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Loan Item5 - DEFCON 23` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Loan Item6 - DEFCON 694` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`SCA1` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`SCA2` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`SCA3` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`SCA Value` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`EC Req Ind` int(1) DEFAULT NULL,
`EC Req 1 - Exempt` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 2 – Non-exempt` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 5 – PSC Non-Schedule 5` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 6 – PSC Schedule 5` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 7 – PWC` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 8 – PSvC` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 9 – Abs of tender` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 10 – R&D (Supply Cont)` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 11 – Extreme urgency` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 12 – Prop/Tech` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 13 – Works/Services` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 14 – Supplies/Wks/Svcs added` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req 15 – Cat B Service` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Req Advert` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`EC Requirements Below Threshold` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Sustainable Procurement Pre` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Sustainable Procurement Post` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`CITS Applicable` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`CITS Type Sub CTR` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`Contract Transparency Indicator` int(1) DEFAULT NULL,
`FOI Absolute Exemption` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`FOI Exemption` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Military Sensitive Technical Information` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Warlike Stores` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`CIO Endorsed Exemption` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Financial Transparency Indicator` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Warlike Stores (Total Exemption)` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`FT CIO Endorsed Exemption` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`NAMCO funded Contract` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`PO Header Closed Code` varchar(4) CHARACTER SET utf8 DEFAULT NULL,
`PO Header Closed Date` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`Hist EndCust7` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist EndCust8` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist NCP6` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist Prov6` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist TOW13` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist TOW14` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist TOW Other` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist Type04` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`IMP EU` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`IMP Non EU` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Security Classification` int(1) DEFAULT NULL,
`Overseas Subcontractor` varchar(2) CHARACTER SET utf8 DEFAULT NULL,
`Price Status 1 - Agreed at Outset` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Price Status 2 - tba` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Price Status 3 - Firm` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Price Status 4 - Fixed` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Price Status 5 - Provisional` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist EC Req 3` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist EC Req 4` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist EC Req 7` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist EC Req 9` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist EC Req 11` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Hist EC Req 18` varchar(1) CHARACTER SET utf8 DEFAULT NULL,
`Prime LOW Code` int(4) DEFAULT NULL,
`Prime LOW Percentage` int(1) DEFAULT NULL,
`Prime Region` varchar(13) CHARACTER SET utf8 DEFAULT NULL,
`Prime UK-Overseas` varchar(13) CHARACTER SET utf8 DEFAULT NULL,
`Prime LOW Payment` decimal(9,2) DEFAULT NULL,
`Prime LOW VAT` int(1) DEFAULT NULL,
`TLB Code` varchar(3) CHARACTER SET utf8 DEFAULT NULL,
`TLB Name` varchar(27) CHARACTER SET utf8 DEFAULT NULL,
`IMG Code` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`MG Code` varchar(3) CHARACTER SET utf8 DEFAULT NULL,
`BLB Code` int(4) DEFAULT NULL,
`BLB Name` varchar(24) CHARACTER SET utf8 DEFAULT NULL,
`Vendor Address Line1` varchar(23) CHARACTER SET utf8 DEFAULT NULL,
`Vendor Address Line2` varchar(32) CHARACTER SET utf8 DEFAULT NULL,
`Vendor Address Line3` varchar(28) CHARACTER SET utf8 DEFAULT NULL,
`Vendor Address Line4` varchar(22) CHARACTER SET utf8 DEFAULT NULL,
`Vendor Address Lines Alt` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`Vendor City` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`Vendor County` varchar(10) CHARACTER SET utf8 DEFAULT NULL,
`Vendor ZIP` varchar(7) CHARACTER SET utf8 DEFAULT NULL,
`Vendor Country` varchar(2) CHARACTER SET utf8 DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5001 DEFAULT CHARSET=latin1;

      

+3


source to share


1 answer


One of the problems I see is that with the given code, you were calling payment()

on an empty object TempTable

. To get related records, you first need to get records TempTable

. To break this down, you did:

$temp_table_data = new TempTable;

// start with empty TempTable object
$payments = $temp_table_data

    // set the table attribute on the empty object
    ->setTempTable("selected_postcodes_{$timestamp_string}_{$userid}")

    // gets the payment relationship from the empty object
    ->payment()

    // gets all the payments for an empty object
    ->get()

    // converts collection to array
    ->toArray();

      

To get associated payments for an item TempTable

, you first need to get a completed item TempTable

. For example:

$temp_table = new TempTable;
$temp_table_data = $temp_table
    ->setTempTable("selected_postcodes_{$timestamp_string}_{$userid}")
    ->get();

// $temp_table_data is now a Collection of all the records in the given table

// loop through all the records and print out their payments
foreach($temp_table_data as $tt) {
    print_r($tt->payment->toArray());
}

      

Also note that if you want to speed things up a bit, you can load the payment relationships when you get records from the temp table:

$temp_table_data = $temp_table
    ->setTempTable("selected_postcodes_{$timestamp_string}_{$userid}")
    ->with('payment') // eager load payment relationship
    ->get();

      

Edit

reply to comments regarding uploaded download

In this case, because you must set the table name after creating a new instance of the model, you cannot call with()

on the model; you need to unwrap and call with()

on the request object that is generated using your generated model. So, instead of the above code, you need to add another call to the method chain:



$temp_table_data = $temp_table
    ->setTempTable("selected_postcodes_{$timestamp_string}_{$userid}")
    ->newQuery() // get a new query builder object for this instance
    ->with('payment') // eager load payment relationship on the query object
    ->get();

      

When you call with()

on a model, it creates a new instance of the model and then creates a new query builder object using that new instance. The problem in your situation is that the new model instance won't have a table name.

Edit 2

reply to comments regarding the selection of specific columns

If you want to change the query used for payments (for example, to select only certain columns), you have two options, depending on whether you want to download or not.

If you don't want to download, you simply modify the payment request like any other request:

foreach($temp_table_data as $tt) {
    // note the use of the relationship query (payment()), not the Collection attribute (payment)
    print_r($tt->payment()->select('Input RAC', 'Contract Number', 'Foreign Ind')->toArray());
}

      

If you want to download, you can change the query used to download the data. In the method, with()

you pass an array of eagerly loaded relationships, where the key is the name of the relationship and the value is the Closure used to modify the query for the relationship. It should be noted here that if you change the select clause, you must include the column containing the foreign key in the TempTable, otherwise Laravel will not know how to link the objects it creates.

$temp_table_data = $temp_table
    ->setTempTable("selected_postcodes_{$timestamp_string}_{$userid}")
    ->newQuery() // get a new query builder object for this instance
    ->with(['payment' => function ($query) {
        // this Closure contains the query object for the payment relationship
        // make sure your select includes the foreign key
        $query->select('Vendor ZIP', 'Input RAC', 'Contract Number', 'Foreign Ind');
    }]) // eager load payment relationship on the query object
    ->get();

      

+2


source







All Articles