Eager Loading not retrieving results

Check your model relationships

https://laracasts.com/discuss/channels/eloquent/remember-which-table-gets-foreign-key-in-relationship

Then test them in tinker.

The order of the 2nd, 3rd parameters is easy to get wrong.

I don't have any default columns or keys so it seems I have to specify all the keys on all the relationships. Yes - it is confusing and I've tried the relationships with the keys swapped too. If they're not correct, I get a 'column not found' QueryException.

Here's what debugger is showing. Query 1

select * from USERID.SHIPCAR where (SHPCAR_DOOR1 in (?, ?, ?, ?, ?, ?) or SHPCAR_DOOR2 in (?, ?, ?, ?, ?, ?)) and (TIMESTAMP(SHPCAR_SCHD_PU_D, SHPCAR_SCHD_PU_T) between ? and ?) and SHPCAR_MILL = ? and SHPCAR_GATE_LOC = ? and SHPCAR_PRIMARY_FLG = ? and SHPCAR_SCAC <> ? FETCH FIRST 1000 ROWS ONLY

0. 77
1. 78
2. 166
3. 167
4. 185A
5. 191
6. 77
7. 78
8. 166
9. 167
10. 185A
11. 191
12. TIMESTAMP('2018-05-14 14:54:46')
13. TIMESTAMP('2018-05-15 18:54:46')
14. M
15. W
16. P
17. RMBX

These are correct and it returns a list of load numbers - in this instance, 154 load numbers.

Query 2

select * from USERID.LOADVEH where USERID.LOADVEH.ldvh_load_no in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) FETCH FIRST 1000 ROWS ONLY

Bindings    
0. 4687389
1. 4707535
...
152. 4734248
153. 4734571

Query 3

select * from USERID.SHIPWTH where USERID.SHIPWTH.swth_ak_load_ref in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

Bindings    
0. 4687389
1. 4707535
...
152. 4734248
153. 4734571

So, there's just something wrong with how the third query is retrieving results.

Is there a way to dump the bindings as they are sent to the third query to check their datatype/size? Can you cast the bindings to a different datatype before they're applied to the query?

I don't think you can manipulate the datatypes between queries. They need to be exactly the same in both tables.

Debugbar should show the bindings?

There is a way but you'd have to make a custom copy of the hasOne relationship class (I did it before when I noticed that unpacking 10.000+ id's in pdo had a big delay compared to a raw 'where id in ('. implode(',', $ids) .')' query)

Create a class like the following, and change:

public function shipsWith() {
        return $this->hasOne('App\Models\ShipsWith','swth_ak_load_ref','shpcar_load_nbr');
}
// becomes 
public function shipsWith() {
    $instance = new ShipsWith();
    return \App\Relations\HasOne($instance->newQuery(), $this, $instance->getTable().'.swth_ak_load_ref', 'shpcar_load_nbr');
}

<?php

namespace App\Relations;

use Illuminate\Database\Eloquent\Relations\HasOneOrMany ;

class HasOne extends HasOneOrMany 
{
    public function addEagerConstraints(array $models)
    {
        // change the query here as you like
        $this->query->whereIn($this->foreignKey, $this->getKeys($models, $this->localKey));
    }
}

But I think it might be worth to try and lowercase your method name first and try it that way (also lowercase it in your script) because I remember running into situations where the snake case vs camelcase gave problems with caching of related data inside the model.

change

public function shipsWith() {
        return $this->hasOne('App\Models\ShipsWith','swth_ak_load_ref','shpcar_load_nbr');
}
// into
public function shipswith() {
        return $this->hasOne('App\Models\ShipsWith','swth_ak_load_ref','shpcar_load_nbr');
}

and

$loads = $this->model::doors()->scheduled($start_time, $end_time)->with(['coils','shipsWith'])->get();
// into
$loads = $this->model::doors()->scheduled($start_time, $end_time)->with(['coils','shipswith'])->get();

Just for a quick test.....

Well, the camel case apparently was an issue but now ...

$loads = $this->model::doors()->scheduled($start_time, $end_time)->with(['coils','shipswith'])->get();

is returning the 3 expected queries AND a separate query for each ShipsWith model.

I might look at creating a custom copy tomorrow.

Thanks everyone for your help. This has been frustrating.

PS. My view was causing those extra queries - which worked; it's just the logic during the with(['coils','shipswith'])->get(); that seems to be the issue.

it's gotta be because the keys don't match for the shipswith and load table.

I even tried this:

$load = $this->model::where('shpcar_load_nbr', '=', '4730335')->with('shipswith')->get();

and nothing. Querying shipswith directly works fine.

Code quoted here, wasn't sure how to quote code:

**Load Model:{"shpcar_load_nbr":"4731610","shpcar_srn":"4731610","shpcar_scac":"SLTA","shpcar_carrier":"STEEL TRANSPORT, INC. (IN) ","shpcar_rrcar_nbr":" ","shpcar_rrcar_owner":" ","shpcar_barge_seq_n":"0","shpcar_assignment":" ","shpcar_mode":"TRK ","shpcar_license_nb":"62315A ","shpcar_license_st":"IN","shpcar_tractor_nb":"62315 ","shpcar_barge_owner":" ","shpcar_barge_nbr":" ","shpcar_tare_wgt":"32440","shpcar_gross_wgt":"79820","shpcar_demurrage":"0","shpcar_detn_chrg":" ","shpcar_schd_pu_d":"2018-05-14","shpcar_schd_pu_t":"16:00:00","shpcar_prev_pu_d":"9999-01-01","shpcar_prev_pu_t":"00:00:00","shpcar_prev_scac":" ","shpcar_door1":"185A","shpcar_door2":" ","shpcar_load_arrv_d":"2018-05-14","shpcar_load_arrv_t":"15:28:00","shpcar_arrival_id":"MDSERRS ","shpcar_stage_cd":"05 ","shpcar_load_admt_d":"2018-05-14","shpcar_load_admt_t":"15:33:00","shpcar_admit_id":"MDSERRS ","shpcar_comp_ld_dt":"2018-05-14","shpcar_comp_ld_tm":"16:03:00","shpcar_comp_ld_id":"MDSHAXM ","shpcar_load_dprt_d":"2018-05-14","shpcar_load_dprt_t":"16:30:00","shpcar_depart_id":"MDSEMJB ","shpcar_return_dt":"9999-01-01","shpcar_return_tm":"00:00:00","shpcar_return_id":" ","shpcar_return_cd":" ","shpcar_return_wgt":"0","shpcar_veh_status":"D","shpcar_lwgt_scale":" ","shpcar_owgt_permit":"N","shpcar_dpt_offwgt":"N","shpcar_catchup":" ","shpcar_mill":"M","shpcar_inspection":" ","shpcar_gate_loc":"W","shpcar_primary_flg":"P","shpcar_comb_load":" ","shpcar_reschd_dt":"9999-01-01","shpcar_reschd_tm":"00:00:00","shpcar_reschd_id":" ","shpcar_reschd_cd":" ","shpcar_reject_dt":"9999-01-01","shpcar_reject_tm":"00:00:00","shpcar_reject_id":" ","shpcar_reject_cd":" ","shpcar_trough_type":" ","shpcar_open_cover":" ","shpcar_last_upd_ts":"2018-05-14 16:30:01.838519","shpcar_last_upd_id":"ESADD01 ","shpcar_permit_wgt":"0","shpcar_pu_blvd_in":" ","shpcar_pu_bay_in":" ","shpcar_dlvycnfm_dt":"9999-01-01","shpcar_dlvycnfm_tm":"00:00:00","shpcar_dlvycnfm_id":" ","shpcar_match_wgt":"0","shpcar_trk_license":" ","shpcar_trk_state":" ","is_split_load":false,

You actually need all those field at one time? The Trk Co I worked for had van, hopper, and reefers, multi-pick and drop many times. But I never needed that many fields at one time.

I had as example:

• customer table
• carrier table
• driver table
• load table (just a few fields here)
• child pickup table
• child drop table
• and some accounting tables
• etc

There is no way to normalize this data some to cut down on the number of fields.

I am not saying a many to many mess, but perhaps spread things out in a few one to many tables.

That is a lot of fields. Just an observation.

Even one of our largest customers, who also had their own trucks, used SAP didn't have that many fields on their printed out shipping paperwork. Maybe one third.

I don't have any control of the tables but I will be cutting down the columns I need considerably. Just trying to get things working for now.

I did find a solution to my issue.

I cast'd the load numbers from characters to integers and that took care of the issue.