Laravel 5.8.* Eloquent Query (with LeftJoin) Returning Incorrect Relation Results

Question

I have a pretty complex query (below) that works in MySQL Workbench and the raw query that is output via enabling the QueryLog produces the same.

But the relation queries produce weird results and so it is not 100% correct.

Context: - The app has Events, EventSessions, Tickets, Persons - Registration records are created when a person buys a ticket for an event - RegSession records are created when a person pre-registers for a session - This query is intended to return the people who have purchased any ticket that would include the desired session and indicate whether a RegSession record has been created (eventually interested in knowing where "hasAttended" == 1).

The mysql query:

select distinct p.personID, p.firstName, p.lastName, op.OrgStat1, rs.hasAttended, er.regID
from `event-registration` as er
join `event-sessions` as es on es.eventID=er.eventID
join `person` as p on p.personID = er.personID
join `org-person` as op on p.defaultOrgPersonID = op.id 
left join `reg-session` as rs on rs.sessionID=es.sessionID 
                and rs.regID = er.regID 
                and rs.personID=er.personID 
                and es.sessionID=397
where er.ticketID in (335, 470, 330) 
    and er.eventID = 313 
    and es.sessionID in (397, null)
    and er.deleted_at is null 
order by p.lastName asc

The equivalent in eloquent:

$out = Registration::whereIn('event-registration.ticketID', $ticketIDs)
    ->select('p.personID', 'p.firstName', 'p.lastName', 'op.OrgStat1', 'rs.hasAttended', 'event-registration.regID')
    ->with('ticket', 'event', 'person.orgperson', 'regsessions', 'person')
    ->where('event-registration.eventID', '=', $es->eventID)
    ->join('person as p', 'p.personID', '=', 'event-registration.personID')
    ->join('org-person as op', 'p.defaultOrgPersonID', '=', 'op.id')
    ->join('event-sessions as es', 'es.eventID', '=', 'event-registration.eventID')
    ->leftJoin('reg-session as rs', function ($q) use ($es) {
        $q->where('rs.regID', '=', 'event-registration.regID');
        $q->where('rs.personID', '=', 'p->personID');
        $q->where('rs.sessionID', '=', 'es.sessionID');
        $q->where('es.sessionID', '=', $es->sessionID);
    })
    ->whereIn('es.sessionID', [$es->sessionID, null])
    ->distinct()
    ->orderBy('p.lastName')
    ->get();

The SQL code for my example returns 62 results, 37 of which have a RegSession record. 25 of the records are returned due to the left join with a null in the rs.hasAttended column.

I know this to be correct based on what is in the database.

These are the queries that Laravel is producing:

array:6 [▼
  0 => array:3 [▼
    "query" => "select distinct `p`.`personID`, `p`.`firstName`, `p`.`lastName`, `op`.`OrgStat1`, `rs`.`hasAttended`, `event-registration`.`regID` from `event-registration` inner join `person` as `p` on `p`.`personID` = `event-registration`.`personID` inner join `org-person` as `op` on `p`.`defaultOrgPersonID` = `op`.`id` inner join `event-sessions` as `es` on `es`.`eventID` = `event-registration`.`eventID` left join `reg-session` as `rs` on `rs`.`regID` = ? and `rs`.`personID` = ? and `rs`.`sessionID` = ? and `es`.`sessionID` = ? where `event-registration`.`ticketID` in (?, ?, ?) and `event-registration`.`eventID` = ? and `es`.`sessionID` in (?, ?) and `event-registration`.`deleted_at` is null order by `p`.`lastName` asc ◀"
    "bindings" => array:10 [▼
      0 => "event-registration.regID"
      1 => "p->personID"
      2 => "es.sessionID"
      3 => 397
      4 => 335
      5 => 470
      6 => 330
      7 => 313
      8 => 397
      9 => null
    ]
    "time" => 60.77
  ]
  1 => array:3 [▼
    "query" => "select * from `event-tickets` where 0 = 1 and `event-tickets`.`deleted_at` is null"
    "bindings" => []
    "time" => 53.02
  ]
  2 => array:3 [▼
    "query" => "select * from `org-event` where 0 = 1 and `org-event`.`deleted_at` is null"
    "bindings" => []
    "time" => 55.63
  ]
  3 => array:3 [▼
    "query" => "select * from `person` where `person`.`personID` in (357, 495, 564, 701, 821, 920, 974, 995, 1029, 1230, 1244, 1272, 1330, 1356, 1357, 1386, 1802, 1834, 1895, 1915, 2044, 2086, 2104, 2130, 2156, 2171, 2239, 2260, 2436, 2478, 2497, 2579, 2630, 2677, 2698, 4637, 4709, 4738, 4755, 5396, 5442, 5747, 5943, 5961, 5965, 6282, 7748, 8206, 8249, 8284, 8330, 8369, 8371, 8518, 8538, 8540, 8544, 8548, 8549, 8557, 8561, 8570) and `person`.`deleted_at` is null ◀"
    "bindings" => []
    "time" => 55.9
  ]
  4 => array:3 [▼
    "query" => "select * from `org-person` where `org-person`.`id` in (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) ◀"
    "bindings" => array:62 [▶]
    "time" => 64.14
  ]
  5 => array:3 [▼
    "query" => "select * from `reg-session` where `reg-session`.`regID` in (14546, 14547, 14570, 14588, 14594, 14618, 14649, 14679, 14704, 14780, 14818, 14837, 14840, 14846, 14849, 14850, 14854, 14865, 14999, 15020, 15025, 15026, 15042, 15054, 15059, 15060, 15064, 15065, 15072, 15075, 15079, 15088, 15115, 15127, 15128, 15135, 15142, 15151, 15154, 15157, 15159, 15164, 15168, 15173, 15179, 15180, 15185, 15187, 15196, 15197, 15201, 15217, 15229, 15240, 15254, 15259, 15264, 15270, 15276, 15285, 15287, 15303) ◀"
    "bindings" => []
    "time" => 91.76
  ]
]

Looking at the relation queries above, I do not understand why the "... where 0 = 1 ..." queries (which, I understand auto fail) are being created and run.

When I look at the first result returned from Laravel, the data is mostly correct (based on the SQL output) except that the hasAttended field is showing as null despite the fact that the first registration (of the person) listed does have a RegSession record in the DB.

Lastly, prior to putting on the ->select() portion of the eloquent query, it was providing output that seemed like a union of all of the tables that was largely correct EXCEPT for the hasAttended field.

Your assistance is appreciated.

Answer 1

Where clause defined as $q->where('rs.regID', '=', 'event-registration.regID'); will interpret relation column as a quoted string, so final query will look like

... WHERE rs.regID = 'event-registration.regID' ...

The solution is to use on() method on a join Docs:

->leftJoin('reg-session as rs', function ($q) use ($es) {
    $q->on('rs.regID', '=', 'event-registration.regID')
        ->on('rs.personID', '=', 'p.personID')
        ->on('rs.sessionID', '=', 'es.sessionID')
        ->where('es.sessionID', '=', $es->sessionID);
})

Another one possible approach I think should work - to use DB::Raw() like this: $q->where('rs.regID', '=', DB:Raw('event-registration.regID'))