Solved thread

This post is marked as solved. If you think the information contained on this thread must be part of the official documentation, please contribute submitting a pull request to its repository.

Routes stored in database

I have categories table in my DB. There is seoUrl column in this table. I wan't to generate routes that will run ArticleController in case URL looks like xxx.com/seoURLfromDB. For now I have next code in my app/config/services.php:

$di->set('router', function () {

    $router = new Router();

    $categories = Categories::find('active = 1');
    if(count($categories)) {
        foreach($categories as $category) {
            $router->add(
            '/'.$category->seoUrl,
                array(
                'controller' => 'article',
                'action' => 'index'
                )
            );
        }
    }

    return $router;
});

All works perfect (any URL that I have in DB opens my ArticleController. Is it a correct way of doing this? Or there is some better variant?



3.5k
edited Sep '14

It depends (as always ;)). It those categories are specific only for one controller/module and you have other routes as well you can use route like:

$router->add( "/:slug", array( "controller" => "article", "action" => "category", "slug" => 1 ) );

or

$router->add( "/([a-z0-9-]*)", array( "controller" => "article", "action" => "category", "slug" => 1 ) );

... and add this route at the very begining so any other "static" route will overwrite it. Then perform checking in specific controller/action. You should do this additional checking anyway but your resolution is ok so it's up to you :)



562
edited Sep '14

Could you give us an example pattern of your seo urls? The best way to deal with dynamic and static routes depends on url patterns. In your case (if you implemented your code as above) i can't see how your article controller handles the routes internally. Every route would return the exact same result. I would prefer the first way from Arius:

$router->add( "/:slug", array( "controller" => "article", "action" => "category", "slug" => 1 ) );

But don't put it at the very begining. Dynamic routes must be handled at last because a static route like "/resetpassword" would match to "/:slug" and results in being handled by your article controller instead of i.e. user controller. The first match found (in order of being added to the router) will be used, which leads into a better matching routes performance.

I assume that your controller action is doing a sql-query to get its content, so just put a seoUrl-column (or slug) to your article table and let the indexAction search the right article by seoUrl/slug $article = Article::findFirst(array("seoUrl = :slug", "bind" => array(":slug" => $slug))).

There are some reasons for this. 1) You've got to catch all routes which can't be handled either way, in this case you can forward or redirect to a 404 page when there was no article found

2) You're performing an sql-query to setup your routes + you've got to do a query to get your contents inside of your controller which result in atleast 2 queries while one could've been avoided

Edit: If you really want to save static routes via database, integrate a caching service so you only need to fetch data once a day. This will let you administrate your static routes by backendsystem and results in a better performance than querying the database every time a user requests a page.

TobiasJ, the sample provided by me was just an example. I can store seoUrl's in several tables and add route to separate controllers (articles to ArticleController, news to NewsController etc):

$categories = Categories::find('active = 1');
    if(count($categories)) {
        foreach($categories as $category) {
            $router->add(
            '/'.$category->seoUrl,
                array(
                'controller' => 'article',
                'action' => 'index'
                )
            );
        }
 }
 $news = News::find('active = 1');
    if(count($news)) {
        foreach($news as $news_item) {
            $router->add(
            '/'.$news_item->seoUrl,
                array(
                'controller' => 'news',
                'action' => 'index'
                )
            );
        }
 }

Your point is very nice. But what to do in next situation: For example I have 2 tables (managed from back-end) with unique seoUrl columns inside. Now I want that all requests with URL's from articles table goes to ArticleController and all URL's from news to NewsController. How can I achive this using your logic? As I can see for now, I can do it only the way I showed in my first comment.

My universal router:

CREATE TABLE IF NOT EXISTS `router` (
`id` int(10) unsigned NOT NULL,
  `uri` varchar(255) COLLATE utf8_general_mysql500_ci NOT NULL,
  `uri_index` smallint(5) unsigned NOT NULL,
  `module` varchar(32) COLLATE utf8_general_mysql500_ci NOT NULL,
  `controller` varchar(32) COLLATE utf8_general_mysql500_ci NOT NULL,
  `action` varchar(32) COLLATE utf8_general_mysql500_ci DEFAULT NULL,
  `params` text COLLATE utf8_general_mysql500_ci
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_general_mysql500_ci;

ALTER TABLE `router`
 ADD PRIMARY KEY (`id`), ADD UNIQUE KEY `uri` (`uri`,`uri_index`);
class Router extends \Phalcon\Mvc\Model
{
    public static function setRouter($di)
    {
        $config = $di->getConfig()->router;
        $di->set('router', function () use ($config)
        {
            $router = new \Phalcon\Mvc\Router();

            $router->clear();
            $router->setUriSource(\Phalcon\Mvc\Router::URI_SOURCE_SERVER_REQUEST_URI);
            $router->setDefaultModule($config->defaultModule);

            preg_match('~/*([\w\d-_]+)~iu', $router->getRewriteUri(), $matches);

            if (!empty($matches[1]))
            {
                list($uri, $uriIndex) = explode('-', $matches[1]);

                $route = self::findFirst([
                    'conditions' => 'uri = ?1 AND uri_index = ?2',
                    'bind' => [1 => $uri, 2 => (!empty($uriIndex)) ? (int)$uriIndex : 0]
                ]);

                if ($route)
                {
                    $router->add(
                        $router->getRewriteUri(),
                        [
                            'module' => $route->module,
                            'controller' => $route->controller,
                            'action' => $route->action,
                            'params' => unserialize($route->params)
                        ]
                    );
                }
            }

            $router->add(
                "/([a-zA-Z0-9_-]++)",
                [
                    'module' => 1
                ]
            );
            $router->add(
                "/([a-zA-Z0-9_-]++)/([a-zA-Z0-9\._]++)",
                [
                    'module' => 1,
                    'controller' => 2
                ]
            );
            $router->add(
                "/([a-zA-Z0-9_-]++)/([a-zA-Z0-9\._]++)/([a-zA-Z0-9\._]++)",
                [
                    'module' => 1,
                    'controller' => 2,
                    'action' => 3
                ]
            );
            $router->add(
                "/([a-zA-Z0-9_-]++)/([a-zA-Z0-9\._]++)/([a-zA-Z0-9\._]++)(/.*+)?+",
                [
                    'module' => 1,
                    'controller' => 2,
                    'action' => 3,
                    'params' => 4
                ]
            );

            return $router;
        });
    }
}

DestinyMKas, it is something I asked for. But I have one question about your sample.

Why you inserted $router->add() blocks after your conditions? I think it must be inserted only if rout not founded yet. Something like that:

if (!empty($matches[1])) {
    ...
    if ($route) {
        ...
        return $router; // return $router since we no need to add more routes.
    }
}


562

So if i'm getting it right, you need to save all routes in one table similar to the solution DestinyMKas posted. But i would really recommend that you cache the resultset in this case. The router model DestinyMKas posted cannot be cached, since it only matches at maximum one database route per request (in order to cache all routes, first you've got to get all routes). Buddy of mine told me the routing model of DestinyMKas remembers him to the one "Wordpress" uses, which isn't popular for best practise code.

All other non static routes should be handled like rest of DestinyMKas posted routes (without the preg_match database ones.

TobiasJ, what you said about next implementation (mix of my variant and DestinyMKas one):

app/config/services.php:

$di->set('router', function () {

    $router = new MyRouter();

});

app/models/MyRouter.php:

use Phalcon\Mvc\Router as Router;

class MyRouter extends \Phalcon\Mvc\Model
{
    public static function __construct()
    {
         $router = new Router();
         $routes = self::find(array(
             "cache" => array("key" => "my-cache")
         ));
         if(count($routes)) {
            foreach($routes as $route) {
                $router->add(
                '/'.$route->seoUrl,
                    array(
                    'controller' =>$route->controller,
                    'action' =>$route->action
                    )
                );
          }
        }
        return $router;
    }
}

Is it good practice?

First, yes. It is better to return $router immediatly after getting route from database.

Why you want to get all routes from database? How much of routes you can have? Lets think how long will take to foreach 1,000,000 routes and cache them?

And for TobiasJ, similar practise is in Magento e-commerce platform, so I know it works fast even with 1000000+ records and it is easy maintainable.

It will be cached (I will cache the request untill my database table will changed), so I will have all my routes in cache and will have no need to query my DB on every request.

Anyway, can you fill your database with 100000 or even 1000000 records, and then report us how fast your cache works?

edited Sep '14

I have no idea (I even not start to create my site), thats why asked for better solution. All I need is managable URL from back-end side.



562
Accepted
answer

OT: @DestinyMKas: I do know magento and I worked (and I'm still working) with magento. Magento isn't really known for its good response timings and high performance on a standart config (this means without additional systems like memcached/APC and no modifications to core functions done). But the way to cache routes if you really need to save the full seoUrl into a database, should activate similar to the caching system of magento. You don't wait until a user hits the "cache routes" button by requesting a page, you cache your routes before a user requests via backend and/or automatically if something in the routing table has changed. If you've got 1.000.000 routes and still use magento without any additional caching systems, well than it's up to you to request a page, get a cup of coffee and if beeing lucky got that page responsed - but I would never recommend this for a shop beeing that big justifying 1.000.000 routes.

BTT: If you really want to save all routes via database (and you only should do that if your url doesn't looks like domain.tld/news/newsarticleslug or domain.tld/controller/action/param(i.e. seoUrl|slug), than pick one of the router models in this discussion and get your work started. If you later on feel that the router model doesn't suite your project anymore just change it the way you want - this is the beauty of soc (seperation of concerns) and the mvc pattern.