Not for 'how-to' coding questions but PHP theory instead, this forum is here for those of us who wish to learn about design aspects of programming with PHP.
I'm having difficulty finding a good way of creating expressions in my db abstraction class(es). Closest to what I'm aiming at comes eZ Components but it seems like so much clutter to create even simple things:
It's the [strike]bolded[/strike] part passed to the where() that is relevant. If writing SQL scares you, this will be worse. So what is an elegant way to create SQL expressions?
Thanks. I've been thinking along those lines also. The problem with it is that it only allows one level of expressions. Eg the following doesn't seem to be possible with it:
WHERE SUBSTRING(name, 1, 1) > k
The other possibility is to use arrays to indicate an expression.
where (array('num', 12, '>'));
You can nest those but it becomes as unwieldly as eZ components' solution.
There are also the issues of database specific escaping and dealing with nonstandard LIMIT syntax. Skeleton has a stand-alone component for this that jcart and pytrin worked on (Credit to Mordred for security audit/scolding ). The problem with this type of solution is the law of diminishing returns.
jayshields wrote:In my opinion, it's impossible to simplify SQL unless you are prepared to limit its capabilities.
This is very true. Writing SQL will always be easier, more flexible and more expressive. The law of diminshing returns was never more obvious that to writers of SQL and OR/M classes!
But there are good reasons for writing these classes -- with limited functionality. In the Skeleton framework the main reason they exist is for internal use within the TableDataGateway, ActiveRecord, DataMapper and other DB classes that need to generate SQL from arrays containing data or conditions. They can be very handy for a lot of very common SQL. Especially INSERTs, UPDATEs, DELETEs and simple SELECTs. Using objects can actually be clearer, cleaner and more error free than writing SQL. Complex SELECTs are where the trouble comes.
today. But regular expressions are a tad too slow. I could cheat and use str_replace for mysql-expressions and that seems about 10 times faster, but still too slow.
edit:
here's some (probably very naive) testing done on an older machine (processor AMD Athlon XP 2100 with 512MB ram, using ubuntu)
arborint wrote:As I recall when building these things, it simplified things to have the operator with the field name:
The only problem I have with your solution is you lose the ability to use the left hand parameter as the key for binding parameters. It's making me think though ... I wonder if a system could be devised of statements or clauses that could be built on top of each other, for example: for a statement "key > :key" you could bind the value 1 to :key, and the statement could be bound to a parent statement "SELECT * FROM table WHERE :where", with the value being added to a final array that could be bound using PDO.That would scale well with the composite nature of SQL queries. The trick would be making it with a slim syntax in PHP... oh, what I wouldn't give for named parameters...
allspiritseve wrote:The only problem I have with your solution is you lose the ability to use the left hand parameter as the key for binding parameters.
I'm not quite sure what you mean ... maybe I misunderstood your parameters ... but the OO SQL style is an alternative to prepared statements with bound parameters. So whether you do $where->select('key', 'value', '>') or $where->select('key>', 'value') you are "binding" the value to the column name in both cases. The real goal of any kind of "binding" is escaping the value. That's why I lean toward fewer parameters and better readability.
The other issue is quoting, so maybe doing $where->select("key>'?'", 'value') might make sense. Then you have a mini prepared statement. It is simplest to always quote though.
allspiritseve wrote:The only problem I have with your solution is you lose the ability to use the left hand parameter as the key for binding parameters.
I'm not quite sure what you mean ... maybe I misunderstood your parameters ... but the OO SQL style is an alternative to prepared statements with bound parameters. So whether you do $where->select('key', 'value', '>') or $where->select('key>', 'value') you are "binding" the value to the column name in both cases. The real goal of any kind of "binding" is escaping the value. That's why I lean toward fewer parameters and better readability.
The other issue is quoting, so maybe doing $where->select("key>'?'", 'value') might make sense. Then you have a mini prepared statement.
I use PDO to make the actual statements... the query class would just work on top of PDO. Here's an example:
That solves a lot of problems with very little syntax. As soon as you throw anything else into the lefthand parameter though, you can't automatically generate the key for binding. Hence my third parameter. Also, I don't have to worry about escaping, because PDO sends the values separately from the statement.