Query class

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.

Moderator: General Moderators

Post Reply
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Query class

Post 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?
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: Query class

Post 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.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Query class

Post 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.
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: Query class

Post 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.
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: Query class

Post 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?
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Query class

Post 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.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Query class

Post 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.
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: Query class

Post by koen.h »

We're in illegal territory it seems.

http://www.freepatentsonline.com/7188105.html
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Query class

Post by allspiritseve »

Umm... no. That's rediculous.
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: Query class

Post by koen.h »

As I understand this patent, using parameters like :param to generate multiple bounded queries is covered by it.
User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Query class

Post 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.
koen.h
Forum Contributor
Posts: 268
Joined: Sat May 03, 2008 8:43 am

Re: Query class

Post 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
marcth
Forum Contributor
Posts: 142
Joined: Mon Aug 25, 2008 8:16 am

Re: Query class

Post by marcth »

User avatar
allspiritseve
DevNet Resident
Posts: 1174
Joined: Thu Mar 06, 2008 8:23 am
Location: Ann Arbor, MI (USA)

Re: Query class

Post 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.
Post Reply