What is the meaning and purpose of query builder implementation?

When I worked on projects before, I used lamp, controller/model/db, and the database operations in the model were actually directly written sql statements. Later, some companies and projects temporarily switched from mysql to mango in the model, and then later I found that there is actually no LIMIT in sqlserver. The original hard-coded sql may hang if I change the driver. Later, I discovered that there is ActiveRecord/ORM, and there is also an ORM framework that is independent of the PHP framework. However, I found that this thing will generate a lot of objects, and table associations and complex statements are not very convenient.

I later learned about the concept of query builder, such as the original:

<code>SELECT u.id, i.real_name AS name FROM user AS u INNER JOIN user_info AS i ON u.id = i.user_id WHERE u.id = 1 LIMIT 1
</code>

will be implemented like this:

<code>model('user')->alias('u')->innerJoin('user_info AS i ON u.id = i.user_id')->where('u.id = 1')->limit(1)->find();
</code>

Is the purpose achieved in this way what they said:

1. Better layout and better readability
2. Independent of the database driver, so that hard-coded sql will not be executed when changing the driver

But there are some things that I still can’t understand, such as:

<code>field('id, user_name AS uname') / field('id, COUNT(*) AS count_num')
</code>

can also be written like this:

<code>field(array('id', 'user_name' => 'uname')) / field(array('id', 'COUNT(*)' => 'count_num'))
</code>

But the following method is not easier to read than the above method, and it is easier to make mistakes. . Could it be that it is to remove AS so that a certain database driver does not use AS so that it can implement other keywords by itself? But it’s not right. Since the above writing method is allowed to exist, and everyone else writes the above type, isn’t this writing method limited to drivers that only support AS?

Similarly, there are thinkphp's neq => <>, nin => not in, etc. Yii is still like / not like.

There is also laravel that implements join in such detail:

<code>join('contacts', 'users.id', '=', 'contacts.user_id')
</code>

Is there any difference in join between different databases? Why do we need to implement it in such detail? Aren’t the quotation marks and commas more troublesome than writing a direct join statement? And it’s not easy to read in comparison?

If the purpose of implementing the query builder is to switch the driver and still run, then shouldn’t we stop passing strings directly into field() or join()? Instead, we should pass in an array or multiple parameters and add all keywords. Remove it? Since string input is allowed, doesn't that destroy portability?

Find the solution!

Reply content:

When I worked on projects before, I used lamp, controller/model/db, and the database operations in the model were actually directly written sql statements. Later, some companies and projects temporarily switched from mysql to mango in the model, and then later I found that there is actually no LIMIT in sqlserver. The original hard-coded sql may hang if the driver is changed. Later, I discovered that there is ActiveRecord/ORM, and there is also an ORM framework that is independent of the PHP framework. However, I found that this thing will generate a lot of objects, and table associations and complex statements are not very convenient.

I later learned about the concept of query builder, such as the original:

<code>SELECT u.id, i.real_name AS name FROM user AS u INNER JOIN user_info AS i ON u.id = i.user_id WHERE u.id = 1 LIMIT 1
</code>

will be implemented like this:

<code>model('user')->alias('u')->innerJoin('user_info AS i ON u.id = i.user_id')->where('u.id = 1')->limit(1)->find();
</code>

Is the purpose achieved in this way what they said:

1. Better layout and better readability
2. Independent of the database driver, so that hard-coded sql will not be executed when changing the driver

But there are some things that I still can’t understand, such as:

<code>field('id, user_name AS uname') / field('id, COUNT(*) AS count_num')
</code>

can also be written like this:

<code>field(array('id', 'user_name' => 'uname')) / field(array('id', 'COUNT(*)' => 'count_num'))
</code>

But the following method is not easier to read than the above method, and it is easier to make mistakes. . Could it be that it is to remove AS so that a certain database driver does not use AS so that it can implement other keywords by itself? But it’s not right. Since the above writing method is allowed to exist, and everyone else writes the above type, isn’t this writing method limited to drivers that only support AS?

Similarly, there are thinkphp's neq => <>, nin => not in, etc. Yii is still like / not like.

There is also laravel that implements join in such detail:

<code>join('contacts', 'users.id', '=', 'contacts.user_id')
</code>

Is there any difference in joining different databases? Why do we need to implement it in such detail? Aren’t the quotation marks and commas more troublesome than writing a direct join statement? And it’s not easy to read in comparison?

If the purpose of implementing the query builder is to switch the driver and still run, then shouldn’t we stop passing strings directly into field() or join()? Instead, we should pass in an array or multiple parameters and add all keywords. Remove it? Since string input is allowed, doesn't that destroy portability?

Find the solution!

The meaning of framework design should not be studied from a developer’s perspective.

As a framework, because it is not sure of the needs of users and projects, its code must consider compatibility while maintaining its characteristics. This is why all DB packages still support query and exec methods on the basis of providing Query builder. .

In addition, no matter how good the design is, it cannot stop developers from using it indiscriminately. Many people don't read the documentation carefully and are satisfied when they see the parameters supporting string format, and won't do more research. For example, you used join in your example, but usually the model can define data relationships, and you don't need to join at all, right?

As you said, using arrays is troublesome, and using strings loses portability. The problem is that the code is written by you. Do you choose trouble or portability? The designers of the framework don't decide for you.