Forums

Hi Max & Bob,

Been a while since I posted here... the good news is I seem to be working better with CF5 and CC5 better now than before, and am migrating all my forms across😀

I have an issue with a CC5 Connection, I have the first model working as expected however the second model with a join isn't working ( I suspect that I am doing something daft wrong and just need pointing in the right direction!)

The common point between each of the tables is dfwid which is the vehicle name, the location of where the vehicle is located should be shown on the front end, I have cleared the location and fault tables so there is only an entry for one vehicle "Bravo 01" so the same vehicle id is common across both tables and the vehicle table.

I hope I have provided enough information here and the post below with the result of a debug.

Dave

Array
(
[0] => SELECT `Extension`.`id` AS `Extension.id`, `Extension`.`name` AS `Extension.name` FROM `x9fgt_chronoengine_extensions` AS `Extension` WHERE `Extension`.`enabled` = '1' ORDER BY `Extension`.`ordering` ASC
[1] => SELECT `Group`.`id` AS `Group.id`, `Group`.`parent_id` AS `Group.parent_id`, `Group`.`lft` AS `Group.lft`, `Group`.`rgt` AS `Group.rgt`, `Group`.`title` AS `Group.title` FROM `x9fgt_usergroups` AS `Group` ORDER BY `Group`.`parent_id` ASC, `Group`.`lft`
[2] => SELECT `Connection`.`id` AS `Connection.id`, `Connection`.`title` AS `Connection.title`, `Connection`.`params` AS `Connection.params`, `Connection`.`extras` AS `Connection.extras`, `Connection`.`published` AS `Connection.published` FROM `x9fgt_chronoengine_connections` AS `Connection` WHERE `Connection`.`title` = 'Vehicles' AND `Connection`.`published` = '1'
[3] => SELECT COUNT(`viewvehicle`.`id`) AS `viewvehicle.count` FROM `x9fgt_datatable_AddVehicle2` AS `viewvehicle` LEFT JOIN `x9fgt_datatable_vehiclelocation` AS `location` ON `viewvehicle`.`dfwid` = 'location.dfwid' LEFT JOIN `x9fgt_datatable_vehiclefault` AS `fault` ON `viewvehicle`.`dfwid` = 'fault.dfwid' WHERE `viewvehicle`.`state` = '1' GROUP BY `viewvehicle`.`id`
[4] => SELECT `viewvehicle`.`id` AS `viewvehicle.id`, `viewvehicle`.`uniq_id` AS `viewvehicle.uniq_id`, `viewvehicle`.`user_id` AS `viewvehicle.user_id`, `viewvehicle`.`created` AS `viewvehicle.created`, `viewvehicle`.`modified` AS `viewvehicle.modified`, `viewvehicle`.`state` AS `viewvehicle.state`, `viewvehicle`.`vehreg` AS `viewvehicle.vehreg`, `viewvehicle`.`make` AS `viewvehicle.make`, `viewvehicle`.`model` AS `viewvehicle.model`, `viewvehicle`.`color` AS `viewvehicle.color`, `viewvehicle`.`purchasedfrom` AS `viewvehicle.purchasedfrom`, `viewvehicle`.`datepicker6` AS `viewvehicle.datepicker6`, `viewvehicle`.`purchaseprice` AS `viewvehicle.purchaseprice`, `viewvehicle`.`warranty` AS `viewvehicle.warranty`, `viewvehicle`.`warrantyexpirey` AS `viewvehicle.warrantyexpirey`, `viewvehicle`.`MOTDue` AS `viewvehicle.MOTDue`, `viewvehicle`.`startmileage` AS `viewvehicle.startmileage`, `viewvehicle`.`dfwid` AS `viewvehicle.dfwid`, `viewvehicle`.`imageupload` AS `viewvehicle.imageupload`, `viewvehicle`.`text37` AS `viewvehicle.text37`, `viewvehicle`.`datepicker38` AS `viewvehicle.datepicker38`, `viewvehicle`.`specinfo` AS `viewvehicle.specinfo`, `viewvehicle`.`checkbox27` AS `viewvehicle.checkbox27`, `viewvehicle`.`datepicker29` AS `viewvehicle.datepicker29`, `viewvehicle`.`text30` AS `viewvehicle.text30`, `viewvehicle`.`text31` AS `viewvehicle.text31`, `viewvehicle`.`text33` AS `viewvehicle.text33`, `viewvehicle`.`textarea32` AS `viewvehicle.textarea32`, `viewvehicle`.`button34` AS `viewvehicle.button34`, `viewvehicle`.`LocalName` AS `viewvehicle.LocalName`, `viewvehicle`.`CurrentAge` AS `viewvehicle.CurrentAge`, `viewvehicle`.`FirstRegistered` AS `viewvehicle.FirstRegistered`, `viewvehicle`.`cc` AS `viewvehicle.cc`, `viewvehicle`.`TaxDue` AS `viewvehicle.TaxDue`, `location`.`id` AS `location.id`, `location`.`uniq_id` AS `location.uniq_id`, `location`.`user_id` AS `location.user_id`, `location`.`created` AS `location.created`, `location`.`modified` AS `location.modified`, `location`.`ridername` AS `location.ridername`, `location`.`dfwid` AS `location.dfwid`, `location`.`area` AS `location.area`, `location`.`datecollected` AS `location.datecollected`, `location`.`button5` AS `location.button5`, `location`.`vehicle` AS `location.vehicle`, `fault`.`id` AS `fault.id`, `fault`.`uniq_id` AS `fault.uniq_id`, `fault`.`user_id` AS `fault.user_id`, `fault`.`created` AS `fault.created`, `fault`.`modified` AS `fault.modified`, `fault`.`datepicker3` AS `fault.datepicker3`, `fault`.`user` AS `fault.user`, `fault`.`dfwid` AS `fault.dfwid`, `fault`.`textarea2` AS `fault.textarea2`, `fault`.`checkbox_group5` AS `fault.checkbox_group5`, `fault`.`fileupload` AS `fault.fileupload`, `fault`.`button6` AS `fault.button6`, `fault`.`dropdown7` AS `fault.dropdown7` FROM `x9fgt_datatable_AddVehicle2` AS `viewvehicle` LEFT JOIN `x9fgt_datatable_vehiclelocation` AS `location` ON `viewvehicle`.`dfwid` = 'location.dfwid' LEFT JOIN `x9fgt_datatable_vehiclefault` AS `fault` ON `viewvehicle`.`dfwid` = 'fault.dfwid' WHERE `viewvehicle`.`state` = '1' GROUP BY `viewvehicle`.`id` ORDER BY `viewvehicle`.`dfwid` LIMIT 20
)

I have adjusted the join conditions to:

<?php
return array("viewvehicle.dfwid = location.dfwid");
?>

and it works as I would expect it should do.

This leaves me with a question about the syntax of the query and when to have it written the way I had it originally?

Because I use relation hasOne when a location is updated I need that one to show, how would I set the order to show the latest entry?