Re: [fw-db] [Postgresql] Syntax error

Hi!

On Fri, Dec 19, 2008 at 10:08 AM, Thomas VEQUAUD <thomas.vequaud@gmail.com> wrote:

Hi there!

I was running a MySQL database but from now I need more stuff... So I
decided to install a PostgreSQL database on my development
environment. I added a [pgsql] section in my config.ini file...
Anyway, when I try to launch the index of my website, I get a SQL
syntax error with the following query :
SELECT *
FROM "user" AS "us"
INNER JOIN "is" ON us.user_id = is.user_id
INNER JOIN "role" AS "ro" ON is.role_id = ro.role_id
WHERE (ro.role_type <> 2 AND ro.role_type <> 1)
ORDER BY "user_date" DESC

This query is generated by my model class 'User' with the following method:
public function         getLastRegistered()
{
       $select = $this->select();
       $select->setIntegrityCheck(false)
                  ->from(array('us' => 'user'))
                  ->join('is', 'us.user_id = is.user_id')
                  ->join(array('ro' => 'role'), 'is.role_id = ro.role_id')
                  ->where('ro.role_type <> '.ROLE_TYPE_INACTIVE.' AND ro.role_type
<> '.ROLE_TYPE_BANNED)
                  ->order('user_date DESC')
                  ->limit(0, 10);
       return ($this->fetchAll($select)->toArray());
}

Do you actually have a table called 'is' ?   In that php code above, you've specified an alias for user (us) and for role (ro), but not for 'is'. I would be highly surprised if you do actually have a table called 'is' since 'is' is a reserved word in most RDBMS (a la    SELECT *  FROM foo WHERE bar IS NULL  etc)

Try specifying the full table name (or give it a different alias) and see what happens...

HTH

Cheers,
Thunder