[odb-users] Correct way to create query on column of referred to table in one-to-one relationship

[Paul Harrison]

Hi,

I have a model where there is a on-to-one relationship with a child object

#pragma db object table("Data_Descriptor") 
class Data_Descriptor_P {
// many members omitted for clarity…
#pragma db column("datloc")
   shared_ptr<Data_Location_P>  _data_location;//!<  Data location

}

#pragma db object table("DataLocation")
class Data_Location_P {
// many members omitted for clarity…
#pragma db  type("VARCHAR(255)") column("loc")
   std::string _location;//!<  Path to data

}

And I want to created a query that selects on the _location member of Data_Location_P

I  have tried

typedef odb::query<Data_Descriptor_P> DDQuery;
typedef odb::query<Data_Location_P> DLQuery;

DDQuery query =  DDQuery (DLQuery::location == DLQuery::_val("tmp"))

but (on MySQL at least) this fails with

mysql exception1054 (42S22): Unknown column 'DataLocation.loc' in 'where clause'

 because the eventual SQL that is produced is

   SELECT ….
  `Data_Descriptor`.`datloc`
   FROM `Data_Descriptor`
   LEFT JOIN `DataLocation` AS `datloc` ON `datloc`.`id`=`Data_Descriptor`.`datloc`
   WHERE `DataLocation`.`loc` ="tmp"

and it seems that MySQL only likes 

   SELECT ….
  `Data_Descriptor`.`datloc`
   FROM `Data_Descriptor`
   LEFT JOIN `DataLocation` AS `datloc` ON `datloc`.`id`=`Data_Descriptor`.`datloc`
   WHERE `dataloc.`loc` ="tmp"

i.e. the table can only be referred to by the AS alias in the WHERE clause. This seems like a MySQL bug to me, and I will report it as such. However, given that the likely timescale of a fix on MySQL is long, do you have any suggestions for workarounds? As you can see I have been experimenting with some naming pragmas, but have not found a combination that will produce something that MySQL will accept.

Regards,
	Paul Harrison.