gbwey/persistent-odbc

Esqueleto bug

felipexpert opened this issue · 3 comments

felipexpert commented

My relevant models:

ProductCharacteristic
  productInstance ProductInstanceId sql=productInstanceId
  characteristicInstance CharacteristicInstanceId sql=characteristicInstanceId
  deriving Show

Product
  codePrefix Text
  defaultPrice Double
  defaultBuyingPrice Double
  name Text
  obs Text
  disabled Bool
  deriving Show

ProductInstance
  product ProductId sql=productId
  code Text
  barCode Text
  price Double
  buyingPrice Double
  proportional Bool
  obs Text
  disabled Bool
  deriving Show

Now my Esqueleto expression:

f' :: Handler [(Entity ProductInstance, Entity Product, Entity ProductCharacteristic)]
f' = runDB
   $ E.select
   $ E.from $ \(pi `E.InnerJoin` p `E.InnerJoin` pc) -> do
     E.on $ pi ^. ProductInstanceProduct E.==. p ^. ProductId
     E.on $ pc ^. ProductCharacteristicProductInstance E.==. pi ^. ProductInstanceId
     return ( pi, p, pc)

The code I showed generated the following select instruction (which is wrong):

SELECT `ProductInstance`.`id`, `ProductInstance`.`productId`, `ProductInstance`.`code`, `ProductInstance`.`barCode`, `ProductInstance`.`price`, `ProductInstance`.`buyingPrice`, `ProductInstance`.`proportional`, `ProductInstance`.`obs`, `ProductInstance`.`disabled`, `Product`.`id`, `Product`.`codePrefix`, `Product`.`defaultPrice`, `Product`.`defaultBuyingPrice`, `Product`.`name`, `Product`.`obs`, `Product`.`disabled`, `ProductCharacteristic`.`id`, `ProductCharacteristic`.`productInstanceId`, `ProductCharacteristic`.`characteristicInstanceId`
FROM `ProductInstance` INNER JOIN `Product` ON `ProductCharacteristic`.`productInstanceId` = `ProductInstance`.`id` INNER JOIN `ProductCharacteristic` ON `ProductInstance`.`productId` = `Product`.`id`;

It is flipping the on clause in the JOIN!! Please, help me, I have to deliver this code next week...

The correct select instruction should be:

SELECT `ProductInstance`.`id`, `ProductInstance`.`productId`, `ProductInstance`.`code`, `ProductInstance`.`barCode`, `ProductInstance`.`price`, `ProductInstance`.`buyingPrice`, `ProductInstance`.`proportional`, `ProductInstance`.`obs`, `ProductInstance`.`disabled`, `Product`.`id`, `Product`.`codePrefix`, `Product`.`defaultPrice`, `Product`.`defaultBuyingPrice`, `Product`.`name`, `Product`.`obs`, `Product`.`disabled`, `ProductCharacteristic`.`id`, `ProductCharacteristic`.`productInstanceId`, `ProductCharacteristic`.`characteristicInstanceId`
FROM `ProductInstance` INNER JOIN `Product` ON `ProductInstance`.`productId` = `Product`.`id` INNER JOIN `ProductCharacteristic` ON `ProductCharacteristic`.`productInstanceId` = `ProductInstance`.`id`;
gbwey commented

Hi,
This might be the problem ...

see https://hackage.haskell.org/package/esqueleto-2.4.3/docs/Database-Esqueleto.html

select $
from $ (p1 InnerJoin f InnerJoin p2) -> do
on (p2 ^. PersonId ==. f ^. FollowFollowed)
on (p1 ^. PersonId ==. f ^. FollowFollower)
return (p1, f, p2)
Note carefully that the order of the ON clauses is reversed! You're required to write your ons in reverse order because that helps composability (see the documentation of on for more details).

Best,
Grant

  • 👍1
felipexpert commented

Thank you for this helpful information