Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Already on GitHub? Sign in to your account

error with translated tables #23

Open
lichtner opened this Issue Sep 23, 2011 · 3 comments

Comments

Projects
None yet
2 participants
Contributor

lichtner commented Sep 23, 2011

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.

Owner

vrana commented Sep 26, 2011

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

Contributor

lichtner commented Sep 27, 2011

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)
Contributor

lichtner commented Sep 27, 2011

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment