So, I have a very simple query:
$values = $option_value->find()->innerJoin('value_option vo ON vo.option_id = ' . (int)$option_id . ' and vo.value_id = option_value.id')->orderBy('value_name')->all();
This generates the following invalid SQL:
SELECT `option_value`.* FROM `option_value` INNER JOIN `value_option vo ON vo`.`option_id = 1 and vo`.`value_id =` `option_value`.`id` ORDER BY `value_name`
Wha?
You have set the method parameters in a wrong way. You have to use $table, $on and $params. Probably like this:
$values = $option_value->find()->innerJoin(
'value_option vo', // $table
['vo.option_id' => ':option_id', 'vo.value_id' => 'option_value.id'], // $on
[':option_id' => (int)$option_id] // $params
)->orderBy('value_name')->all();
innerJoin()
The purpose of a framework is to make coding simpler, not more complex. As far as I’m concerned, it’s a bug.
Seriously? You consider this a bug because you can’t be bothered to learn the api? Wtf.
Maybe you should just write the entire query yourself instead of using AR/Query. That’s the simplest way to do it, right?
Can’t be bothered to learn? I think I know far more than you ever will sonny. Ever hear of KISS? Why on earth would you consider something more complex as better?
Sure, placeholders are a good thing, however when we are working with numeric ID’s then a simple (int)$var should suffice and should work.
Let me tell you: In Yii 1 AR had no issues with such a query. Yii 2 should not either. You can clearly see that the backticks are all over the place.
It is a bug.
It looks to me that the API of yii\db\Query is so systematic and consistent that we would have very little time to be accustomed to it. I mean, I think it’s simple enough.
Also in Yii 1.1, the parameters for “join” method has already been divided into three parts (table name, conditions and parameters). You said that your code worked with 1.1. I don’t know why. I just imagine that 1.1 might have been more permissive to a plain-sql style of coding.
I’m also a believer in KISS, and a lazy programmer, too. So I always use the place holders whenever applicable, because it’s simpler as a whole and also energy-saving for my poor brain. I don’t want to evaluate the possibility of sql injection for every parameter by myself.
if (I think it is safe) {
do not use a place holder;
} else {
use a place holder;
}
use a place holder;
The former is more tedious and error-prone than the latter, don’t you think so?
The appropriate level of simpleness differs depending on the complexity of the target. That’s why we use some framework that is in fact not very simple and easy to learn.
No, I do not. I know perfectly well to use placeholders for non numeric attributes. I have been safely using (int)$var for numeric attributes since the year dot.
Looking at your supposedly "simpler" example:
['vo.option_id' => ':option_id', 'vo.value_id' => 'option_value.id'], // $on
[':option_id' => (int)$option_id
]
What if my join "ON" condition is != (int)$option_id ?
Exam?
'vo.option_id <> :option_id AND vo.value_id = option_value.id', // $on in string format
[':option_id' => (int)$option_id] // $params
The only thing missing from your method call is the table name to join:
just add a table name:
$values = $option_value->find()->innerJoin('value_option', 'vo.option_id = ' . (int)$option_id . ' and vo.value_id = option_value.id')->orderBy('value_name')->all();