mysql db framework patterns - too complex

[Destramic]

hey guys i see alot of sql patterns designed where you have a method for every sql command eg.

 

$sql = new SQL
$sql->select('news, date')->from('news')->execute();

 

but that all seems too complex...what im asking why isnt sql classes made not as simple as

 

$sql = new SQL
$sql->query('SELECT news, date FROM news')->execute();

 

would the way above be more easier?

[gizmola]

When you see the above, it is typically because there is an ORM involved.  It takes changing your thinking about things, because the point of an ORM is that it is working with Objects, rather than sql tables.  The sql is suppossed to be obfuscated, and you're supposed to configure the ORM so that the underlying details of how data is persisted into the database is not important - you simply deal with objects and how they relate to each other, even if an object is a composite of a number of different tables.  So while there may be a direct correlation in the first example between new and a news table and date and a date table, at the point you're working with the ORM you are really working with the classes news & date.

[Destramic]

i see...but how would you do such a complex query such as :

 

$team_select  = "SELECT *,


 @rank := IF(@points=points, IF(@points = '0', @rank + 1, @rank), @rank+1), 
     @points := points,
     @drawing := IF(@points=points, IF(@points = '0', @drawing = '0', @drawing = '1'), @drawing = '0'),
     IF(@drawing = '1', @rank + 1, @rank) as rank,
     @drawing := 0
        
    	                           FROM
                (SELECT    t.team_id,
                           t.team_name,
                           COUNT(r.league_match_result_id) AS 'matches_played',
                           SUM(IF(r.result='Win',1,0)) AS `wins`,
                           SUM(IF(r.result='Loss',1,0)) AS `losses`,
                           SUM(IF(r.result='Draw',1,0)) AS `draws`,
                           SUM(IF(r.result='Win',3,IF(r.result='Draw',1, IF(r.result='Loss',0,0)))) AS `points`
                    FROM teams t
                    LEFT JOIN league_match_results r ON r.team_id = t.team_id
                    LEFT JOIN team_leagues tl ON tl.team_id = t.team_id
                    WHERE tl.league_id = :1
                    GROUP BY t.team_id
                    ORDER BY points DESC, t.team_name) AS x";

 

using that method?

[gizmola]

The answer really depends on the ORM.  One advantage of using an orm is that it's code, and some of the procedural code can be codified and added to the model for a particular class.  When you configure the relationships it will join the objects together, in terms of transforming the data into a series of connected objects, but you don't really think about how a sql query would work, since the sql is being done by the orm.

[Destramic]

but i dont think a orm could produce a complex query as above

[gizmola]

If you're talking about as an example, Doctrine, it has a full query language called DQL that you can use to build complicated queries, and it is always possible to drop down to basically raw sql.  If we ignored the top section and focused on the correlated subquery that could probably be done in DQL without too much trouble.

 

There is no question of can an ORM do something --- it is just code and you can use it as it fits your needs or circumvent it. The bigger question is, why would you use an ORM, and for most people, it's because it fits in well with their choice of framework.  For example, the symfony framework has integrated propel and doctrine.  The purpose of that integration is not focused on 5% of queries that are complex and have to be hand tuned, but rather, the 95% that aren't and it can save you a lot of time, and provide consistency and quality to the code.  For example, consider something like this:

 

$commment = new Comment();
$comment->title = $form->title;
$comment->comment = $form->comment;
$blogpost->comments[] = $comment;
try {
  $blogpost->save();
} catch (Doctrine_Exception $e) {
  // Log error
  // Redirect

 

This is the type of code you would often see where a lot of things have been encapsulated in the framework and the ORM, and you're left with very simple code that lets you focus on the business logic and creating functionality.

[Destramic]

thank you that is a intresting site...i dont tend to use other script but just ideas...i like everything i use to be created by me so i know i fully understand the system/script...im trying to create my own framework of a project i have...

 

this is what the page controllers will look like...let me know what you think and if you have any ideas please...thank you again

 

<?php

class Leagues_Controller extends Action
{
public function leagues()
{

}

public function league($game)
{
	$config = array('host'     => 'localhost',
	                'username' => 'root',
	                'password' => '',
	                'database' => '');

	$db = DB::factory('MySQL', $config);
	$db->connect();

	$variables = array('rank'    => '0',
	                   'points'  => '0',
	                   'drawing' => '0');

	$query = "SELECT *,
                     @rank := IF(@points=points, IF(@points = '0', @rank + 1, @rank), @rank+1), 
                         @points := points,
                         @drawing := IF(@points=points, IF(@points = '0', @drawing = '0', @drawing = '1'), @drawing = '0'),
                         IF(@drawing = '1', @rank + 1, @rank) as rank,
                         @drawing := 0
                  FROM(
                  SELECT t.team_id,
                         t.team_name,
                         COUNT(r.league_match_result_id) AS 'matches_played',
                         SUM(IF(r.result='Win',1,0)) AS `wins`,
                         SUM(IF(r.result='Loss',1,0)) AS `losses`,
                         SUM(IF(r.result='Draw',1,0)) AS `draws`,
                         SUM(IF(r.result='Win',3,IF(r.result='Draw',1, IF(r.result='Loss',0,0)))) AS `points`
                  FROM teams t
                  LEFT JOIN league_match_results r ON r.team_id = t.team_id
                  LEFT JOIN team_leagues tl ON tl.team_id = t.team_id
                  WHERE tl.league_id = :1
                  GROUP BY t.team_id
                  ORDER BY points DESC, t.team_name) AS x";

	$league = $db->set($variables)->fetch_all($query, '1');
	$rows   = $league->get_rows();

	$view       = new View;
	$view->rows = $rows;
	$view->title('')->add_css('')->add_js('')->add_template('');
}
}

[trq]

If you've already separated your controllers and view you really should look into separating your database logic into models.

 

Having the database logic in the controller like that means you cannot reuse it outside of your controller. Nor can you share database logic between controllers.

[ebmigue]

hey guys i see alot of sql patterns designed where you have a method for every sql command eg.

 

$sql = new SQL
$sql->select('news, date')->from('news')->execute();

 

but that all seems too complex...what im asking why isnt sql classes made not as simple as

 

$sql = new SQL
$sql->query('SELECT news, date FROM news')->execute();

 

would the way above be more easier?

 

I agree. They are complex.

 

I'd suggest that you use re (http://www.reetudes.com).

 

The interface (the way you will use it) does not require you to instatiate objects. You can also directly issue queries, and retrieve its results.

 

$array = re::_eval("SELECT * FROM some_table WHERE a = ?param2? AND b = ?param1?", array(
    'args'=>array('param1'=>1, 'param2'=>'b')
))->toArray();

foreach($array as $tuple){
  echo $tuple->get('some_value');
}

 

That's it.

 

Join Tables?

 

$array = re::usevar("table1")->join(re::usevar("table2"))->toArray();
//loop as above.

 

Of course, setting up connection parameters are necessary. But that would be easy. Once its done, you can immediately

begin.

 

re is based on the Relational Algebra. Perfect for working with databases that are relational (e.g, MySQL).

 

You notice that the syntax is "OOP style" but the "approach" is the good-old procedural method.

 

 

Hope it helps.

[Destramic]

interesting guys thank you for your help...regarding modules?...should i just have a one module class that executes the connection of the database for controller?

[gizmola]

modules or models?

[Destramic]

model i meant sorry...although i believe modules are good to use?

[gizmola]

Typically you have a bootstrap that sets up resources like database connections. The models will all depend on the resources but the model code would be specific to an entity.  So for example, you have a user model, which would provide an api for dealing with users, including creating, modifying and finding a user.  You have a model for every one of your logical application entities, but not a model for database connections per se.

[gizmola]

I'd suggest that you use re (http://www.reetudes.com).

 

I have no problem with you suggesting the use of a library you've obviously put a lot of time and thought into, but I think you should make it really clear that it's your library.  For example:  "You might consider my library:  for these reasons....".  vs. suggesting it in a thread like this.  Yes it could be inferred from the link in your signature, but I think you need to come right out and state it, whenever you are suggesting people use it, or risk the appearance that you're not being entirely forthcoming about the potential for bias.

 

 

[ebmigue]

I'd suggest that you use re (http://www.reetudes.com).

 

I have no problem with you suggesting the use of a library you've obviously put a lot of time and thought into, but I think you should make it really clear that it's your library.  For example:  "You might consider my library:  for these reasons....".  vs. suggesting it in a thread like this.  Yes it could be inferred from the link in your signature, but I think you need to come right out and state it, whenever you are suggesting people use it, or risk the appearance that you're not being entirely forthcoming about the potential for bias.

 

Noted.

 

I thought that stating it explicitly that is is "my library" would be a bit immodest.

 

I would like to give the impression that it is a library that is for everyone, and could be modified by anyone (it's released as a FOSS).

 

Hence the avoidance of the term "my library."

 

I will try to improve my phrasing on the next opportunity.

[gizmola]

No worries, and I can understand your reluctance to come off as a seeming full of yourself, but the fact of the matter is, that it's something you engineered and are making freely available as a service for people, so you're certainly entitled to take credit for it, and are welcome to promote it on the forum whenever the opportunity avails itself.

[ebmigue]

Thank you for allowing me to do so.

 

This is may be off-topic, but...

 

I'm a bit more careful of my posts now. If you read my other posts in other threads, I really sounded like a jerk (and yes I did, specially when I read them again).

 

I was just provoked, I'd like to think, by some. And I didn't handle it well. But it's nothing personal. I guarantee.

 

----

Now, back to the topic.

 

The nice thing about re, is that declaring classes to handle table manipulation (i.e., SELECT, INSERT, UPDATE), may not be anymore necessary.

Simple solutions to such generic tasks could be easily be implemented by the user, according to his choice.

 

Of course, classes can also be created, but it could be avoided if one wants. Not that classes are useless or bad, but time is saved in utilising them. If there is opportunity to save time, why not grab it?

 

Secondly.

 

re is bringing to PHP the power and expressiveness of relational technology.

 

So database tables are treated as your typical PHP variables. Relations (and tuples) are treated as your typical PHP values (say, array, integer, boolean values). Operators are treated as your usual PHP functions.

 

Result: simplicity, ease-of-use.

 

Also, IMO, PHP's PDO could be still be improved. Or a similar-to-re class/package could be introduced natively in PHP.

I hope someone would write a native/pre-packaged/built-in package(set of classes) or set of functions in PHP that is relational.

 

We could save a lot more time.

 

 

[jawef]

$sql = new SQL
$sql->select('news, date')->from('news')->execute();

 

Is a really but practice of OOP. If a framework has this way of doing thinks then move on. OOP should produce simple code and understandable in 10 – 15 lines of code block. If you done it the right way (with instances) one line of that is 5 … that is not good practice.

[trq]

$sql = new SQL
$sql->select('news, date')->from('news')->execute();

 

Is a really but practice of OOP. If a framework has this way of doing thinks then move on. OOP should produce simple code and understandable in 10 – 15 lines of code block. If you done it the right way (with instances) one line of that is 5 … that is not good practice.

 

Pff, this is simple method chaining and there is nothing wrong with it. There is absolutely zero difference between:

 

$sql = new SQL
$sql->select('news, date')
$sql->from('news')
$sql->execute();

 

and the previous code.

 

It's just easier to read IMO.

[ebmigue]

I'd just like to note that:

 

I understand that what were posted were mere examples. However, the use of some hypothetical class named 'SQL' may not be very "framework-minded."

 

For there is the possibility that a database is not SQL-based.

 

And there is some movement in just that direction --- there are attempts to replace SQL, making way for a more "relationally-oriented" database programming language.

 

And if that happens, would that mean that we have to edit all code referencing/using the 'SQL' class?

 

I hope not.