What does it mean the colon in queries yii2 framew

Question

I'm totally new in yii2 and I want to know what does it mean the colon in the query?

I have made a research about binding parameters, but in the yii2 documentation says:

// returns all inactive customers
$sql = 'SELECT * FROM customer WHERE status=:status';

which side is from the data base? the left side or the right side?

which is a simple text and which one is a column from the DB? Im so confused.

what would be another way to make the query without the colon? is it valid?

why it has 'anyo = **:**valor' in the next example? and some others dont?

$dbLibro = Libro::find()->where('tipo = "Nacimiento"')->andWhere('cerrado = 0')->andWhere('anyo = :valor',[':valor'=>date("Y")])->one();

I hope its clear cause the documentation is a bit confusing for me.

Answer 1

The colons are not directly related with Yii2, it's related with PHP PDO extension that used by Yii2.

Each colon is placeholder used later for binding value. Check for example this question.

If we write this query in ActiveQuery:

SELECT * FROM customer WHERE status = :status

we can get something like this:

$query = Customer::find()->where('status = :status', [':status' => Customer::STATUS_ACTIVE]);

Assuming STATUS_ACTIVE constant equals to 1, after execution it transforms to this:

SELECT * FROM "customer" WHERE status = 1

So the left side (before equals) represents column name, right part - value which will be safely binded after.

But you don't have to write params by yourself, Yii2 QueryBuilder generates it automatically for you.

There are other ways to write query without colons and they are used more often. This query can be written like this:

$query = Customer::find(['status' => Customer::STATUS_ACTIVE]);
$models = $query->all();

Or like this using shortcut:

$models = Customer::findAll(['status' => Customer::STATUS_ACTIVE]);

Or it can be even put inside of a scope:

$models = Customer::find()->active();

In this case Yii generates parameters automatically and it will be equivalent to this:

SELECT * FROM "customer" WHERE "status"=:qp1

Value 1 will be binded to :qp1 parameter, note that in this case column names are also double quoted.

If you try to use more conditions, params will be :qp2, :qp3 and so on (default PARAM_PREFIX is :qp).

As for your second query, it can be rewritten like this:

$model = Libro::find()
    ->where([
        'tipo' => 'Nacimiento',
        'cerrado' => 0,
        'anyo' => date('Y'),
    ])->one();

Such queries look way better and readable in this state.

Yii2 allows generate even more complex conditions in queries, check this section of the docs for more details.

P.S. It's better to use english for naming in your code. Think about other international developers supporting your code. date('Y') can be calculated using database functions depending on used RDBMS.