SQL-query in cakephp-style

Question

I'm working on a cakephp issue. I know how we do queries in cakephp. In certain circumstances you have to use the sql statement itself as query($sql). Below is a query to get the nearest points from a database. The SQL works fine. Now, I want to have it in cakephp-style to add the common pagination form cakephp. Unfortunately I can't find any information about how to do it. It's not easy to find information to do that in general. So I don't want to have a solution from you, I would be happy to get links how to transform a complicated query into cakephp.

Here is my codesnippet (it's working as already mentioned).

    public function getRestAroundMe($lat, $lng, $radius, $limit) {  // Get some data to validate login.
        /* Source: http://funkjedi.com/technology/308-search-using-geolocation-data-in-mysql/ */

        // Constants related to the surface of the Earth
        $earths_radius = 6371;
        $surface_distance_coeffient = 111.320;

        // Spherical Law of Cosines
        $distance_formula = sprintf('%s * ACOS( SIN(RADIANS(geolatitude)) * SIN(RADIANS(%s)) + COS(RADIANS(geolongitude - %s)) * COS(RADIANS(geolatitude)) * COS(RADIANS(%s)) )', $earths_radius, $lat, $lng, $lat); 

        // Create a bounding box to reduce the scope of our search
        $lng_b1 = $lng - $radius / abs(cos(deg2rad($lat)) * $surface_distance_coeffient);
        $lng_b2 = $lng + $radius / abs(cos(deg2rad($lat)) * $surface_distance_coeffient);
        $lat_b1 = $lat - $radius / $surface_distance_coeffient;
        $lat_b2 = $lat + $radius / $surface_distance_coeffient;

        // Construct our sql statement
        $sql = sprintf('SELECT *, (%s) AS distance FROM restaurants AS Restaurant WHERE (geolatitude BETWEEN %s AND %s) AND (geolongitude BETWEEN %s AND %s) HAVING distance < %s ORDER BY distance ASC LIMIT 0, %s', $distance_formula, $lat_b1, $lat_b2, $lng_b1, $lng_b2, $radius, $limit);

        return $this->query($sql);
}

Thank you for any help

FishWave · Accepted Answer · 2013-03-18 20:14:17Z

Now I have my solution:

    public function geoRest($lat, $lng, $radius, $limit) {
        /* Source: http://funkjedi.com/technology/308-search-using-geolocation-data-in-mysql/ */

        // Constants related to the surface of the Earth
        $earths_radius = 6371;
        $surface_distance_coeffient = 111.320;

        // Spherical Law of Cosines
        //$distance_formula ='$earths_radius * ACOS( SIN(RADIANS(geolatitude)) * SIN(RADIANS($lat)) + COS(RADIANS(geolongitude - $lng)) * COS(RADIANS(geolatitude)) * COS(RADIANS($lat)) )';
        $distance_formula = sprintf('%s * ACOS( SIN(RADIANS(geolatitude)) * SIN(RADIANS(%s)) + COS(RADIANS(geolongitude - %s)) * COS(RADIANS(geolatitude)) * COS(RADIANS(%s)) )', $earths_radius, $lat, $lng, $lat); 
        // Create a bounding box to reduce the scope of our search
        $lng_b1 = $lng - $radius / abs(cos(deg2rad($lat)) * $surface_distance_coeffient);
        $lng_b2 = $lng + $radius / abs(cos(deg2rad($lat)) * $surface_distance_coeffient);
        $lat_b1 = $lat - $radius / $surface_distance_coeffient;
        $lat_b2 = $lat + $radius / $surface_distance_coeffient;

        // Construct our sql statement
        //$sql = sprintf('SELECT *, (%s) AS distance FROM restaurants AS Restaurant WHERE (geolatitude BETWEEN %s AND %s) AND (geolongitude BETWEEN %s AND %s) HAVING distance < %s ORDER BY distance ASC LIMIT 0, %s', $distance_formula, $lat_b1, $lat_b2, $lng_b1, $lng_b2, $radius, $limit);
        //return $this->query($sql);
        //$options['fields'] = array('*',$distance_formula.' AS distance');

        $this->virtualFields['distance'] = $distance_formula;
        $options['fields'] = array('*');
        $options['conditions'] = array(
                            'Restaurant.geolatitude BETWEEN ? AND ?' => array($lat_b1, $lat_b2),
                            'AND' => array('Restaurant.geolongitude BETWEEN ? AND ?' => array($lng_b1, $lng_b2)),
                            'AND' => array('NOT' => array('Restaurant.geolongitude' => 0)),
                            'AND' => array('NOT' => array('Restaurant.geolatitude' => 0)),
                            'AND' => array('NOT' => array('Restaurant.geolongitude' => null)),
                            'AND' => array('NOT' => array('Restaurant.geolatitude' => null))
                            );
        $options['contain'] = array(
                            'Menu' => array(
                                            'conditions' => array(
                                                                  'Menu.active' => '1'
                                                                  )
                                            )
        );

        $options['order'] = 'Restaurant.distance ASC';
        $options['limit'] = $limit;

        //return $options;

        return $this->find('all', $options);

Unfortunately the 'contain' does not work, but that's another problem I'm working on. If anybody has a solution for that It would be great!

Alvaro · Accepted Answer · 2013-03-04 11:48:37Z

0

Take a look at the Custom query pagination. There are some ways to paginate using custom queries.

answered Mar 4, 2013 at 11:48

Alvaro

41.7k31 gold badges174 silver badges352 bronze badges

Collectives™ on Stack Overflow

SQL-query in cakephp-style

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related