sql query: Syntax error, unexpected token IDENTIFIER(WHEN)

Question

sql query: Syntax error, unexpected token IDENTIFIER(WHEN)

jcof Oct '15

Created Oct '15	Last Reply Oct '15	Replies 0	Views 1721	Votes 1

jcof 1.8k

Oct '15

Hello,

I'm trying to do the following query:

  $sql = 'SELECT refgeo1.rgo_depcom, refgeo1.rgo_libelle, refgeo1.rgo_latitude, refgeo1.rgo_longitude
  FROM vie.refgeo refgeo1
  JOIN vie.refgeo refgeo2 ON refgeo2.rgo_depcom = \''.$insee.'\'
  WHERE
  6366 * (acos(
  CASE WHEN(cos(radians(refgeo1.rgo_latitude))
  * cos(radians(refgeo2.rgo_latitude))
  * cos(radians(refgeo2.rgo_longitude) - radians(refgeo1.rgo_longitude)) + sin(radians(refgeo1.rgo_latitude))
  * sin(radians(refgeo2.rgo_latitude)) > 1 ) THEN 1 ELSE
  cos(radians(refgeo1.rgo_latitude))
  * cos(radians(refgeo2.rgo_latitude))
  * cos(radians(refgeo2.rgo_longitude) - radians(refgeo1.rgo_longitude)) + sin(radians(refgeo1.rgo_latitude))
  * sin(radians(refgeo2.rgo_latitude)) END
  )) <='.$rayon.'
  ORDER BY refgeo1.rgo_libelle';

This sql request works great in PGadmin (I'm using postgresql database). But this request give me the following error when I do it with code:

  {
  status: "error",
  message: "Syntax error, unexpected token IDENTIFIER(WHEN), near to '(cos(radians(refgeo1.rgo_latitude)) * cos(radians(refgeo2.rgo_latitude)) * cos(radians(refgeo2.rgo_longitude) - radians(refgeo1.rgo_longitude)) + sin(radians(refgeo1.rgo_latitude)) * sin(radians(refgeo2.rgo_latitude)) > 1 ) THEN 1 ELSE cos(radians(refgeo1.rgo_latitude)) * cos(radians(refgeo2.rgo_latitude)) * cos(radians(refgeo2.rgo_longitude) - radians(refgeo1.rgo_longitude)) + sin(radians(refgeo1.rgo_latitude)) * sin(radians(refgeo2.rgo_latitude)) END )) <=100 ORDER BY refgeo1.rgo_libelle', when parsing: SELECT refgeo1.rgo_depcom, refgeo1.rgo_libelle, refgeo1.rgo_latitude, refgeo1.rgo_longitude FROM vie.refgeo refgeo1 JOIN vie.refgeo refgeo2 ON refgeo2.rgo_depcom = '29019' WHERE 6366 * (acos( CASE WHEN(cos(radians(refgeo1.rgo_latitude)) * cos(radians(refgeo2.rgo_latitude)) * cos(radians(refgeo2.rgo_longitude) - radians(refgeo1.rgo_longitude)) + sin(radians(refgeo1.rgo_latitude)) * sin(radians(refgeo2.rgo_latitude)) > 1 ) THEN 1 ELSE cos(radians(refgeo1.rgo_latitude)) * cos(radians(refgeo2.rgo_latitude)) * cos(radians(refgeo2.rgo_longitude) - radians(refgeo1.rgo_longitude)) + sin(radians(refgeo1.rgo_latitude)) * sin(radians(refgeo2.rgo_latitude)) END )) <=100 ORDER BY refgeo1.rgo_libelle (997)"
  }

I use the following code to query

  $modelManager = $this->getDI()->getShared('application')->modelsManager;
  $query = $modelManager->createQuery($sql);
  $cars = $query->execute();
  ec($cars); //Function that var dump
  die;

I have try several things already, putting the big calculation as part of the select to reduce the case when part. I look for information on how to create a rawsql but the documentation est limited and it seems that I still need the class to extend model, which I cannot.

I tried the following:

    $sql   = "SELECT refgeo1.rgo_depcom, refgeo1.rgo_libelle, refgeo1.rgo_latitude, refgeo1.rgo_longitude
    FROM vie.refgeo refgeo1 JOIN vie.refgeo refgeo2
    ON refgeo2.rgo_depcom = '29019'";

    // Base model
    //$robot = new Robots();

    // Execute the query
    $g = $this->getReadConnection()->query($sql);

    ec($g);
    $t = new Resultset(null, $this, $this->getReadConnection()->query($sql));
    ec($t);

Can anybody give me some pointer on what I'm doing wrong?

1