error with translated tables

[lichtner]

Hi Jakub, commit 3419027 broke my code. I use NotORM with language extension inspired by (http://php.vrana.cz/co-je-noveho-v-notorm.php). I have text fields translated in second table (e.g. article and article_translation(article_id, lang, title,....)). After I applied "git pull" this commit on this code:

foreach ($notorm->article() as $article) {
    echo "$article[id], $article[title]\n";
}

I got this error:

SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens

File: /home/marek/www/new/libs/notorm/NotORM/Result.php   Line: 157

155:            }
156:            $return = $this->notORM->connection->prepare($query);
157:            if (!$return || !$return->execute(array_map(array($this, 'formatValue'), $parameters))) {
158:                return false;
159:            }
160:            return $return;
161:        }

Do you know where the problem is?
thanks
Marek.

[vrana]

Please enable debug ($notorm->debug = true from command line) and send me the executed queries.

[lichtner]

correct queries before commit are:

SELECT * FROM category;

with params:

array(0) 

and second query

SELECT * 
FROM category_strings 
WHERE (category_strings.category_id IN (1, 2, 3, 4, 5, 6, 7, 12, 13, 14, 64, 60, 61, 65)) 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1)

with params:

array(2) [
     0 => "sk" (2)
     1 => "cz" (2)
]

---

bad queries after commit are (with same params):

SELECT * FROM category;

and second:

(
SELECT * 
FROM category_strings 
WHERE (category_id = '1') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '2') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '3') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '4') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '5') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '6') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '7') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '12') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '13') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '14') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '64') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '60') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '61') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0) UNION (
SELECT * 
FROM category_strings 
WHERE (category_id = '65') 
ORDER BY category_strings.category_id, IF(language = ?, 0, 1), IF(language = ?, 0, 1) 
LIMIT 1 OFFSET 0)

[lichtner]

I realize where the problem is. My Implementation of NotORM_Row is:

function offsetExists($offset) {
    if (!isset($this->row[$offset])) {
        $table = $this->result->table . "_translation";
        $result = $this->$table()
                    ->order(new NotORM_Literal('IF(language = ?, 0, 1)', 'cz'))
                    ->order(new NotORM_Literal('IF(language = ?, 0, 1)', 'en'))->limit(1);
        foreach ($result as $row) {
            foreach ($row as $key => $val) {
                $this->row[$key] = $val;
            }
        }
    }
    return parent::offsetExists($offset);
}

I use this because I need specify primary, secondary and also ternary language if primary and secondary don't exists.
and in this commit you change implementation for using UNION but params are only two array('cz','en').

whne I change my code, without NotORM_Literal, result is ok

        $result = $this->$table()
                    ->order("IF(language = '{$this->primaryLang}', 0, 1)")
                    ->order("IF(language = '{$this->secondaryLang}', 0, 1)")->limit(1);

may be this problem (UNION with less params) is bug, I am not sure, but you can close this issue.