2

I'm looking for query building engine for PHP (not ORM!) which would satisfy some criteria specified below. Unfortunately, after looking into Doctrine, Propel, Adodb, Zend_Db, etc. I couldn't find any that really fits the profile - they are either too abstract (I don't need ORM-level abstraction) or don't support enough features. What I need is a library that would allow me to build the SQL query programmatically - I don't even need it to run it, though that would be OK too.

I really would like to avoid reinventing the wheel, so if you know something that fits the profile please bring it forward. If you think one of those named above fits (and I missed that), please tell me too.

MUST HAVE (if library doesn't have it, it's not useful for me):

  • Support PHP 5.2 (namespaced libraries can't be used for now, unfortunately)
  • Support programmatic query generation, including select expressions, order-by, limit, group-by, having, unions, outer joins, etc.
  • Support Mysql, Oracle oci8, MSSQL, DB2, Postrges
  • Support building named parametrized queries & prepared statements
  • Support adding conditions/joins dynamically at any point
  • Support for datatypes like datetime, etc. - e.g., properly formatting incoming/outgoing data, use proper comparison functions if needed, etc. (and of course proper quoting). We know types of all the fields in the code, so the library should allow us to tell it what each field is.
  • Stand-alone (easy to use without tons of other support classes)
  • Easy to extend and license allows extending on BSD-like terms
  • Clean PHP 5 code (no PHP 4 object-by-ref cruft, etc.)

NICE TO HAVE (we could implement that on top of "must have"s but would be happier if it were already done)

  • Support instantiating parametrized queries (full & partial) - i.e. after having built a query support giving it part of the params and generate new query with those params substituted
  • Support merging two queries (i.e. adding conditions and tables from one query into another)
  • Support query comments (including parametrizing them)

BONUS (this would really make us happy, but we could live without it for now)

  • Support serialization
  • Support caching

So, does anyone know such library?

StasM
  • 10,593
  • 6
  • 56
  • 103
  • 3
    May I ask what disqualified [Zend_Db](http://framework.zend.com/manual/en/zend.db.select.html)? It has low-level methods that seem to fit what you're describing: `$select->from(..)->where(..)->groupBy(..)`. It uses PDO which supports most (if not all) the databases specified and, from what I understand, is reasonably stand-alone. – Mike B Feb 06 '11 at 06:58
  • @Mike: It doesn't support data types AFAIK and only supports positional parameters, not named ones. At least that's what I am geting from the docs & the code. I'm considering using it anyway and extending it to support it but I don't want to do it if there's something that already has it. – StasM Feb 06 '11 at 07:09
  • I have a library that does some of that. eg: `Person::name('billy')->join(Town::table(), Town::id(), SQL::OP_EQUAL, Persion::town_id())->group_by(Town::id())->select(SQL::count(Persion::id()), Town::all_fields());` – Petah Feb 06 '11 at 11:38
  • @StasM This should work: `$select->where('id = :user_id', array('user_id' => 123));`. [Source](http://framework.zend.com/apidoc/core/Zend_Db/Select/Zend_Db_Select.html#methodwhere) – Mike B Feb 07 '11 at 19:26
  • @MikeB I'm not sure if it's supported for backends that don't do named binding. If it is, then only one point is missing for ZF, which is quite close. – StasM Feb 08 '11 at 01:04
  • Looking at Zend_Db_Statement::_parseParameters, if the driver doesn't support named params (like Mysqli) and you use it, it would throw exception. – StasM Feb 08 '11 at 01:10

1 Answers1

3

CodeIgniter is a safe bet for what you're looking for: http://codeigniter.com/user_guide/database/active_record.html

Also, it can easily be extended from it's core to do what you need. It also has query caching (one of your bonus items).

seangates
  • 1,467
  • 11
  • 28
  • I don't need ActiveRecord, I just need query generator. I don't like their API decisions too much - like having separate `or_where_not_in ` and `or_not_like` methods, and I couldn't see parameterized query support there. Also, their code uses evals and passing objects by reference. – StasM Feb 25 '11 at 18:26
  • 2
    @StasM Yes, it uses evals to load the database classes, but is never used with any query data or any user input. They are clearly separated ... never the twain shall meet. Also, if you don't like some of the core functions (e.g. `or_where_not_in`) don't use them. Extend the library and write your own. Either way, good luck! – seangates Mar 02 '11 at 01:44