Laravel5.3-local or condition and global or condition under database operations (local and global orWhere), laravel5.3-orwhere

Laravel5.3-local or condition and global or condition under database operations (local and global orWhere), laravel5.3-orwhere

When the user name is not blank

SELECT * FROM 'account _ RECHARGE 'left JOIN 'order' ON 'account _ RECHARGE '. 'orderno' = 'order '. 'orderno' left join 'user _ account' ON 'order '. 'userid' = 'user _ account '. 'userid'WHERE('User _ account '. 'username' LIKE '% 18%' OR ('user _ account '. 'Your eno' LIKE '% 100') OR ('user _ account '. 'email 'LIKE' % 18% '))AND 'account _ RECHARGE '. 'ordertime' BETWEEN '2017-08-11 23:59:59' AND '2017-08-11 'ORDER'Account _ RECHARGE '. 'ordertime' DESC;

The preceding SQL statement is written in laravel5.3:

$ Model = DB: connection ('order _ user')-> table ('account _ RECHARGE ')

-> LeftJoin ('order', 'account _ RECHARGE. orderno', '=', 'order. orderno ')

-> LeftJoin ('user _ account', 'order. userid', '=', 'user _ ACCOUNT. userid ')
　　　　　　　　　　　　　　　　　-> Select ('account _ RECHARGE. * ', 'user _ ACCOUNT. username', 'user _ ACCOUNT. login eno', 'user _ ACCOUNT. EMail ')

-> OrderBy ('account _ RECHARGE. ordertime', 'desc ');
If (! Empty ($ username )){
$ Username = trim ($ username );
$ Model = $ model-> where (function ($ query) use ($ username ){
$ Query-> where ('user _ ACCOUNT. username', 'like', "% $ UserName % ")
-> OrWhere (function ($ query) use ($ username ){
$ Query-> where ('user _ ACCOUNT. inclueno', 'like', "% $ username % ");
});
-> OrWhere (function ($ query) use ($ username ){
$ Query-> where ('user _ ACCOUNT. EMail ', 'like', "% $ username % ");
});
});
}

Note: In the judgment that username is not empty, if use is not used, the undefined variable username will be reported.

Comparison

First

$ Model = $ model-> where (function ($ query) use ($ username ){
$ Query-> where ('user _ ACCOUNT. username', 'like', "% $ UserName % ")
-> OrWhere (function ($ query) use ($ username ){
$ Query-> where ('user _ ACCOUNT. inclueno', 'like', "% $ username % ");
});
-> OrWhere (function ($ query) use ($ username ){
$ Query-> where ('user _ ACCOUNT. EMail ', 'like', "% $ username % ");
});
});
}

And

Second

$ Model = $ model> where ('user _ ACCOUNT. userName ', 'like', "% $ username %")-> orWhere ('regioneno', 'like', "% $ username %")-> orWhere
('Email ', 'like', "% $ username % ");
}

Difference

The first is the local or condition, which only determines the or of username, and the relationship with other conditions is the same as that of the first SQL statement that appears.

The second is the global or condition. If you replace the second statement with the first statement, it is equivalent to the SQL statement.

SELECT * FROM 'account _ RECHARGE 'left JOIN 'order' ON 'account _ RECHARGE '. 'orderno' = 'order '. 'orderno' left join 'user _ account' ON 'order '. 'userid' = 'user _ account '. 'userid'WHERE'User _ account '. 'username' LIKE '% 18%' OR ('user _ account '. 'Your eno' LIKE '% 100') OR ('user _ account '. 'email 'LIKE' % 100 ')AND 'account _ RECHARGE '. 'ordertime' BETWEEN '2017-08-11 23:59:59' AND '2017-08-11 'ORDER'Account _ RECHARGE '. 'ordertime' DESC;

At this time, you will find that as long as the username exists, your other conditions will become invalid.

This is the pitfall I skipped ......