2

I'm using the following code to create a query with a join.

$query      =   rs_vehicles::select(   
                            'rs_vehicles.id',
                            'rs_vehicles.type', 
                            'rs_vehicles.make', 
                            'rs_vehicles.model',
                            'rs_vehicles.year',
                            'rs_vehicles.status',
                            'rs_vehicle_regs.registration'

                            );            

    $query      ->leftJoin('rs_vehicle_regs', function($join){

                    $join->on('rs_vehicle_regs.rs_vehicles_id', '=', 'rs_vehicles.id')
                         ->where('rs_vehicle_regs.registration_date', '=',
                                DB::raw("(select max(registration_date) from rs_vehicle_regs where rs_vehicles_id = rs_vehicles.id)"));
                });       

    $rsGrid =   $query->get();

This produces the following sql statement:

select rs_vehicles.id, rs_vehicles.type, rs_vehicles.make, 
s_vehicles.model, rs_vehicles.year, rs_vehicles.status, 
s_vehicle_regs.registration from rs_vehicles 
left join rs_vehicle_regs 
on rs_vehicle_regs.rs_vehicles_id = rs_vehicles.id 
and rs_vehicle_regs.registration_date = (select max(registration_date) from rs_vehicle_regs where rs_vehicles_id = rs_vehicles.id)

The SQL statement generated by Laravel runs perfect when I execute it in MySQL Workbench and it brings back the expected values for ALL fields. However when I execute the code in Laravel although the script runs perfectly with no errors and brings back almost all the required data the 'rs_vehicle_regs.registration' field which is in the joined table comes back as null. I've spent ages trying to figure this out and I am getting nowhere. Anybody any idea why this wont work in Laravel? I'm at my wits end.

I have tried changing get() to toSql() and I get the following

select `rs_vehicles`.`id`, `rs_vehicles`.`type`, `rs_vehicles`.`make`, `rs_vehicles`.`model`, `rs_vehicles`.`year`, `rs_vehicles`.`vehicle_status`, `vh_type`.`display_text` as `type_text`, `vh_status`.`display_text` as `vehicle_status_text`, `rs_vehicle_regs`.`registration` as `registration` from `rs_vehicles` left join `app_params` as `vh_type` on `rs_vehicles`.`type` = `vh_type`.`list_value` and `vh_type`.`param_type` = ? left join `app_params` as `vh_status` on `rs_vehicles`.`vehicle_status` = `vh_status`.`list_value` and `vh_status`.`param_type` = ? left join `rs_vehicle_regs` on `rs_vehicle_regs`.`rs_vehicles_id` = `rs_vehicles`.`id` where `rs_vehicles`.`status` = ?

I have also tried the following:

        $query      ->leftJoin('rs_vehicle_regs', function($join){
                $join->on('rs_vehicle_regs.rs_vehicles_id', '=', 'rs_employees.rs_vehicles_id')
                     ->where('rs_vehicle_regs.registration_date', '=', 'max(registration_date)'); 
            });

This produces the exact same result as the original query where the object as the field but the values are null.

Then I tried this:

$query      ->leftJoin('rs_vehicle_regs', 'rs_vehicle_regs.rs_vehicles_id', '=', 'rs_vehicles.id');

This join works and brings back the data I am looking for however if the join table has more than one entry related to the parent table as can happen I get all records returned and I only want one record were max(registration_date). This has me totally stumped.

Bas
  • 23
  • 6
  • This is an interesting mystery. Are you certain that the Laravel query was running on exactly the same database as when you ran the raw query on MySQL? Is the entire `rs_vehicle_regs.registration` null or just a portion of it? – Tim Biegeleisen Jul 12 '16 at 14:21
  • I don't think you can get another table's column when you query the DB via a specific table model. It would be problematic if you subsequently called `$result->save()` – apokryfos Jul 12 '16 at 14:25
  • Yes i'm 100% certain that the Laravel query is running on exactly the same database as the raw query on MySQL and the entire rs_vehicle_regs.registration is null not just a portion of it. For some reason Laravel doesn't seem to be putting the data from the join table into the resulting object. If I dd($rsGrid) and view the object the field is there it is just null. – Bas Jul 12 '16 at 14:34
  • Could be similar to this question: http://stackoverflow.com/a/18120728/2526914 - try flipping the query order of the code... – Kjell Jul 12 '16 at 14:42
  • Tried flipping the query order as suggested. Afraid it didn't make any difference. I think it might have something to do with the DB::raw() part of the query. If I simplify the join and remove this part it return all the values as you would expect however I seriously need to bring back only the record with the max(registration_date) – Bas Jul 12 '16 at 15:02
  • change `$rsGrid = $query->get();` to `$rsGrid = $query->toSql();` and compare the output with the existing sql query that you wrote above. – itachi Jul 12 '16 at 17:07
  • Ever find a solution for this? – Ethan C Aug 16 '16 at 19:39
  • Hi Ethan, Thanks for reading my post. I never go to the bottom of this issue. I thought it might have something to do with using DB Raw as eloquent might not see the join table as an eloquent model and maybe that was why it was bringing back empty results but I tried a few other variations on the query removing the DB Raw part all together but I couldn't get the join to work the way I wanted. If you come up with any solution to this please let me know. – Bas Aug 18 '16 at 10:06

0 Answers0