We have moved our forum to GitHub Discussions. For questions about Phalcon v3/v4/v5 you can visit here and for Phalcon v6 here.
  1. Home
  2. Database

When to use custom queries

Assume phalcon devtools is in your $PATH, a valid hosts entry for your application domain, an apache vhost mapped to your hosts file entry, and a database, host, user and password exist.

1 - phalcon create-project projectname

2 - cd projectname

3 - at this point update /app/config/config.php with you Data Source Name connectivity details

4 - *phalcon scaffold projects creates models, controllers and views for projects

5 - Inside an action within the ProjectsController.php

// Define a SQL string query

$qry = 'SELECT * FROM projects';

// Run query with fetchAll and add it to a view template available as $projects

$this->view->projects = $this->db->fetchAll($qry);

Question: 1) What is the best practice for PDO SQL query location in an application? I don't think they should live in the Controller. I will be looking at creating a Class that contains a list of methods for complex data manipulations.

1


2.3k
edited Dec '14

Look up another thread on multi-tier applications. I went through a ton of this learning experience. Here are some good google searches to get you going. There are multiple different names or semantics for what you need to learn. Some mean basically the same thing.

  • Onion architecture
  • Multi-tiered application
  • Service layer / service pattern
  • Repository layer / repository pattern
  • DAO - Data Access Object pattern
  • DTO - Data Transfer Object (and how it is different from DB model/entity)
  • Enterprise design patterns

You essentially "slice up" your application into layers or tiers. Our "enterprise" app looks roughly like this:

  • request -> controller
  • controller -> service
  • service -> repository (dao)
  • repository -> query class (optional, if we have big queries then we push this into a separate "query" class that the repo method calls)
  • repository -> dbal (doctrine, spot, aura.db) essentially some wrapper convenience around PDO
  • dbal -> pdo

here we get the data from PDO as associative array, now we go back out to the response

  • pdo (as assoc array) -> dbal (might send raw array, or might hydrate into entities (doctrine, spot, etc)
  • repository sends array or array of entities to service
  • service -> dto assembler (turn PDO array or entities into DTOs)
  • dto -> controller
  • controller -> response mapper (takes dto and turns into a HAL object)

at this point, reponse mapper returns a HAL ojbect which is rendered into json/xml and set as the response content.



2.3k

I took a Construx course where the instructor Rob (used to be director at monster.com) coined the phrase "transaction service". At least I hope I am remember that correctly.

Essentially, your code might start out as a prototype by having queries directly in your controller. This is pretty "crap" coding, doesnt belong in a controller, but for rapid app dev that could be where it starts.

The next phase would be to move the query stuff into a data access layer. So your controller might now call a UserRepo->findById(123).

Essentially you continue refactoring, adding layers, moving towards a more SOLID-conforming design.