[odb-users] Correct way to create query on column of referred to
table in one-to-one relationship
Paul Harrison
Paul.Harrison at manchester.ac.uk
Mon Oct 7 14:48:17 EDT 2013
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.
More information about the odb-users
mailing list