TP5.1 Implement search function in multi-table related query
Problems with the multi-table association search function:
The code is as follows:
// 实现搜索功能
$productName = Request::param('productName');
$storeName = Request::param('storeName');
if ( !empty($productName) && empty($storeName) ) {
$map[] = ['p.name', 'like', '%' . $productName . '%'];
} else if ( !empty($storeName) && empty($productName) ) {
$map[] = ['s.name', 'like', '%' . $storeName . '%'];
} else if ( !empty($productName) && !empty($storeName) ) {
$map[] = ['p.name', 'like', '%' . $productName . '%'];
$map[] = ['s.name', 'like', '%' . $storeName . '%'];
}
// 定义分页参数
$limit = isset($_GET['limit']) ? $_GET['limit'] : 10;
$page = isset($_GET['page']) ? $_GET['page'] : 1;
$stockList = StockModel::where($map)
-> alias('sk')
-> leftJoin('product p', 'sk.product_id = p.id')
-> join('store s', 'sk.store_id = s.id')
-> where('sk.status', 1)
-> order('sk.product_id', 'desc')
-> field('sk.*, p.name as product_name, s.name as store_name')
-> page($page, $limit)
-> select();1. In the above code, the search function is wanted to be implemented, but in the $map condition, because the search is through Join joins the fields of the attached table, so use the alias of the attached table. The form of the field, such as: p.name, s.name, there is a problem with this search. In the generated SQL statement, both appended tables are filtered, as follows :
The result is that the attached table is gone, so it is wrong to add aliases to the fields.
2. Change the search conditions to the form of table name.field. In this way, it is no problem to search the fields of the main table, but there is still a problem in searching the appendix table. In the SQL statement generated in this way, the appendix table is retained. , but there is still a problem with the search. The error is as follows:
Can anyone help me look at this? How to do it? ? ?
