Re: [fw-db] Zend_Db enhancements

On Jul 2, 2010, at 12:39 AM, FeIn wrote:

I have found that the need to use queries where the number of parameters for IN() is not known until run-time is pretty common.

There's another trick you can use, but it spoils the chance of using indexes:

  $zlist = array(1,2,3);

  $sql = "SELECT * FROM x WHERE y = ? AND ? LIKE CONCAT('%|', z, '|%')";

  $stmt = $db->prepare($sql);

  $stmt->execute(array(1, "|".implode("|", $zlist)."|"));

To visualize what this does, compare to this non-parameterized query:

  SELECT * FROM x WHERE y = 1 AND '|1|2|3|' LIKE CONCAT('%|', z, '|%')";

So if the pattern containing the value of column z matches any of the pipe-delimited values in the left hand side of the LIKE, then that comparison returns true.  This allows you to use a single SQL query parameter for a variable-length list.  But it can't benefit from an index on column z, so performance and scalability is bad.  Also, you have to choose a delimiter symbol that's guaranteed not to appear in any element of $zlist or anywhere in column z.

That solution comes from: http://stackoverflow.com/questions/337704/parameterizing-a-sql-in-clause  

The last option I can suggest is to use the quote() function, which already exists in Zend_Db:

  $zlist = array(1,2,3);

  $sql = "SELECT * FROM x WHERE y = ? AND z IN ( {$db->quote($zlist)} )";

  $stmt = $db->query($sql, array(1));

This interpolates the list of values instead of creating real SQL parameters, but it does support arrays and it applies the right type of quoting consistently.  Also it benefits from an index on z.

Since the use case you're after (arrays with unknown number of elements) means you have to re-prepare the query for every execution anyway, it shouldn't be a problem to re-create the SQL string each time either.

Requires

Bill Karwin