Product Table Structure and data -
id, product_name, hsn, product_category, product_subcategory, company, pic, part_no, min_stock, stock, rate, notes
The product_subcategory column contains the values like for eg.
13,14,31,138
18
126,140,176,177
78,79
The above numbers are ids of the following table
product_subcategory
-
id, subcategory_name
While creating an Invoice I need to search the product by the combination of product_category, product_subcategory, company
.
For this, I have the ajax as follows
<?php
$script2 = <<< JS
$(document).ready(function () {
$(document.body).on('change', '#purchaseitems-0-category_id, #purchaseitems-0-model_id, #purchaseitems-0-company_id', function () {
var tt = $("#purchaseitems-0-category_id").val();
var tt2 = $("#purchaseitems-0-model_id").val();
var tt3 = $("#purchaseitems-0-company_id").val();
var stuff1 ={'key1': tt ,'key2': tt2, 'key3': tt3};
p1();
});
});
function p1() {
var stuff ={'key1':$("#purchaseitems-0-category_id").val(),'key2':$("#purchaseitems-0-model_id").val(), 'key3': $("#purchaseitems-0-company_id").val()};
$.ajax({
type: "POST",
url: "http://localhost/yii-application/backend/web/index.php?r=purchase/p1",
data: {result:JSON.stringify(stuff)},
success: function (test4) {
var json_obj5 = $.parseJSON(test4);
$('#purchaseitems-0-name_of_product').val(json_obj5.id);
$('#purchaseitems-0-hsn').val(json_obj5.hsn);
$('#purchase-taxrate').val(json_obj5.rate);
$('#purchaseitems-0-part').val(json_obj5.part_no);
},
error: function (exception) {
alert(exception);
}
});
}
JS;
$this->registerJs($script2);
?>
Now the real code which needs to be changed -
public function actionP1()
{
$data2 = Yii::$app->request->post('result');
$data = $_POST["result"];
$data = json_decode("$data", true);
if (isset($data)) {
$test = $data;
$modelfedbkshiprate = \backend\models\Product::find()->where(['product_category' => $data["key1"]])->andWhere(['like' , 'product_subcategory' , $data["key2"]])->andWhere(['company' => $data["key3"]])->one();
} else {
$test = "Ajax failed";
}
return \yii\helpers\Json::encode($modelfedbkshiprate);
}
The issue with the above query is the like
operator in product_subcategory
.
The like
operator searches the id 7
instead of 78
and therefore I end up getting a wrong product. I thought of using FIND_IN_SET
but don't know how.
Please suggest an alternate solution to like
operator.