allspiritseve wrote:Well, you're actually gaining flexibility in the query class-- how would you do a join with your class? Or select from multiple tables? (i.e. inner join). Or subqueries? If your class only deals with the users table, it loses the flexibility inherent in an SQL query-- you're restricted to getting data from one table only.
If your query is simply a wrapper for the database, and handles the DB abstraction, you have all the flexibility and no constraints. That way, you can "dynamically" pull any data you want, from any number of tables, in any order. The same way you can in raw SQL.
Now, if you still want to deal in users (rather than queries) I would still suggest a table data gateway or data mapper. If you only want the usergroup sometimes, use Lazy Load. If you want 5 results, just grab the first 5 from the array it gives you. Or overload the method with order and limit vars. Either would work... but I agree there are positives and negatives to both raw sql and gateways/mappers.
One more question-- what happens when your domain object, ie users, has a property that isn't in the database? Say, $full_name (a composite of $first_name and $last_name). It could be more complex, maybe the number of posts a user has, or the number of contacts they have in an address book. Those types of things aren't fields in the users table. How does your class handle those?
Good questions. It can handle joins fairly easily, but I had to limit it to the join types that are shared across the major database types (left, right, inner, and full). It handles subqueries as well, but I'm not completely sold on the way they are implemented, but I haven't thought of a better solution yet.
Join syntax would be as follows:
Code: Select all
$users = new users();
$users->alias('u'); //Gives the main table an alias of 'u'
$users->add_join('left', 'userGroups', 'ug.userId', '=', 'u.userId', 'ug');
$users->select();
The above code outputs the following MySQL query:
SELECT * FROM tst_users AS u LEFT JOIN tst_userGroups AS ug ON ug.userId = u.userId
(Above you can see that the table is tst_users rather than users and tst_userGroups. The class can also handle prefixes for databases, even if they are different across systems. It just needs to be specified in a DB_PREFIX constant in the DB connection script.)
The parameters for add_join are:
Code: Select all
add_join($join_type, $table, $column, $operator, $value, $table_alias=NULL)
I'm not totally satisfied with the subqueries syntax. Currently they're built the same way regular queries are, the only difference is that you insert 'subquery' in the optional mode parameter for the select method, which will cause it to return the entire query without executing it.
Code: Select all
$users = new users();
$users->where('lastName', '=', 'Smith');
$subquery = $users->select('subquery');
The $subquery variable could then be inserted as a parameter when you build your main query. However, I don't know if this is the most efficient way of doing that. It might be fine for small data sets, but it would probably bog down larger data sets. I still have to benchmark it.
You can specify which fields you want by using the select_list() function. It takes an array of the fields you want returned in your query. You can also use it to include aggregate functions, but to keep it portable, you would have to limit the function to something that is common across databases (which the class doesn't validate, but I have a list). The aggregate function is accessed by its full text, the same way a regular field would be.
Code: Select all
$users->select_list(array('AVG(age)'));
$users->select('single');
echo $users->{'AVG(age)'};
If you want to get a simple count of something, you can build your query the way you normally would with this class, and then enter 'count' in as the mode parameter for select().
If you need to run one query with the users class and then another different query, you can clear the entire query by using the clear_all() function, or you can specify which methods you'd like to clear by using the clear() function:
Code: Select all
$users->clear('select_list') //Sets the select_list() method to null, as if it never happened
$users->clear_all(); //nulls all methods
As I get my folder organization together a little more, the classes that correspond to tables will probably be prefixed by DB_ so that they can be autoloaded.
The class was written so developers can write code once and have it portable across database types. However, that doesn't mean that it necessarily has to be used that way. It's definitely not perfect and it doesn't cover all of the intricacies of each flavor of SQL, but it can be extended for database-specific optimization and functionality. It is basically just a wrapper for the DB abstraction, but the parts that look like ActiveRecord are just meant to give you easier access to the data and an easy way of setting values before record updates or creation.