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

[Paul Harrison]

On 2013-10 -07, at 19:48, Paul Harrison <Paul.Harrison at manchester.ac.uk>
 wrote:

> 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.
> 

Actually I have just found what will work…

make sure that Data_Location_P table name and the Data_Descriptor  location column have pragmas that give them identical names including using the same case, then it ends up with 

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

a bit of a hack….