Query builder

abajja · November 9, 2010, 9:57am

What about this test case?:


SELECT a, SELECT(..WHERE..) AS b WHERE ...

tonydspaniard · November 9, 2010, 10:39am

One more case (not IN array() but in another SUBQUERY):

SELECT … IN (SELECT … IN ( SELECT …) ) …

rickgrana · November 9, 2010, 12:27pm

And don’t forget UNION support.

Maybe




$query1->union($query2);

or




$query1->union(array($query2, $query3));

or




CDbQueryBuilder::union(query1, query2, ...);

Which SQL VERSION would this feature support?

Maybe start with SQL ANSI and gradually increasing its support to SQL 1997/2003/2007?

Asgaroth · November 10, 2010, 6:47pm

Im like this idea too, its a pretty straightforward way of writing understandable SQL queries.

what id you need to rebind parameters on the CDBCommand on a loop for example?

First let me see if Im not doing it wrong:




<?php

                $sql = "SELECT f1, f2 FROM table WHERE f3 = :f";

		$cmd = Yii::app()->db->createCommand($sql);

		foreach ($myFs as $f) {

			$cmd->bindParam(":f", $f, PDO::PARAM_STR);

			$cmd->queryAll();

		}

?>

If thats correct how will it be done using the new query builder, like this?





<?php

       $cmd = Yii::app()->db->createCommand()

                ->select('f1, f2')

                ->from('table');

                

		foreach ($myFs as $f) {

			$cmd->where('f3=:f', array(':f3'=>$f))

                            ->queryAll();

		}

?>

I think its nice. =)

alvs · November 11, 2010, 4:31am

i wait this builder qiang, many problem i adapted query by AR…

it simple way to quickly code

thaks again

tova · November 11, 2010, 10:53am

qiang:




$db->createCommand()

        ->select('name, password, tbl_profile.content')

        ->from('tbl_user')

        ->join('tbl_profile', 'tbl_profile.user_id=tbl_user.id')

        ->where('id=:id', array(':id'=>$id))

        ->queryRow();

Sometimes when I’m writing attibutes in quotes, I’m missing IDE’s autocompletion. I would like to use $db->createCommandFromModel() like this




$u = new User::model()->prepareForSql();

$p = new Profile::model()->prepareForSql();


$db->createCommandFromModel()

        ->select(array($u->name, $u->password, $p->content)) 

        ->from($u) // $u->tableName();

        ->join($p, $p->user_id.'='.$u->id))

        ->where($p->id.'=:id', array(':id'=>$id))

        ->queryRow();

Just an idea, nevermind if looks crazy or to complex to implement

Backslider · November 19, 2010, 4:39am

I just noticed it added in my latest SVN update, so we can all play now!

qiang · November 22, 2010, 1:12am

Yes, the full implementation is available in trunk. You may find the tutorial on query builder at http://code.google.com/p/yii/source/browse/trunk/docs/guide/database.query-builder.txt

Please help test this feature and let us know if you find any issues or have suggestions to further improve it. Thanks!

twisted1919 · November 22, 2010, 10:34pm

This is more like CodeIgniter is using and that’s an excellent Query Builder class.

Working with databases is the most important thing in web dev environment therefore the things really need to get simplified with the Yii Database layers.

One thing that keeps bothering me when using Yii is that for example, if i want to insert into a table with 20 fields i need to go on a long road, bind every param etc etc, and i just hate that, i don’t want to waste time, i want to earn time.

Implementing something like:




$insert = array(

    'field1'   => 'value1',

    [...]      => [...],

    'field20   => 'value20'

);

$db->createCommand()->insert('table',$insert);

Would be just great, and also, the values should be automatically escaped against sql injection, which is easy thing to do in the core of Yii.

Just keep an eye on CI AR class just to get an example on how a query builder class should look like.

This would be a huge win for Yii framework, as i, coming from a CI background had some difficulties understanding the YII AR Class, and i prefer to use the DAO method because it let’s me understand what’s really happening when i run a SQL query(and believe me, this is the real thing on a web app, the queries you execute and the performance of those queries)

qiang · November 22, 2010, 10:52pm

To make sure I didn’t miss anything, what is the difference between your proposed insert() and the one described in the first post (which is already implemented in svn)?

twisted1919 · November 23, 2010, 2:51am

It seems i was tired when i wrote the post and somehow didn’t saw the insert() method in the link you provided.

Yes, that’s exactly what i was talking about.

Also, i was looking closer to the other methods and i can easily see how can i use them, as i said, it’s a great win for Yii, and when .6 will be released i’ll be happy to use this feature instead of DAO .

WallTearer · November 25, 2010, 10:03am

Query builder will be a huge jump forward. I thought that it was allready here =)

qiang · November 28, 2010, 2:58am

I just added a set of new methods for building queries that manipulate DB schema (e.g. createTable, alterColumn).

We will use these methods in the upcoming DB migration command. Cheers!

Your feedback and test are greatly appreciated!

http://code.google.com/p/yii/source/detail?r=2681

trejder · November 29, 2010, 9:20am

Mike,

To simply NOT use SQL all, if you have such possibility?

Yes! Query builder is something I would appreciate with all my hands and legs up!

trejder · November 29, 2010, 9:28am

Antonio,

You would be surprised how many users there are out there that understands databases as spreadsheets, not data storage warehouses! I know and been working with many people that can operate on databases very easy and without knowing even basis of SQL statements. All thanks to Query Builders on top of which you can build graphical interfaces for manipulating data and DB’ structure without knowing SQL.

Take phpMyAdmin as an example. You can do nearly everything with it and you don’t even need to know what is the different before UPDATE and DELETE!

My private opinion is that any kind of simplicity is welcome if it does not cost performance. There are many Yii developers that will never use planned Query Builder and for sure there will be many in future that would never get an interest of this framework if it wouldn’t have Query Builder.

P.S.: What “rizar el rizo” means in English?

tonydspaniard · December 1, 2010, 11:36pm

That means, making extra efforts for something that is already done and works great.

I have just seen above some improvements on schema manipulation… that is very good.

PS: I am already convinced about the new approach… no need to keep punching me guys!

tonydspaniard · December 1, 2010, 11:40pm

A.M.A.Z.I.N.G. work guys… thank you very much

jacmoe · December 2, 2010, 5:57am

Awesome!

The only problem is that it’s hard to do any programming with my hands up in the air.

DB migrations are on my todo list - and those methods would make that so much easier to tick.

galymzhan · December 2, 2010, 8:04am

Probably I won’t use query builder, but Migrations is a killer-feature for me! Thanks for implementing it!

Describing my database structure in a database-agnostic code is what I was missing from Yii.

Qiang, I can’t find methods dealing with foreign keys. Will it be implemented? Also, all that createTable, alterColumns methods will be in CDbCommand class, right?

macinville · December 7, 2010, 5:53am

Whoa! I like this feature! I’m so happy I’m using Yii!