Failed to use where after tp5 union
->alias("b")
->field(["(b.order_amount + b.store_amount) as total","b.create_time","b.order_id","b.pay_types"])
->where("b.order_status = 2")
->union(["select pay_amount as total,create_time,recharge_id as order_id,pay_type as pay_types from tp_recharge"])
->buildSql();
after executing as above, the sql statement is
SELECT (b.order_amount + b.store_amount) as total,`b`.`create_time`,`b`.`order_id`,`b`.`pay_types` FROM `tp_order` `b` UNION ( select pay_amount as total,create_time,recharge_id as order_id,pay_type as pay_types from tp_recharge ) WHERE ( b.order_status = 2 )
The
where condition is finally executed. What I want is to query the where condition after the first table. How can you solve this problem
?
operate with an intermediate table,
1. Use buildSql () to generate sql for each table to be queried,
2. Union all the tables to be queried in one middle, and then use buildSql () to generate sql.
$sqlArr[] = DB::table('tp_order')
->where($where)
->field(['(order_amount + store_amount) as total','create_time','order_id','pay_types','order_status'])
->buildSql();
Mr. sql, multiple tables, and so on. You can use where or other operations. It is important to note that the fields of the query should be consistent, including the intermediate table
.
the intermediate table adds up all the sql union
$temp=Db::name('users')
->field("unionid as total,create_time,count(users_id) as order_id,users_name as pay_types,gift_balance as order_status")
->union($sqlArr)
->buildSql();
finally, use the subquery to construct a new query
$data = DB::table($temp.'a')
->where(['order_id'=>['neq',0],'create_time'=>['neq',0]])
->order("create_time desc")
->paginate($pagesize)
you can use paging for this last step
this problem, because there is no such requirement has been operated, it is recommended to take a look at the where method source code of tP5. However, in this case, I usually choose to operate directly with Yuansheng sql.
if it is a full tp_recharge table, you can reverse the table
Db::table('tp_recharge')
->field('pay_amount as total,create_time,recharge_id as order_id,pay_type as pay_types')
->union(function ($query) {
$query->alias('b')->field('(b.order_amount + b.store_amount) as total,`b`.`create_time`,`b`.`order_id`,`b`.`pay_types`')->table('tp_order')->where("b.order_status = 2");
})
->buildSql();
