Hi bumciach,
Im glad that you liked this topic.
Really the main goal of this "how to write a standard module" would be guidelines for writing a strong, understandable, easy to debug and consistent
base codes for all xoops modules.
Therefore because this standard has to be basic I did not add special needs (eg: special queries like joining).
but if a developer follow this guideline the output module is ready for adding any special needs too.
for example for joining purposes, the most important thing is not adding a
hard-coded table nameAnd if the developer followed this guideline:
1- If module
had one class for each table we should use
handlers to find/use table name and avoid hard-code.
2- If module used
helper class it will be easier too
So for example to join (of course you dont need join which I will describe later) we can improve your query:
class mymoduleArticleHandler extends XoopsPersistableObjectHandler
{
function &getObjects($args)
{
$sql = "SELECT t2.name AS category_name, t1.* FROM {$this->table} t1, LEFT JOIN {$this->MODULE_X->getHandler('category')->table} t2 ON t2.category_id = t1.category_id"; //need add initVar('category_name') to Article class
and $this->MODULE_X is the helper class for module x.(module x can be the module itself)
The above is without hard-code and very understandable for everybody.
Then I like to discuss about your special need and your exampled code(which is off-topic so maybe it is better to be in another topic
):
Side note 1:
I dont know these codes you wrote comes from what module? but they are not good. One can do this without JOIN by enhancing that
Easy way Quote:
For example: displays list articles with category names.
Easy way:
1 - read articles from database by using article class
2 - for every article object do access to database by using category class
why do you (or that module developer) think you cannot follow that easy way more effective and with much less codes than the JOIN way?
It can be done just by
two queries.1 - read articles from database by using article handler class
SELECT * FROM {$this->table}
2- for
all above article category Ids do access to database by using category handler class.
SELECT category_id, category_name FROM {$this->table} WHERE category_id IN {$articles_category_id}
which $articles_category_id is all category ids you get from the first query.
To implement the above you even dont need to write special function (function &getObjects($args))
All can be done in front side
I commented the below code for you to see how it can be done with ease
$articleH = xoops_getmodulehandler('article', 'MODULE');
$categoryH = xoops_getmodulehandler('category', 'MODULE');
$criteriaArticle = new CriteriaCompo();
// continue your criteria here for $criteriaArticle
// ...
// START first query for articles
// remember getAll($criteria = null, $fields = null, $asObject = true, $id_as_key = true)
$articleObjs = $articleH->getAll($criteriaArticle);
// END first query for articles
$articles_category_id = array();
foreach ($articleObjs as $artObj) {
$articles_category_id[] = $artObj->getVar("category_id");
// you can do any other thing here with articles!!!
// ...
}
// START second query for category names
$criteriaCat = new CriteriaCompo();
$criteriaCat->add(new Criteria("category_id", "(" . implode(", ", $articles_category_id) . ")", "IN"), "AND");
// you can add what fields you need here :D
$catFields = array("category_id","category_name");
$catNames = $categoryH->getAll($criteriaCat, $catFields, false);
// END second query for category names
// FINISH!!!
// usage: $catNames[$artObj->getVar("category_id")]["category_name"]
As i said before the above code is:
1- most effective code to do the job (more than JOIN) 2 simple queries are better than one query with JOIN.
2- no need to write any code in the handler class
3- can be extend very easy. eg: if you need category_created time you can add the field.
Side note 2:
Anyway IMO your query will not work because you didnt add db prefix:
instead of:
mymodule_article
you have to use:
$this->db->prefix('mymodule_article')
but using hadlers and helper class you will not need to think about such a annoying thing (like i must add prefix here or not)
Side note 3:
If you are very tight for using only one query you should add one field category_name to articles table.
//need add initVar('category_name') to Article class
As the above comment after your query suggested, If you really need to display the category name alongside an article in
many links of your module it is better to add another field called "category_name" in mymodule_article table.
But generally you dont need that and i dont advise it
