Page 1 of 1

Query class

Posted: Sat Sep 06, 2008 11:54 pm
by allspiritseve
I sort of got inspired by this thread... but I already felt I was hijacking it a bit, so I've started a new thread.

I'm sure we all agree writing plain SQL can be a pain and nothing can compare to it in flexibility. I've been playing around with some stuff, and I guess I have two alternatives I'd like to run by you guys:

Code: Select all

$query = new Query();
$query->select ()
    ->from ('table1')
    ->innerJoin ('table2')
        ->on ('table1.id = table2.table_id')->or ('table2.color', $color)
    ->where ('num > :num', $num)
    ->limit ('20')
    ->orderBy ('id', 'asc')
    ->execute();
 
$query = new Query();
$query->insert->into ('table1')->set($fields)->execute();

or

Code: Select all

$query = new Query ('SELECT * FROM table1')
    ->append ('LEFT JOIN table2 ON :conditions');
 
$conditions = new Query ('table1.id = table2.table OR table2.color = :color');
$conditions->bind (':color', $color);
 
$query->bind (':conditions', $conditions)
    -> append ('WHERE num > :num LIMIT :limit ORDER BY :order_by ASC')
    -> bind (':num', $num)
    -> bind (':limit', 20)
    -> bind (':order_by', 'id')
    -> execute();
    
$query = new query ('INSERT INTO table1 SET :fields')
    -> bind (':fields, $fields)
    -> execute();
The first intending to mimic and read like an SQL query... the second intending to allow appending, prepending, and replacing of SQL fragments, and binding either single values or arrays of values to those fragments. Obviously the syntax is very rough right now, but I'm just playing around with what feels more natural and would make writing certain queries a bit easier. Thoughts?

Re: Query class

Posted: Sun Sep 07, 2008 11:23 am
by andyhoneycutt
From an ease-of-use standpoint I personally would prefer to use something like example 1. It reads more naturally and that's a big bonus to me since I already know sql: it'd be easier to troubleshoot.

Re: Query class

Posted: Sun Sep 07, 2008 11:35 am
by allspiritseve
I agree, for smaller queries... I think the benefits of the second would come from very large queries, where there are many subqueries or joins that you have to keep track of... you could break those out into separate statements to work on, and then bind the whole thing together. I could maybe even do a combination of the two... at the moment, the Query class is just a factory and returns a specialized query class (SelectQuery, InsertQuery, UpdateQuery, DeleteQuery) for testing purposes.

Re: Query class

Posted: Sun Sep 07, 2008 12:47 pm
by koen.h
How will you know you are writing/using and expression (function/operator) without breaking syntax for other databases?

I think your PDO use of binding is better than what I came up with last (the question marks). It seems easy enough to mimick outside of PDO.

Re: Query class

Posted: Sun Sep 07, 2008 12:53 pm
by koen.h
I think the second one is confusing.

How would you prepare the first one and execute it multiple times with different parameters?

Re: Query class

Posted: Sun Sep 07, 2008 12:58 pm
by allspiritseve
koen.h wrote:How will you know you are writing/using and expression (function/operator) without breaking syntax for other databases?
Do you mean "an expression"? Basically, I'm not trying to abstract away SQL to something database-generic. That's not the problem I'm trying to solve. (I think PDO is moving to cover that kind of thing soon anyways). I want queries to be constructed in parts, so if, for example, joining a table is dependant on a variable, I want to be able to modify a statement in an if-clause:

Code: Select all

$query->select()->from('table');
if ($join): $query->join ('table2')->on('conditions'); endif;
$query->execute();
koen.h wrote:I think your PDO use of binding is better than what I came up with last (the question marks). It seems easy enough to mimick outside of PDO.
I love PDO's bindings. In fact, I love them so much I don't want to use them for just values-- I want to bind fragments of expressions into other expressions. I think that makes a very flexible (though not very readable, as andyhoneycutt pointed out) query-building class.

Re: Query class

Posted: Sun Sep 07, 2008 1:03 pm
by allspiritseve
koen.h wrote:I think the second one is confusing.
It was just an idea I had... not set in stone, by any means.
koen.h wrote:How would you prepare the first one and execute it multiple times with different parameters?

Considering its essentially a wrapper for PDO, I'd use the same syntax:

Code: Select all

$query->select()->from('table')->where('var = :var');
$query->bindParam (':var', $var);
 
$var = 'blue';
$query->execute();
 
$var = 'yellow';
$query->execute();
Or something like that... I have never used that feature of PDO yet, so I hadn't thought about it.

Re: Query class

Posted: Sun Sep 07, 2008 2:02 pm
by koen.h
We're in illegal territory it seems.

http://www.freepatentsonline.com/7188105.html

Re: Query class

Posted: Sun Sep 07, 2008 2:05 pm
by allspiritseve
Umm... no. That's rediculous.

Re: Query class

Posted: Sun Sep 07, 2008 2:15 pm
by koen.h
As I understand this patent, using parameters like :param to generate multiple bounded queries is covered by it.

Re: Query class

Posted: Sun Sep 07, 2008 3:06 pm
by allspiritseve
They can sue me for all I care... I'd like to see them argue their case before a judge:

http://www.uspto.gov/web/offices/pac/do ... tml#patent

"Interpretations of the statute by the courts have defined the limits of the field of subject matter that can be patented, thus it has been held that the laws of nature, physical phenomena, and abstract ideas are not patentable subject matter."

Not to mention... I guarantee you that that specific method has been documented before the patent was filed, therefore it can't be a new process. Nice try.

Re: Query class

Posted: Sun Sep 07, 2008 3:38 pm
by koen.h
I just posted this to show how stupid things can get with patents. We wouldn't have PDO if this was serious.

Back to the query class. Ez components db component is also a wrapper around PDO. You might want to look at their guide for similar ideas (apart from the expression abstraction):

http://ezcomponents.org/docs/tutorials/Database

Re: Query class

Posted: Sun Sep 07, 2008 4:42 pm
by marcth

Re: Query class

Posted: Sun Sep 07, 2008 4:55 pm
by allspiritseve
marcth wrote:Here are a couple projects that might inspire you.
With the exception of Creole, those are ORM's, yes? I prefer the data mapper. My intention is to have the datamapper use this query class to construct and execute queries.