MoLow MoLow - 17 days ago 7
MySQL Question

loop to generate a unique url-frinedly route

I have a table in mariadb in this structure,

CREATE TABLE `items` (
`id` char(36), `route` varchar(255), `value` text,
PRIMARY KEY (`id`),
UNIQUE KEY `route` (`route`)
)


I use the
route
column to get user-friendy urls, such as http://www.examle.com/this-is-the-route-of-an-item

When a user creates a new item, apart from omitting spaces and illegal characters, I would like to "catch" cases where the route chosen for the new item is in use, and generate a valid route.

For example, if
route-of-an-item
is already in use, i would fallback to
route-of-an-item-a
, or
route-of-an-item-b
, etc.

The naive solution could be querying db in a loop, for example (kind of pseudo code):

var additionalChars = "";
while (db.query("select count * from `items` where `route`='" + route + "-" + additionalChars + "'"))
additionalChars = nextAdditionalChars(additionalChars);

finalRoute = route + '-' + additionalChars;


Since this involves querying the db many times I thought of another solution.

var additionalChars = "";
var usedRoutes = db.query("select `route` from `items` where `route` like '" + route + "%'");
while(usedRoutes.contains(route + '-' + additionalChars))
additionalChars = nextAdditionalChars(additionalChars);

finalRoute = route + '-' + additionalChars;


Is there any better way to approach this kind of a problem?

Am I correct that the second solution would perform better?

If I use the second solution, should I add a fulltext index to route field?

Answer

Ok, so after consulting with a colleague I ended up using solution 2, here is the code (node.js), in case anyone faces this problem:

db access

var route = req.body.route || 'noname';    
route = route.replace(/[\s_]/g, '-').toLowerCase().replace(/[^0-9a-z\u0591-\u05F4\u0621-\u064A\-_\s]/g, "").replace(/_+/g, ' ').trim().replace(/[\s_]+/g, '-');

var isArabic = (/[\u0621-\u064A]/g).test(route),
    isHebrew = (/[\u0591-\u05F4]/g).test(route),
    lang = isArabic ? 'ar' : (isHebrew ? 'he' : 'en');

Items.findAll({ where: { route: { $like: route + '%' } }, attributes: ['route'] })
    .then((items) => {
        var routes = _.keyBy(items, 'route'),
            prefix = '';

        while (routes[route + (prefix ? '-' + prefix : '')])
            prefix = charactersCount(prefix, lang);

        Items.create({ route: route + (prefix ? '-' + prefix : ''), value: req.body.value })
        .then(function(item){ 
            res.send({ item: _.pick(item, ['id', 'route', 'author_id', 'created_at']) })
        })
        .catch(function(){ res.sendStatus(500)});
    })
    .catch(function(){ res.sendStatus(500) });

generate additional characters

var chars = {
    ar: { val: "اﻻبتثجحخدذرزسشصضطظعغفقكلمنهةوىي", len: 0 },
    he: { val: "אבגדהוזחטיכלמנסעפצקרשת", len: 0 },
    en: { val: "abcdefghijklmnopqrstuvwxyz", len: 0 }
};
_.forEach(chars, (c) => { c.len = c.val.length });

function charactersCount (current, lang) => {
    if (!current) return chars[lang].val[0];
    lang = lang || 'en';
    var curr = current.split(''),
        len = curr.length,
        pointer = len,
        lastIndex;

    while ((lastIndex = chars[lang].val.indexOf(curr[--pointer]) + 1) >= chars[lang].len) curr[pointer] = chars[lang].val[0];
    if (pointer < 0) { curr.unshift(''); pointer++; }
    curr[pointer] = chars[lang].val[lastIndex];

    return curr.join('');
}

so I end up with one select query and one inset query, and prevent the clashes in node's side.

and a fulltext index is not needed since the % apears only at the end of the like operater

Comments