Phalcon execute sql query in the model

Question

Currently I decided to look at Phalcon php as an alternate php framework to Codeigniter. I followed the tutorials on the website and it is pretty sweet the way it works. I'm still trying to wrap my head around few things though.

From my understanding, Models are bind with Database and mapped to the table in the database. I have a project, where I need to use 2 or more databases. The project has a backend (one database) and multiple frontends (another database). The easiest way is to run custom MySQL queries to fetch data from multiple databases. I'm not sure how to do that from the Model in the Phalcon. I looked over stackoverflow, tried few suggestions, but still no luck.

I would guess there should be some easy way to do so from the Model like $result=$this->query("SELECT * FROM backend.users")->fetch(); but it doesn't work.

Here what I have:

Controller:

class SignupController extends \Phalcon\Mvc\Controller{

  function indexAction()
  {

  }

  function registerAction()
  {
    $user = new Users();
    $result=$user->saveNewUser();
    print_r($result); // Nothing

    //$result=$this->db->query("SELECT * FROM phalcon.system_users")->fetchAll();
    //print_r($result); // Works

    $this->view->disable();
   }

}

Model:

class Users extends Phalcon\Mvc\Model
{
   public function saveNewUser()
   {
      return $this->db; // how to run the query???

   }
}

Bootstrap:

try {

//Register an autoloader
$loader = new \Phalcon\Loader();
$loader->registerDirs(array(
    '../app/controllers/',
    '../app/models/'
))->register();

//Create a DI
$di = new Phalcon\DI\FactoryDefault();

//Setup the database service
$di->set('db', function(){
    return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => "localhost",
        "username" => "root",
        "password" => "123456",
        "dbname" => ""
    ));
});

//Setup the view component
$di->set('view', function(){
    $view = new \Phalcon\Mvc\View();
    $view->setViewsDir('../app/views/');
    return $view;
});

//Setup a base URI so that all generated URIs include the "tutorial" folder
$di->set('url', function(){
    $url = new \Phalcon\Mvc\Url();
    $url->setBaseUri('/phalcon/');
    return $url;
});

//Handle the request
$application = new \Phalcon\Mvc\Application($di);

echo $application->handle()->getContent();

} catch(\Phalcon\Exception $e) {
     echo "PhalconException: ", $e->getMessage();
}

I liked the way Codeigniter had it, not sure if Phalcon have a simple way of doing that. May be I need to load the extension or library to do that in the Model.

Thanks in advance!

Answer 1

Thanks jodator,

But it is a little bit different that I needed. I wanted to execute sql queries from the Model.

After spending more time testing and experimenting I think I figured it out. Just in case if someone has the same need to be able execute mysql queries from the model, here the way I figured it out. I'm not sure if impacts the performance, but it works.

Create a new Model class and call it for example BaseModel.php with the next inside:

class BaseModel extends Phalcon\Mvc\Model
{
   public $db;

   public function initialize()
   {
      $this->db=$this->getDi()->getShared('db');
   }   
} 

The BaseModel will extend the Phalcon Model and I created a public property called $db. Then on the initialize() I used $this->getDi()->getShared('db') to grab shared dependency injector and assigned it to our $this->db. Now all Models that extend this BaseModel will have access to that property. Then in my Users Model I have next:

class Users extends BaseModel // Users extends out BaseModel and will have access to $db
{

   public function test()
   {
      //print_r(\Phalcon\Di::getDefault()->getShared('db')); // This is the ugly way to grab the connection.

      $result=$this->db->query("SELECT * FROM phalcon.system_users"); // Working now
      echo $result->numRows();
      print_r($result->fetchAll());
   }

}

Now it works beautifully. I also figured out one more thing that might be interesting to someone who wants to use mysql queries (PDO) in Phalcon. I always use FETCH_ASSOC when fetching data, and to make life easier you can set up FETCH_ASSOC by default at the connection this way you don't need to setAttribute every time fetching data. Here is how I have done it. At the bootstrap, when setting DI class for the database connection, you can include the options....

//Setup the database service
$di->set('db', function(){
    return new \Phalcon\Db\Adapter\Pdo\Mysql(array(
        "host" => "localhost",
        "username" => "root",
        "password" => "123456",
        "dbname" => "",
        'options' => [PDO::ATTR_CASE => PDO::CASE_LOWER, PDO::ATTR_PERSISTENT => TRUE,PDO::ATTR_DEFAULT_FETCH_MODE=>PDO::FETCH_ASSOC],
    ));
});

As you see the last option sets PDO::ATTR_DEFAULT_FETCH_MODE.

If anyone has better way to do that, please post it here.

I hope it will help newbies to Phalcon like me :)

Answer 2

You need to setup two database services in you config, like:

//Setup the database service

$di->set('db', function(){ /* like in what you have */ });
// then the other one
$di->set('dbBackend', function(){ /* like in what you have */ });

Then in your models change the db service

public function initialize()
{
    parent::initialize();
    $this->setConnectionService('dbBackend');
    // or $this->setWriteConnectionService('dbB') and $this->setReadConnectionService('dbA')
}

Then just $model->create()

But if you want to run a query on different databases you can look at Pdo\Mysql Adapter.

Also models can have set table name $this->setSource() and schema $this->setSchema().