Propel multiple-parameter binding in where() clause

Question

I'm trying to run this query on Propel 1.6 with symfony 1.4.20.

I want to bind 2 parameters onto this subquery but its not working.

$paginas = PaginaQuery::create()                              
                               ->where("pagina.id not in (select id from cliente_artista where cliente_artista.cliente_id = ? and cliente_artista.culture = ?)"
                                       ,array('XXX', 'en')
                                      )
                          ->limit(5)
                          ->find();

This gives me the error:

Cannot determine the column to bind to the parameter in clause

I also found this post but there is no answer (https://groups.google.com/forum/?fromgroups=#!topic/propel-users/2Ge8EsTgoBg)

I don't have time to write this up as an answer, but you should look at the documentation for subqueries which I think will let you do this in one DB hit. — Jordan Kasper

Qoheleth-Tech Qoheleth-Tech · Accepted Answer · 2013-03-26T23:25:11

Instead of using placeholders. You may use $id and $culture:

//first, get an array of the id's
//define your vars
$id = $your_id_param;
$culture = 'en';
$cliente_artistas = ClienteArtistaQuery::create()
                        ->select('id')
                        ->distinct()
                        ->filterByClienteId($id)
                        ->filterByCulture($culture)
                        ->find();

$paginas = PaginaQuery::create()                              
               ->where("pagina.id NOT IN ?", $cliente_artistas)
               ->limit(5)
               ->find();

If this has to be done in one query, recommend using raw sql and binding the parameters into the PDO statement (but then you lose the convenience of PropelObjectCollections):

  public function getResultSet($id, $culture) {
    $id = $id_param;
    $culture = $culture_param;
    $sql = <<<ENDSQL
SELECT * from pagina
WHERE id NOT IN (SELECT distinct id 
                 FROM cliente_artista 
                 WHERE cliente_id = ?
                 AND culture = ?
                 )
LIMIT 5
ENDSQL;
    $connection = Propel::getConnection();
    $statement = $connection->prepare($sql);
    $statement->bindValue(1, $id);
    $statement->bindValue(2, $culture);
    $statement->execute();
    $resultset = $statement->fetchAll(PDO::FETCH_ASSOC); // or whatever you need

    if (! count($resultset) >= 1) {
      // Handle empty resultset
    }

    return $resultset;
  }

You could also write some query methods to use propel orm query methods. Ofcourse, the propel api is beneficial reference. There are several ways to do this. I have indicated one method here which should work for you.

EDIT: Here's an idea on doing this as one query [since useSelectQuery() requires 'relation' name], this idea assumes tables are not related but that id's are:

$paginas = PaginaQuery::create()                              
           ->addJoin(PaginaPeer::ID, ClienteArtistaPeer::CLIENTE_ID, Criteria::LEFT_JOIN)
           ->where("ClienteArtista.Id <> ?", $id)
           ->where("ClienteArtista.Culture <> ?", $culture)
           ->select(array('your','pagina','column','array'))
           ->limit(5)
           ->find();

Propel multiple-parameter binding in where() clause

1 Answers