i might missing point of relationships eloquent 1 but, if doing raw sql, query;
select [i have lots of columns won't list them here] payments t1 left join selected_postcodes_34894839483_1 t2 on t1.`vendor zip` = t2.postcode selected_postcodes_xxxxxxx_x holds list of postcodes.
my controller this;
$temp_table_data = new temptable; $payments = $temp_table_data ->settemptable("selected_postcodes_{$timestamp_string}_{$userid}") ->payment()->get()->toarray(); at time, thought ->payment() part of query maybe return related payments data. don't think that's right, don't know else try.
i have models this;
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 have tried seeing if temptable model works in it's own right - forgetting join/relationship;
$data = $temp_table_data->settemptable("selected_postcodes_{$timestamp_string}_{$userid}")->get()->toarray(); that does work. proper collection returned. have tried taking out space in vendor zip , did nothing help.
---update---
this error get;
sqlstate[42s22]: column not found: 1054 unknown column 'payments.vendor zip' in 'where clause' (sql: select * `payments` `payments`.`vendor zip` null , `payments`.`vendor zip` 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'm not owner of table - can't change field names remove spaces (unless 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 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 @ 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;
one issue see given code, you've called payment() on empty temptable object. in order related records, first need temptable records. break down, you've done:
$temp_table_data = new temptable; // start empty temptable object $payments = $temp_table_data // set table attribute on empty object ->settemptable("selected_postcodes_{$timestamp_string}_{$userid}") // gets payment relationship empty object ->payment() // gets payments empty object ->get() // converts collection array ->toarray(); in order related payments temptable object, first need populated temptable object. example:
$temp_table = new temptable; $temp_table_data = $temp_table ->settemptable("selected_postcodes_{$timestamp_string}_{$userid}") ->get(); // $temp_table_data collection of records in given table // loop through records , print out payments foreach($temp_table_data $tt) { print_r($tt->payment->toarray()); } also note, if wanted speed things little bit, eager load payment relationships when records temp table:
$temp_table_data = $temp_table ->settemptable("selected_postcodes_{$timestamp_string}_{$userid}") ->with('payment') // eager load payment relationship ->get(); edit
response comments regarding eager loading
in case, since have set table name after creating new instance of model, can't call with() on model; need drill down , call with() on query object created using instantiated model. so, instead of code above, need add 1 more call method chain:
$temp_table_data = $temp_table ->settemptable("selected_postcodes_{$timestamp_string}_{$userid}") ->newquery() // new query builder object instance ->with('payment') // eager load payment relationship on query object ->get(); when call with() on model, creates new instance of model, , creates new query builder object using new instance. problem in situation new instance of model not have table name set.
edit 2
response comments regarding selecting specific columns
if want modify query used on payments (such selecting specific columns), have 2 options, depending on if you're eager loading or not.
if not eager loading, modify payment query other query:
foreach($temp_table_data $tt) { // note use of relationship query (payment()), not collection attribute (payment) print_r($tt->payment()->select('input rac', 'contract number', 'foreign ind')->toarray()); } if eager loading, can modify query used eager load data. in with() method, pass array of relationships eager load, key name of relationship, , value closure used modify query relationship. 1 thing note here if modify select clause, must include column containing foreign key temptable table, otherwise laravel not know how link objects being created.
$temp_table_data = $temp_table ->settemptable("selected_postcodes_{$timestamp_string}_{$userid}") ->newquery() // new query builder object instance ->with(['payment' => function ($query) { // closure contains query object payment relationship // make sure select includes foreign key $query->select('vendor zip', 'input rac', 'contract number', 'foreign ind'); }]) // eager load payment relationship on query object ->get(); 
Comments
Post a Comment