Is it possible to make composite sql request with phalcon?

[Александр Васин]

I need to perform following sql query (with sub-query) in phalcon, as i found this query has the best perfomance with maxmind db for innodb tables:

    SELECT * FROM Geoip\Block 

    INNER JOIN ( 

        SELECT MAX(ip_start) AS start 

        FROM Geoip\Block 

        WHERE ip_start <= %s 

    ) AS s ON (ip_start = s.start) 

    WHERE ip_end >= %s'

I have Geoip\Block model, that refers to geoip_block table. If i try to use with this query following code, PHQL will throw an error:

    $manager = Phalcon\DI::getDefault()->getShared('modelsManager');

    $query = $manager->createQuery($queryWithSubQuery);

    // throws Syntax error, unexpected token (, near to ' SELECT MAX(ip_start) AS start FROM

    // Geoip\Block WHERE ip_start <= $ip) AS s ON (ip_start = s.start) WHERE ip_end >= $ip'

Is it technically possible? I want to define static method, that will accept ip address and will return model instance.

[Andres Gutierrez]

Hi Alexander, 

Sub-queries aren't still supported in PHQL, I suggest you the following options:

1. Using two queries

$maxIp = Geoip\Block::maximum(array(

   'column' => 'ip_start',

   'conditions' => 'ip_start <= ?0',

   'bind' => array($start)

));

$blocks = Geoip\Block::find(array(

   'conditions' => 'ip_start = ?0 AND ip_end >= ?1',

   'bind' => array($maxIp, $end)

));

2. Using a RAW sql in your model

public static function getBlock($start, $end)

{

$sql = 'SELECT * FROM Geoip\Block 

INNER JOIN ( 

SELECT MAX(ip_start) AS start 

FROM Geoip\Block 

WHERE ip_start <= ? 

) AS s ON (ip_start = s.start) 

WHERE ip_end >= ?';

return $this

->getConnection()

->query($sql, array($start, $end))

->fetchAll();

}

However, I'm not sure if you really need to use the ORM here, since the maxmind database is mostly read-only, you can better implement a user component that provide this info querying directly to the database (same as above):

class GeoIp extends Phalcon\DI\Injectable

{

public function getBlock($start, $end)

{

$sql = 'SELECT * FROM Geoip\Block 

INNER JOIN ( 

SELECT MAX(ip_start) AS start 

FROM Geoip\Block 

WHERE ip_start <= ? 

) AS s ON (ip_start = s.start) 

WHERE ip_end >= ?';

return $this->db->query($sql, array($start, $end))->fetchAll();

}

}

2013/2/4 Александр Васин <true...@gmail.com>

--
You received this message because you are subscribed to the Google Groups "Phalcon PHP Framework" group.
To unsubscribe from this group and stop receiving emails from it, send an email to phalcon+u...@googlegroups.com.
To post to this group, send email to pha...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msg/phalcon/-/uKTEAvg8InMJ.
For more options, visit https://groups.google.com/groups/opt_out.
 
 

[Александр Васин]

Hmm.. wow.. thank you, i did not knew that this is possible :)

вторник, 5 февраля 2013 г., 5:11:21 UTC+4 пользователь Andres Gutierrez написал: