0
0

Query.php 105 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405140614071408140914101411141214131414141514161417141814191420142114221423142414251426142714281429143014311432143314341435143614371438143914401441144214431444144514461447144814491450145114521453145414551456145714581459146014611462146314641465146614671468146914701471147214731474147514761477147814791480148114821483148414851486148714881489149014911492149314941495149614971498149915001501150215031504150515061507150815091510151115121513151415151516151715181519152015211522152315241525152615271528152915301531153215331534153515361537153815391540154115421543154415451546154715481549155015511552155315541555155615571558155915601561156215631564156515661567156815691570157115721573157415751576157715781579158015811582158315841585158615871588158915901591159215931594159515961597159815991600160116021603160416051606160716081609161016111612161316141615161616171618161916201621162216231624162516261627162816291630163116321633163416351636163716381639164016411642164316441645164616471648164916501651165216531654165516561657165816591660166116621663166416651666166716681669167016711672167316741675167616771678167916801681168216831684168516861687168816891690169116921693169416951696169716981699170017011702170317041705170617071708170917101711171217131714171517161717171817191720172117221723172417251726172717281729173017311732173317341735173617371738173917401741174217431744174517461747174817491750175117521753175417551756175717581759176017611762176317641765176617671768176917701771177217731774177517761777177817791780178117821783178417851786178717881789179017911792179317941795179617971798179918001801180218031804180518061807180818091810181118121813181418151816181718181819182018211822182318241825182618271828182918301831183218331834183518361837183818391840184118421843184418451846184718481849185018511852185318541855185618571858185918601861186218631864186518661867186818691870187118721873187418751876187718781879188018811882188318841885188618871888188918901891189218931894189518961897189818991900190119021903190419051906190719081909191019111912191319141915191619171918191919201921192219231924192519261927192819291930193119321933193419351936193719381939194019411942194319441945194619471948194919501951195219531954195519561957195819591960196119621963196419651966196719681969197019711972197319741975197619771978197919801981198219831984198519861987198819891990199119921993199419951996199719981999200020012002200320042005200620072008200920102011201220132014201520162017201820192020202120222023202420252026202720282029203020312032203320342035203620372038203920402041204220432044204520462047204820492050205120522053205420552056205720582059206020612062206320642065206620672068206920702071207220732074207520762077207820792080208120822083208420852086208720882089209020912092209320942095209620972098209921002101210221032104210521062107210821092110211121122113211421152116211721182119212021212122212321242125212621272128212921302131213221332134213521362137213821392140214121422143214421452146214721482149215021512152215321542155215621572158215921602161216221632164216521662167216821692170217121722173217421752176217721782179218021812182218321842185218621872188218921902191219221932194219521962197219821992200220122022203220422052206220722082209221022112212221322142215221622172218221922202221222222232224222522262227222822292230223122322233223422352236223722382239224022412242224322442245224622472248224922502251225222532254225522562257225822592260226122622263226422652266226722682269227022712272227322742275227622772278227922802281228222832284228522862287228822892290229122922293229422952296229722982299230023012302230323042305230623072308230923102311231223132314231523162317231823192320232123222323232423252326232723282329233023312332233323342335233623372338233923402341234223432344234523462347234823492350235123522353235423552356235723582359236023612362236323642365236623672368236923702371237223732374237523762377237823792380238123822383238423852386238723882389239023912392239323942395239623972398239924002401240224032404240524062407240824092410241124122413241424152416241724182419242024212422242324242425242624272428242924302431243224332434243524362437243824392440244124422443244424452446244724482449245024512452245324542455245624572458245924602461246224632464246524662467246824692470247124722473247424752476247724782479248024812482248324842485248624872488248924902491249224932494249524962497249824992500250125022503250425052506250725082509251025112512251325142515251625172518251925202521252225232524252525262527252825292530253125322533253425352536253725382539254025412542254325442545254625472548254925502551255225532554255525562557255825592560256125622563256425652566256725682569257025712572257325742575257625772578257925802581258225832584258525862587258825892590259125922593259425952596259725982599260026012602260326042605260626072608260926102611261226132614261526162617261826192620262126222623262426252626262726282629263026312632263326342635263626372638263926402641264226432644264526462647264826492650265126522653265426552656265726582659266026612662266326642665266626672668266926702671267226732674267526762677267826792680268126822683268426852686268726882689269026912692269326942695269626972698269927002701270227032704270527062707270827092710271127122713271427152716271727182719272027212722272327242725272627272728272927302731273227332734273527362737273827392740274127422743274427452746274727482749275027512752275327542755275627572758275927602761276227632764276527662767276827692770277127722773277427752776277727782779278027812782278327842785278627872788278927902791279227932794279527962797279827992800280128022803280428052806280728082809281028112812281328142815281628172818281928202821282228232824282528262827282828292830283128322833283428352836283728382839284028412842284328442845284628472848284928502851285228532854285528562857285828592860286128622863286428652866286728682869287028712872287328742875287628772878287928802881288228832884288528862887288828892890289128922893289428952896289728982899290029012902290329042905290629072908290929102911291229132914291529162917291829192920292129222923292429252926292729282929293029312932293329342935293629372938293929402941294229432944294529462947294829492950295129522953295429552956295729582959296029612962296329642965296629672968296929702971297229732974297529762977297829792980298129822983298429852986298729882989299029912992299329942995299629972998299930003001300230033004300530063007300830093010301130123013301430153016301730183019302030213022302330243025302630273028302930303031303230333034303530363037303830393040304130423043304430453046304730483049305030513052305330543055305630573058305930603061306230633064306530663067306830693070307130723073307430753076307730783079308030813082308330843085308630873088308930903091309230933094309530963097309830993100310131023103310431053106310731083109311031113112311331143115311631173118311931203121312231233124312531263127312831293130313131323133313431353136313731383139314031413142314331443145314631473148314931503151315231533154315531563157315831593160316131623163316431653166316731683169317031713172317331743175317631773178317931803181318231833184318531863187318831893190319131923193319431953196319731983199320032013202320332043205320632073208320932103211321232133214321532163217321832193220322132223223322432253226322732283229323032313232323332343235323632373238323932403241324232433244324532463247324832493250325132523253325432553256325732583259326032613262326332643265326632673268326932703271327232733274327532763277327832793280328132823283328432853286328732883289329032913292329332943295329632973298329933003301330233033304330533063307330833093310331133123313331433153316331733183319332033213322332333243325332633273328332933303331333233333334333533363337333833393340334133423343334433453346334733483349335033513352335333543355335633573358335933603361336233633364336533663367336833693370337133723373337433753376337733783379338033813382338333843385338633873388338933903391339233933394339533963397339833993400340134023403340434053406340734083409341034113412341334143415341634173418341934203421342234233424342534263427342834293430343134323433343434353436343734383439344034413442344334443445344634473448344934503451345234533454345534563457345834593460346134623463346434653466346734683469347034713472347334743475347634773478347934803481348234833484348534863487348834893490349134923493349434953496349734983499350035013502350335043505350635073508350935103511351235133514351535163517351835193520352135223523352435253526352735283529353035313532353335343535353635373538353935403541354235433544354535463547354835493550355135523553355435553556355735583559356035613562356335643565356635673568356935703571357235733574357535763577357835793580358135823583358435853586358735883589359035913592359335943595359635973598359936003601360236033604360536063607360836093610361136123613361436153616361736183619362036213622362336243625362636273628362936303631363236333634363536363637363836393640364136423643364436453646364736483649365036513652365336543655365636573658365936603661366236633664366536663667366836693670367136723673367436753676367736783679368036813682368336843685368636873688368936903691369236933694369536963697369836993700370137023703370437053706370737083709371037113712371337143715371637173718371937203721372237233724372537263727372837293730373137323733373437353736373737383739374037413742374337443745374637473748374937503751375237533754375537563757375837593760376137623763376437653766
  1. <?php
  2. // +----------------------------------------------------------------------
  3. // | ThinkPHP [ WE CAN DO IT JUST THINK ]
  4. // +----------------------------------------------------------------------
  5. // | Copyright (c) 2006~2018 http://thinkphp.cn All rights reserved.
  6. // +----------------------------------------------------------------------
  7. // | Licensed ( http://www.apache.org/licenses/LICENSE-2.0 )
  8. // +----------------------------------------------------------------------
  9. // | Author: liu21st <liu21st@gmail.com>
  10. // +----------------------------------------------------------------------
  11. namespace think\db;
  12. use PDO;
  13. use think\Collection;
  14. use think\Container;
  15. use think\Db;
  16. use think\db\exception\BindParamException;
  17. use think\db\exception\DataNotFoundException;
  18. use think\db\exception\ModelNotFoundException;
  19. use think\Exception;
  20. use think\exception\DbException;
  21. use think\exception\PDOException;
  22. use think\Loader;
  23. use think\Model;
  24. use think\model\Collection as ModelCollection;
  25. use think\model\Relation;
  26. use think\model\relation\OneToOne;
  27. use think\Paginator;
  28. class Query
  29. {
  30. /**
  31. * 当前数据库连接对象
  32. * @var Connection
  33. */
  34. protected $connection;
  35. /**
  36. * 当前模型对象
  37. * @var Model
  38. */
  39. protected $model;
  40. /**
  41. * 当前数据表名称(不含前缀)
  42. * @var string
  43. */
  44. protected $name = '';
  45. /**
  46. * 当前数据表主键
  47. * @var string|array
  48. */
  49. protected $pk;
  50. /**
  51. * 当前数据表前缀
  52. * @var string
  53. */
  54. protected $prefix = '';
  55. /**
  56. * 当前查询参数
  57. * @var array
  58. */
  59. protected $options = [];
  60. /**
  61. * 当前参数绑定
  62. * @var array
  63. */
  64. protected $bind = [];
  65. /**
  66. * 事件回调
  67. * @var array
  68. */
  69. private static $event = [];
  70. /**
  71. * 扩展查询方法
  72. * @var array
  73. */
  74. private static $extend = [];
  75. /**
  76. * 读取主库的表
  77. * @var array
  78. */
  79. protected static $readMaster = [];
  80. /**
  81. * 日期查询表达式
  82. * @var array
  83. */
  84. protected $timeRule = [
  85. 'today' => ['today', 'tomorrow -1second'],
  86. 'yesterday' => ['yesterday', 'today -1second'],
  87. 'week' => ['this week 00:00:00', 'next week 00:00:00 -1second'],
  88. 'last week' => ['last week 00:00:00', 'this week 00:00:00 -1second'],
  89. 'month' => ['first Day of this month 00:00:00', 'first Day of next month 00:00:00 -1second'],
  90. 'last month' => ['first Day of last month 00:00:00', 'first Day of this month 00:00:00 -1second'],
  91. 'year' => ['this year 1/1', 'next year 1/1 -1second'],
  92. 'last year' => ['last year 1/1', 'this year 1/1 -1second'],
  93. ];
  94. /**
  95. * 日期查询快捷定义
  96. * @var array
  97. */
  98. protected $timeExp = ['d' => 'today', 'w' => 'week', 'm' => 'month', 'y' => 'year'];
  99. /**
  100. * 架构函数
  101. * @access public
  102. */
  103. public function __construct(Connection $connection = null)
  104. {
  105. if (is_null($connection)) {
  106. $this->connection = Db::connect();
  107. } else {
  108. $this->connection = $connection;
  109. }
  110. $this->prefix = $this->connection->getConfig('prefix');
  111. }
  112. /**
  113. * 创建一个新的查询对象
  114. * @access public
  115. * @return Query
  116. */
  117. public function newQuery()
  118. {
  119. $query = new static($this->connection);
  120. if ($this->model) {
  121. $query->model($this->model);
  122. }
  123. if (isset($this->options['table'])) {
  124. $query->table($this->options['table']);
  125. } else {
  126. $query->name($this->name);
  127. }
  128. if (isset($this->options['json'])) {
  129. $query->json($this->options['json'], $this->options['json_assoc']);
  130. }
  131. if (isset($this->options['field_type'])) {
  132. $query->setJsonFieldType($this->options['field_type']);
  133. }
  134. return $query;
  135. }
  136. /**
  137. * 利用__call方法实现一些特殊的Model方法
  138. * @access public
  139. * @param string $method 方法名称
  140. * @param array $args 调用参数
  141. * @return mixed
  142. * @throws DbException
  143. * @throws Exception
  144. */
  145. public function __call($method, $args)
  146. {
  147. if (isset(self::$extend[strtolower($method)])) {
  148. // 调用扩展查询方法
  149. array_unshift($args, $this);
  150. return Container::getInstance()
  151. ->invoke(self::$extend[strtolower($method)], $args);
  152. } elseif (strtolower(substr($method, 0, 5)) == 'getby') {
  153. // 根据某个字段获取记录
  154. $field = Loader::parseName(substr($method, 5));
  155. return $this->where($field, '=', $args[0])->find();
  156. } elseif (strtolower(substr($method, 0, 10)) == 'getfieldby') {
  157. // 根据某个字段获取记录的某个值
  158. $name = Loader::parseName(substr($method, 10));
  159. return $this->where($name, '=', $args[0])->value($args[1]);
  160. } elseif (strtolower(substr($method, 0, 7)) == 'whereor') {
  161. $name = Loader::parseName(substr($method, 7));
  162. array_unshift($args, $name);
  163. return call_user_func_array([$this, 'whereOr'], $args);
  164. } elseif (strtolower(substr($method, 0, 5)) == 'where') {
  165. $name = Loader::parseName(substr($method, 5));
  166. array_unshift($args, $name);
  167. return call_user_func_array([$this, 'where'], $args);
  168. } elseif ($this->model && method_exists($this->model, 'scope' . $method)) {
  169. // 动态调用命名范围
  170. $method = 'scope' . $method;
  171. array_unshift($args, $this);
  172. call_user_func_array([$this->model, $method], $args);
  173. return $this;
  174. } else {
  175. throw new Exception('method not exist:' . ($this->model ? get_class($this->model) : static::class) . '->' . $method);
  176. }
  177. }
  178. /**
  179. * 扩展查询方法
  180. * @access public
  181. * @param string|array $method 查询方法名
  182. * @param callable $callback
  183. * @return void
  184. */
  185. public static function extend($method, $callback = null)
  186. {
  187. if (is_array($method)) {
  188. foreach ($method as $key => $val) {
  189. self::$extend[strtolower($key)] = $val;
  190. }
  191. } else {
  192. self::$extend[strtolower($method)] = $callback;
  193. }
  194. }
  195. /**
  196. * 设置当前的数据库Connection对象
  197. * @access public
  198. * @param Connection $connection
  199. * @return $this
  200. */
  201. public function setConnection(Connection $connection)
  202. {
  203. $this->connection = $connection;
  204. $this->prefix = $this->connection->getConfig('prefix');
  205. return $this;
  206. }
  207. /**
  208. * 获取当前的数据库Connection对象
  209. * @access public
  210. * @return Connection
  211. */
  212. public function getConnection()
  213. {
  214. return $this->connection;
  215. }
  216. /**
  217. * 指定模型
  218. * @access public
  219. * @param Model $model 模型对象实例
  220. * @return $this
  221. */
  222. public function model(Model $model)
  223. {
  224. $this->model = $model;
  225. return $this;
  226. }
  227. /**
  228. * 获取当前的模型对象
  229. * @access public
  230. * @return Model|null
  231. */
  232. public function getModel()
  233. {
  234. return $this->model ? $this->model->setQuery($this) : null;
  235. }
  236. /**
  237. * 设置从主库读取数据
  238. * @access public
  239. * @param bool $all 是否所有表有效
  240. * @return $this
  241. */
  242. public function readMaster($all = false)
  243. {
  244. $table = $all ? '*' : $this->getTable();
  245. static::$readMaster[$table] = true;
  246. return $this;
  247. }
  248. /**
  249. * 指定当前数据表名(不含前缀)
  250. * @access public
  251. * @param string $name
  252. * @return $this
  253. */
  254. public function name($name)
  255. {
  256. $this->name = $name;
  257. return $this;
  258. }
  259. /**
  260. * 获取当前的数据表名称
  261. * @access public
  262. * @return string
  263. */
  264. public function getName()
  265. {
  266. return $this->name ?: $this->model->getName();
  267. }
  268. /**
  269. * 得到当前或者指定名称的数据表
  270. * @access public
  271. * @param string $name
  272. * @return string
  273. */
  274. public function getTable($name = '')
  275. {
  276. if (empty($name) && isset($this->options['table'])) {
  277. return $this->options['table'];
  278. }
  279. $name = $name ?: $this->name;
  280. return $this->prefix . Loader::parseName($name);
  281. }
  282. /**
  283. * 执行查询 返回数据集
  284. * @access public
  285. * @param string $sql sql指令
  286. * @param array $bind 参数绑定
  287. * @param boolean $master 是否在主服务器读操作
  288. * @param bool $pdo 是否返回PDO对象
  289. * @return mixed
  290. * @throws BindParamException
  291. * @throws PDOException
  292. */
  293. public function query($sql, $bind = [], $master = false, $pdo = false)
  294. {
  295. return $this->connection->query($sql, $bind, $master, $pdo);
  296. }
  297. /**
  298. * 执行语句
  299. * @access public
  300. * @param string $sql sql指令
  301. * @param array $bind 参数绑定
  302. * @return int
  303. * @throws BindParamException
  304. * @throws PDOException
  305. */
  306. public function execute($sql, $bind = [])
  307. {
  308. return $this->connection->execute($sql, $bind, $this);
  309. }
  310. /**
  311. * 监听SQL执行
  312. * @access public
  313. * @param callable $callback 回调方法
  314. * @return void
  315. */
  316. public function listen($callback)
  317. {
  318. $this->connection->listen($callback);
  319. }
  320. /**
  321. * 获取最近插入的ID
  322. * @access public
  323. * @param string $sequence 自增序列名
  324. * @return string
  325. */
  326. public function getLastInsID($sequence = null)
  327. {
  328. return $this->connection->getLastInsID($sequence);
  329. }
  330. /**
  331. * 获取返回或者影响的记录数
  332. * @access public
  333. * @return integer
  334. */
  335. public function getNumRows()
  336. {
  337. return $this->connection->getNumRows();
  338. }
  339. /**
  340. * 获取最近一次查询的sql语句
  341. * @access public
  342. * @return string
  343. */
  344. public function getLastSql()
  345. {
  346. return $this->connection->getLastSql();
  347. }
  348. /**
  349. * 执行数据库Xa事务
  350. * @access public
  351. * @param callable $callback 数据操作方法回调
  352. * @param array $dbs 多个查询对象或者连接对象
  353. * @return mixed
  354. * @throws PDOException
  355. * @throws \Exception
  356. * @throws \Throwable
  357. */
  358. public function transactionXa($callback, array $dbs = [])
  359. {
  360. $xid = uniqid('xa');
  361. if (empty($dbs)) {
  362. $dbs[] = $this->getConnection();
  363. }
  364. foreach ($dbs as $key => $db) {
  365. if ($db instanceof Query) {
  366. $db = $db->getConnection();
  367. $dbs[$key] = $db;
  368. }
  369. $db->startTransXa($xid);
  370. }
  371. try {
  372. $result = null;
  373. if (is_callable($callback)) {
  374. $result = call_user_func_array($callback, [$this]);
  375. }
  376. foreach ($dbs as $db) {
  377. $db->prepareXa($xid);
  378. }
  379. foreach ($dbs as $db) {
  380. $db->commitXa($xid);
  381. }
  382. return $result;
  383. } catch (\Exception $e) {
  384. foreach ($dbs as $db) {
  385. $db->rollbackXa($xid);
  386. }
  387. throw $e;
  388. } catch (\Throwable $e) {
  389. foreach ($dbs as $db) {
  390. $db->rollbackXa($xid);
  391. }
  392. throw $e;
  393. }
  394. }
  395. /**
  396. * 执行数据库事务
  397. * @access public
  398. * @param callable $callback 数据操作方法回调
  399. * @return mixed
  400. */
  401. public function transaction($callback)
  402. {
  403. return $this->connection->transaction($callback);
  404. }
  405. /**
  406. * 启动事务
  407. * @access public
  408. * @return void
  409. */
  410. public function startTrans()
  411. {
  412. $this->connection->startTrans();
  413. }
  414. /**
  415. * 用于非自动提交状态下面的查询提交
  416. * @access public
  417. * @return void
  418. * @throws PDOException
  419. */
  420. public function commit()
  421. {
  422. $this->connection->commit();
  423. }
  424. /**
  425. * 事务回滚
  426. * @access public
  427. * @return void
  428. * @throws PDOException
  429. */
  430. public function rollback()
  431. {
  432. $this->connection->rollback();
  433. }
  434. /**
  435. * 批处理执行SQL语句
  436. * 批处理的指令都认为是execute操作
  437. * @access public
  438. * @param array $sql SQL批处理指令
  439. * @return boolean
  440. */
  441. public function batchQuery($sql = [])
  442. {
  443. return $this->connection->batchQuery($sql);
  444. }
  445. /**
  446. * 获取数据库的配置参数
  447. * @access public
  448. * @param string $name 参数名称
  449. * @return mixed
  450. */
  451. public function getConfig($name = '')
  452. {
  453. return $this->connection->getConfig($name);
  454. }
  455. /**
  456. * 获取数据表字段信息
  457. * @access public
  458. * @param string $tableName 数据表名
  459. * @return array
  460. */
  461. public function getTableFields($tableName = '')
  462. {
  463. if ('' == $tableName) {
  464. $tableName = isset($this->options['table']) ? $this->options['table'] : $this->getTable();
  465. }
  466. return $this->connection->getTableFields($tableName);
  467. }
  468. /**
  469. * 获取数据表字段类型
  470. * @access public
  471. * @param string $tableName 数据表名
  472. * @param string $field 字段名
  473. * @return array|string
  474. */
  475. public function getFieldsType($tableName = '', $field = null)
  476. {
  477. if ('' == $tableName) {
  478. $tableName = isset($this->options['table']) ? $this->options['table'] : $this->getTable();
  479. }
  480. return $this->connection->getFieldsType($tableName, $field);
  481. }
  482. /**
  483. * 得到分表的的数据表名
  484. * @access public
  485. * @param array $data 操作的数据
  486. * @param string $field 分表依据的字段
  487. * @param array $rule 分表规则
  488. * @return array
  489. */
  490. public function getPartitionTableName($data, $field, $rule = [])
  491. {
  492. // 对数据表进行分区
  493. if ($field && isset($data[$field])) {
  494. $value = $data[$field];
  495. $type = $rule['type'];
  496. switch ($type) {
  497. case 'id':
  498. // 按照id范围分表
  499. $step = $rule['expr'];
  500. $seq = floor($value / $step) + 1;
  501. break;
  502. case 'year':
  503. // 按照年份分表
  504. if (!is_numeric($value)) {
  505. $value = strtotime($value);
  506. }
  507. $seq = date('Y', $value) - $rule['expr'] + 1;
  508. break;
  509. case 'mod':
  510. // 按照id的模数分表
  511. $seq = ($value % $rule['num']) + 1;
  512. break;
  513. case 'md5':
  514. // 按照md5的序列分表
  515. $seq = (ord(substr(md5($value), 0, 1)) % $rule['num']) + 1;
  516. break;
  517. default:
  518. if (function_exists($type)) {
  519. // 支持指定函数哈希
  520. $value = $type($value);
  521. }
  522. $seq = (ord(substr($value, 0, 1)) % $rule['num']) + 1;
  523. }
  524. return $this->getTable() . '_' . $seq;
  525. }
  526. // 当设置的分表字段不在查询条件或者数据中
  527. // 进行联合查询,必须设定 partition['num']
  528. $tableName = [];
  529. for ($i = 0; $i < $rule['num']; $i++) {
  530. $tableName[] = 'SELECT * FROM ' . $this->getTable() . '_' . ($i + 1);
  531. }
  532. return ['( ' . implode(" UNION ", $tableName) . ' )' => $this->name];
  533. }
  534. /**
  535. * 得到某个字段的值
  536. * @access public
  537. * @param string $field 字段名
  538. * @param mixed $default 默认值
  539. * @return mixed
  540. */
  541. public function value($field, $default = null)
  542. {
  543. $this->parseOptions();
  544. return $this->connection->value($this, $field, $default);
  545. }
  546. /**
  547. * 得到某个列的数组
  548. * @access public
  549. * @param string $field 字段名 多个字段用逗号分隔
  550. * @param string $key 索引
  551. * @return array
  552. */
  553. public function column($field, $key = '')
  554. {
  555. $this->parseOptions();
  556. return $this->connection->column($this, $field, $key);
  557. }
  558. /**
  559. * 聚合查询
  560. * @access public
  561. * @param string $aggregate 聚合方法
  562. * @param string|Expression $field 字段名
  563. * @param bool $force 强制转为数字类型
  564. * @return mixed
  565. */
  566. public function aggregate($aggregate, $field, $force = false)
  567. {
  568. $this->parseOptions();
  569. $result = $this->connection->aggregate($this, $aggregate, $field);
  570. if (!empty($this->options['fetch_sql'])) {
  571. return $result;
  572. } elseif ($force) {
  573. $result = (float) $result;
  574. }
  575. return $result;
  576. }
  577. /**
  578. * COUNT查询
  579. * @access public
  580. * @param string|Expression $field 字段名
  581. * @return float|string
  582. */
  583. public function count($field = '*')
  584. {
  585. if (!empty($this->options['group'])) {
  586. // 支持GROUP
  587. $options = $this->getOptions();
  588. $subSql = $this->options($options)
  589. ->field('count(' . $field . ') AS think_count')
  590. ->bind($this->bind)
  591. ->buildSql();
  592. $query = $this->newQuery()->table([$subSql => '_group_count_']);
  593. if (!empty($options['fetch_sql'])) {
  594. $query->fetchSql(true);
  595. }
  596. $count = $query->aggregate('COUNT', '*', true);
  597. } else {
  598. $count = $this->aggregate('COUNT', $field, true);
  599. }
  600. return is_string($count) ? $count : (int) $count;
  601. }
  602. /**
  603. * SUM查询
  604. * @access public
  605. * @param string|Expression $field 字段名
  606. * @return float
  607. */
  608. public function sum($field)
  609. {
  610. return $this->aggregate('SUM', $field, true);
  611. }
  612. /**
  613. * MIN查询
  614. * @access public
  615. * @param string|Expression $field 字段名
  616. * @param bool $force 强制转为数字类型
  617. * @return mixed
  618. */
  619. public function min($field, $force = true)
  620. {
  621. return $this->aggregate('MIN', $field, $force);
  622. }
  623. /**
  624. * MAX查询
  625. * @access public
  626. * @param string|Expression $field 字段名
  627. * @param bool $force 强制转为数字类型
  628. * @return mixed
  629. */
  630. public function max($field, $force = true)
  631. {
  632. return $this->aggregate('MAX', $field, $force);
  633. }
  634. /**
  635. * AVG查询
  636. * @access public
  637. * @param string|Expression $field 字段名
  638. * @return float
  639. */
  640. public function avg($field)
  641. {
  642. return $this->aggregate('AVG', $field, true);
  643. }
  644. /**
  645. * 设置记录的某个字段值
  646. * 支持使用数据库字段和方法
  647. * @access public
  648. * @param string|array $field 字段名
  649. * @param mixed $value 字段值
  650. * @return integer
  651. */
  652. public function setField($field, $value = '')
  653. {
  654. if (is_array($field)) {
  655. $data = $field;
  656. } else {
  657. $data[$field] = $value;
  658. }
  659. return $this->update($data);
  660. }
  661. /**
  662. * 字段值(延迟)增长
  663. * @access public
  664. * @param string $field 字段名
  665. * @param integer $step 增长值
  666. * @param integer $lazyTime 延时时间(s)
  667. * @return integer|true
  668. * @throws Exception
  669. */
  670. public function setInc($field, $step = 1, $lazyTime = 0)
  671. {
  672. $condition = !empty($this->options['where']) ? $this->options['where'] : [];
  673. if (empty($condition)) {
  674. // 没有条件不做任何更新
  675. throw new Exception('no data to update');
  676. }
  677. if ($lazyTime > 0) {
  678. // 延迟写入
  679. $guid = md5($this->getTable() . '_' . $field . '_' . serialize($condition));
  680. $step = $this->lazyWrite('inc', $guid, $step, $lazyTime);
  681. if (false === $step) {
  682. // 清空查询条件
  683. $this->options = [];
  684. return true;
  685. }
  686. }
  687. return $this->setField($field, ['INC', $step]);
  688. }
  689. /**
  690. * 字段值(延迟)减少
  691. * @access public
  692. * @param string $field 字段名
  693. * @param integer $step 减少值
  694. * @param integer $lazyTime 延时时间(s)
  695. * @return integer|true
  696. * @throws Exception
  697. */
  698. public function setDec($field, $step = 1, $lazyTime = 0)
  699. {
  700. $condition = !empty($this->options['where']) ? $this->options['where'] : [];
  701. if (empty($condition)) {
  702. // 没有条件不做任何更新
  703. throw new Exception('no data to update');
  704. }
  705. if ($lazyTime > 0) {
  706. // 延迟写入
  707. $guid = md5($this->getTable() . '_' . $field . '_' . serialize($condition));
  708. $step = $this->lazyWrite('dec', $guid, $step, $lazyTime);
  709. if (false === $step) {
  710. // 清空查询条件
  711. $this->options = [];
  712. return true;
  713. }
  714. $value = ['INC', $step];
  715. } else {
  716. $value = ['DEC', $step];
  717. }
  718. return $this->setField($field, $value);
  719. }
  720. /**
  721. * 延时更新检查 返回false表示需要延时
  722. * 否则返回实际写入的数值
  723. * @access protected
  724. * @param string $type 自增或者自减
  725. * @param string $guid 写入标识
  726. * @param integer $step 写入步进值
  727. * @param integer $lazyTime 延时时间(s)
  728. * @return false|integer
  729. */
  730. protected function lazyWrite($type, $guid, $step, $lazyTime)
  731. {
  732. $cache = Container::get('cache');
  733. if (!$cache->has($guid . '_time')) {
  734. // 计时开始
  735. $cache->set($guid . '_time', time(), 0);
  736. $cache->$type($guid, $step);
  737. } elseif (time() > $cache->get($guid . '_time') + $lazyTime) {
  738. // 删除缓存
  739. $value = $cache->$type($guid, $step);
  740. $cache->rm($guid);
  741. $cache->rm($guid . '_time');
  742. return 0 === $value ? false : $value;
  743. } else {
  744. // 更新缓存
  745. $cache->$type($guid, $step);
  746. }
  747. return false;
  748. }
  749. /**
  750. * 查询SQL组装 join
  751. * @access public
  752. * @param mixed $join 关联的表名
  753. * @param mixed $condition 条件
  754. * @param string $type JOIN类型
  755. * @param array $bind 参数绑定
  756. * @return $this
  757. */
  758. public function join($join, $condition = null, $type = 'INNER', $bind = [])
  759. {
  760. if (empty($condition)) {
  761. // 如果为组数,则循环调用join
  762. foreach ($join as $key => $value) {
  763. if (is_array($value) && 2 <= count($value)) {
  764. $this->join($value[0], $value[1], isset($value[2]) ? $value[2] : $type);
  765. }
  766. }
  767. } else {
  768. $table = $this->getJoinTable($join);
  769. if ($bind) {
  770. $this->bindParams($condition, $bind);
  771. }
  772. $this->options['join'][] = [$table, strtoupper($type), $condition];
  773. }
  774. return $this;
  775. }
  776. /**
  777. * LEFT JOIN
  778. * @access public
  779. * @param mixed $join 关联的表名
  780. * @param mixed $condition 条件
  781. * @param array $bind 参数绑定
  782. * @return $this
  783. */
  784. public function leftJoin($join, $condition = null, $bind = [])
  785. {
  786. return $this->join($join, $condition, 'LEFT');
  787. }
  788. /**
  789. * RIGHT JOIN
  790. * @access public
  791. * @param mixed $join 关联的表名
  792. * @param mixed $condition 条件
  793. * @param array $bind 参数绑定
  794. * @return $this
  795. */
  796. public function rightJoin($join, $condition = null, $bind = [])
  797. {
  798. return $this->join($join, $condition, 'RIGHT');
  799. }
  800. /**
  801. * FULL JOIN
  802. * @access public
  803. * @param mixed $join 关联的表名
  804. * @param mixed $condition 条件
  805. * @param array $bind 参数绑定
  806. * @return $this
  807. */
  808. public function fullJoin($join, $condition = null, $bind = [])
  809. {
  810. return $this->join($join, $condition, 'FULL');
  811. }
  812. /**
  813. * 获取Join表名及别名 支持
  814. * ['prefix_table或者子查询'=>'alias'] 'table alias'
  815. * @access protected
  816. * @param array|string $join
  817. * @param string $alias
  818. * @return string
  819. */
  820. protected function getJoinTable($join, &$alias = null)
  821. {
  822. if (is_array($join)) {
  823. $table = $join;
  824. $alias = array_shift($join);
  825. } else {
  826. $join = trim($join);
  827. if (false !== strpos($join, '(')) {
  828. // 使用子查询
  829. $table = $join;
  830. } else {
  831. $prefix = $this->prefix;
  832. if (strpos($join, ' ')) {
  833. // 使用别名
  834. list($table, $alias) = explode(' ', $join);
  835. } else {
  836. $table = $join;
  837. if (false === strpos($join, '.') && 0 !== strpos($join, '__')) {
  838. $alias = $join;
  839. }
  840. }
  841. if ($prefix && false === strpos($table, '.') && 0 !== strpos($table, $prefix) && 0 !== strpos($table, '__')) {
  842. $table = $this->getTable($table);
  843. }
  844. }
  845. if (isset($alias) && $table != $alias) {
  846. $table = [$table => $alias];
  847. }
  848. }
  849. return $table;
  850. }
  851. /**
  852. * 查询SQL组装 union
  853. * @access public
  854. * @param mixed $union
  855. * @param boolean $all
  856. * @return $this
  857. */
  858. public function union($union, $all = false)
  859. {
  860. if (empty($union)) {
  861. return $this;
  862. }
  863. $this->options['union']['type'] = $all ? 'UNION ALL' : 'UNION';
  864. if (is_array($union)) {
  865. $this->options['union'] = array_merge($this->options['union'], $union);
  866. } else {
  867. $this->options['union'][] = $union;
  868. }
  869. return $this;
  870. }
  871. /**
  872. * 查询SQL组装 union all
  873. * @access public
  874. * @param mixed $union
  875. * @return $this
  876. */
  877. public function unionAll($union)
  878. {
  879. return $this->union($union, true);
  880. }
  881. /**
  882. * 指定查询字段 支持字段排除和指定数据表
  883. * @access public
  884. * @param mixed $field
  885. * @param boolean $except 是否排除
  886. * @param string $tableName 数据表名
  887. * @param string $prefix 字段前缀
  888. * @param string $alias 别名前缀
  889. * @return $this
  890. */
  891. public function field($field, $except = false, $tableName = '', $prefix = '', $alias = '')
  892. {
  893. if (empty($field)) {
  894. return $this;
  895. } elseif ($field instanceof Expression) {
  896. $this->options['field'][] = $field;
  897. return $this;
  898. }
  899. if (is_string($field)) {
  900. if (preg_match('/[\<\'\"\(]/', $field)) {
  901. return $this->fieldRaw($field);
  902. }
  903. $field = array_map('trim', explode(',', $field));
  904. }
  905. if (true === $field) {
  906. // 获取全部字段
  907. $fields = $this->getTableFields($tableName);
  908. $field = $fields ?: ['*'];
  909. } elseif ($except) {
  910. // 字段排除
  911. $fields = $this->getTableFields($tableName);
  912. $field = $fields ? array_diff($fields, $field) : $field;
  913. }
  914. if ($tableName) {
  915. // 添加统一的前缀
  916. $prefix = $prefix ?: $tableName;
  917. foreach ($field as $key => &$val) {
  918. if (is_numeric($key) && $alias) {
  919. $field[$prefix . '.' . $val] = $alias . $val;
  920. unset($field[$key]);
  921. } elseif (is_numeric($key)) {
  922. $val = $prefix . '.' . $val;
  923. }
  924. }
  925. }
  926. if (isset($this->options['field'])) {
  927. $field = array_merge((array) $this->options['field'], $field);
  928. }
  929. $this->options['field'] = array_unique($field);
  930. return $this;
  931. }
  932. /**
  933. * 表达式方式指定查询字段
  934. * @access public
  935. * @param string $field 字段名
  936. * @return $this
  937. */
  938. public function fieldRaw($field)
  939. {
  940. $this->options['field'][] = $this->raw($field);
  941. return $this;
  942. }
  943. /**
  944. * 设置数据
  945. * @access public
  946. * @param mixed $field 字段名或者数据
  947. * @param mixed $value 字段值
  948. * @return $this
  949. */
  950. public function data($field, $value = null)
  951. {
  952. if (is_array($field)) {
  953. $this->options['data'] = isset($this->options['data']) ? array_merge($this->options['data'], $field) : $field;
  954. } else {
  955. $this->options['data'][$field] = $value;
  956. }
  957. return $this;
  958. }
  959. /**
  960. * 字段值增长
  961. * @access public
  962. * @param string|array $field 字段名
  963. * @param integer $step 增长值
  964. * @return $this
  965. */
  966. public function inc($field, $step = 1, $op = 'INC')
  967. {
  968. $fields = is_string($field) ? explode(',', $field) : $field;
  969. foreach ($fields as $field => $val) {
  970. if (is_numeric($field)) {
  971. $field = $val;
  972. } else {
  973. $step = $val;
  974. }
  975. $this->data($field, [$op, $step]);
  976. }
  977. return $this;
  978. }
  979. /**
  980. * 字段值减少
  981. * @access public
  982. * @param string|array $field 字段名
  983. * @param integer $step 增长值
  984. * @return $this
  985. */
  986. public function dec($field, $step = 1)
  987. {
  988. return $this->inc($field, $step, 'DEC');
  989. }
  990. /**
  991. * 使用表达式设置数据
  992. * @access public
  993. * @param string $field 字段名
  994. * @param string $value 字段值
  995. * @return $this
  996. */
  997. public function exp($field, $value)
  998. {
  999. $this->data($field, $this->raw($value));
  1000. return $this;
  1001. }
  1002. /**
  1003. * 使用表达式设置数据
  1004. * @access public
  1005. * @param mixed $value 表达式
  1006. * @return Expression
  1007. */
  1008. public function raw($value)
  1009. {
  1010. return new Expression($value);
  1011. }
  1012. /**
  1013. * 指定JOIN查询字段
  1014. * @access public
  1015. * @param string|array $table 数据表
  1016. * @param string|array $field 查询字段
  1017. * @param mixed $on JOIN条件
  1018. * @param string $type JOIN类型
  1019. * @return $this
  1020. */
  1021. public function view($join, $field = true, $on = null, $type = 'INNER')
  1022. {
  1023. $this->options['view'] = true;
  1024. if (is_array($join) && key($join) === 0) {
  1025. foreach ($join as $key => $val) {
  1026. $this->view($val[0], $val[1], isset($val[2]) ? $val[2] : null, isset($val[3]) ? $val[3] : 'INNER');
  1027. }
  1028. } else {
  1029. $fields = [];
  1030. $table = $this->getJoinTable($join, $alias);
  1031. if (true === $field) {
  1032. $fields = $alias . '.*';
  1033. } else {
  1034. if (is_string($field)) {
  1035. $field = explode(',', $field);
  1036. }
  1037. foreach ($field as $key => $val) {
  1038. if (is_numeric($key)) {
  1039. $fields[] = $alias . '.' . $val;
  1040. $this->options['map'][$val] = $alias . '.' . $val;
  1041. } else {
  1042. if (preg_match('/[,=\.\'\"\(\s]/', $key)) {
  1043. $name = $key;
  1044. } else {
  1045. $name = $alias . '.' . $key;
  1046. }
  1047. $fields[] = $name . ' AS ' . $val;
  1048. $this->options['map'][$val] = $name;
  1049. }
  1050. }
  1051. }
  1052. $this->field($fields);
  1053. if ($on) {
  1054. $this->join($table, $on, $type);
  1055. } else {
  1056. $this->table($table);
  1057. }
  1058. }
  1059. return $this;
  1060. }
  1061. /**
  1062. * 设置分表规则
  1063. * @access public
  1064. * @param array $data 操作的数据
  1065. * @param string $field 分表依据的字段
  1066. * @param array $rule 分表规则
  1067. * @return $this
  1068. */
  1069. public function partition($data, $field, $rule = [])
  1070. {
  1071. $this->options['table'] = $this->getPartitionTableName($data, $field, $rule);
  1072. return $this;
  1073. }
  1074. /**
  1075. * 指定AND查询条件
  1076. * @access public
  1077. * @param mixed $field 查询字段
  1078. * @param mixed $op 查询表达式
  1079. * @param mixed $condition 查询条件
  1080. * @return $this
  1081. */
  1082. public function where($field, $op = null, $condition = null)
  1083. {
  1084. $param = func_get_args();
  1085. array_shift($param);
  1086. return $this->parseWhereExp('AND', $field, $op, $condition, $param);
  1087. }
  1088. /**
  1089. * 指定OR查询条件
  1090. * @access public
  1091. * @param mixed $field 查询字段
  1092. * @param mixed $op 查询表达式
  1093. * @param mixed $condition 查询条件
  1094. * @return $this
  1095. */
  1096. public function whereOr($field, $op = null, $condition = null)
  1097. {
  1098. $param = func_get_args();
  1099. array_shift($param);
  1100. return $this->parseWhereExp('OR', $field, $op, $condition, $param);
  1101. }
  1102. /**
  1103. * 指定XOR查询条件
  1104. * @access public
  1105. * @param mixed $field 查询字段
  1106. * @param mixed $op 查询表达式
  1107. * @param mixed $condition 查询条件
  1108. * @return $this
  1109. */
  1110. public function whereXor($field, $op = null, $condition = null)
  1111. {
  1112. $param = func_get_args();
  1113. array_shift($param);
  1114. return $this->parseWhereExp('XOR', $field, $op, $condition, $param);
  1115. }
  1116. /**
  1117. * 指定Null查询条件
  1118. * @access public
  1119. * @param mixed $field 查询字段
  1120. * @param string $logic 查询逻辑 and or xor
  1121. * @return $this
  1122. */
  1123. public function whereNull($field, $logic = 'AND')
  1124. {
  1125. return $this->parseWhereExp($logic, $field, 'NULL', null, [], true);
  1126. }
  1127. /**
  1128. * 指定NotNull查询条件
  1129. * @access public
  1130. * @param mixed $field 查询字段
  1131. * @param string $logic 查询逻辑 and or xor
  1132. * @return $this
  1133. */
  1134. public function whereNotNull($field, $logic = 'AND')
  1135. {
  1136. return $this->parseWhereExp($logic, $field, 'NOTNULL', null, [], true);
  1137. }
  1138. /**
  1139. * 指定Exists查询条件
  1140. * @access public
  1141. * @param mixed $condition 查询条件
  1142. * @param string $logic 查询逻辑 and or xor
  1143. * @return $this
  1144. */
  1145. public function whereExists($condition, $logic = 'AND')
  1146. {
  1147. if (is_string($condition)) {
  1148. $condition = $this->raw($condition);
  1149. }
  1150. $this->options['where'][strtoupper($logic)][] = ['', 'EXISTS', $condition];
  1151. return $this;
  1152. }
  1153. /**
  1154. * 指定NotExists查询条件
  1155. * @access public
  1156. * @param mixed $condition 查询条件
  1157. * @param string $logic 查询逻辑 and or xor
  1158. * @return $this
  1159. */
  1160. public function whereNotExists($condition, $logic = 'AND')
  1161. {
  1162. if (is_string($condition)) {
  1163. $condition = $this->raw($condition);
  1164. }
  1165. $this->options['where'][strtoupper($logic)][] = ['', 'NOT EXISTS', $condition];
  1166. return $this;
  1167. }
  1168. /**
  1169. * 指定In查询条件
  1170. * @access public
  1171. * @param mixed $field 查询字段
  1172. * @param mixed $condition 查询条件
  1173. * @param string $logic 查询逻辑 and or xor
  1174. * @return $this
  1175. */
  1176. public function whereIn($field, $condition, $logic = 'AND')
  1177. {
  1178. return $this->parseWhereExp($logic, $field, 'IN', $condition, [], true);
  1179. }
  1180. /**
  1181. * 指定NotIn查询条件
  1182. * @access public
  1183. * @param mixed $field 查询字段
  1184. * @param mixed $condition 查询条件
  1185. * @param string $logic 查询逻辑 and or xor
  1186. * @return $this
  1187. */
  1188. public function whereNotIn($field, $condition, $logic = 'AND')
  1189. {
  1190. return $this->parseWhereExp($logic, $field, 'NOT IN', $condition, [], true);
  1191. }
  1192. /**
  1193. * 指定Like查询条件
  1194. * @access public
  1195. * @param mixed $field 查询字段
  1196. * @param mixed $condition 查询条件
  1197. * @param string $logic 查询逻辑 and or xor
  1198. * @return $this
  1199. */
  1200. public function whereLike($field, $condition, $logic = 'AND')
  1201. {
  1202. return $this->parseWhereExp($logic, $field, 'LIKE', $condition, [], true);
  1203. }
  1204. /**
  1205. * 指定NotLike查询条件
  1206. * @access public
  1207. * @param mixed $field 查询字段
  1208. * @param mixed $condition 查询条件
  1209. * @param string $logic 查询逻辑 and or xor
  1210. * @return $this
  1211. */
  1212. public function whereNotLike($field, $condition, $logic = 'AND')
  1213. {
  1214. return $this->parseWhereExp($logic, $field, 'NOT LIKE', $condition, [], true);
  1215. }
  1216. /**
  1217. * 指定Between查询条件
  1218. * @access public
  1219. * @param mixed $field 查询字段
  1220. * @param mixed $condition 查询条件
  1221. * @param string $logic 查询逻辑 and or xor
  1222. * @return $this
  1223. */
  1224. public function whereBetween($field, $condition, $logic = 'AND')
  1225. {
  1226. return $this->parseWhereExp($logic, $field, 'BETWEEN', $condition, [], true);
  1227. }
  1228. /**
  1229. * 指定NotBetween查询条件
  1230. * @access public
  1231. * @param mixed $field 查询字段
  1232. * @param mixed $condition 查询条件
  1233. * @param string $logic 查询逻辑 and or xor
  1234. * @return $this
  1235. */
  1236. public function whereNotBetween($field, $condition, $logic = 'AND')
  1237. {
  1238. return $this->parseWhereExp($logic, $field, 'NOT BETWEEN', $condition, [], true);
  1239. }
  1240. /**
  1241. * 比较两个字段
  1242. * @access public
  1243. * @param string|array $field1 查询字段
  1244. * @param string $operator 比较操作符
  1245. * @param string $field2 比较字段
  1246. * @param string $logic 查询逻辑 and or xor
  1247. * @return $this
  1248. */
  1249. public function whereColumn($field1, $operator = null, $field2 = null, $logic = 'AND')
  1250. {
  1251. if (is_array($field1)) {
  1252. foreach ($field1 as $item) {
  1253. $this->whereColumn($item[0], $item[1], isset($item[2]) ? $item[2] : null);
  1254. }
  1255. return $this;
  1256. }
  1257. if (is_null($field2)) {
  1258. $field2 = $operator;
  1259. $operator = '=';
  1260. }
  1261. return $this->parseWhereExp($logic, $field1, 'COLUMN', [$operator, $field2], [], true);
  1262. }
  1263. /**
  1264. * 设置软删除字段及条件
  1265. * @access public
  1266. * @param false|string $field 查询字段
  1267. * @param mixed $condition 查询条件
  1268. * @return $this
  1269. */
  1270. public function useSoftDelete($field, $condition = null)
  1271. {
  1272. if ($field) {
  1273. $this->options['soft_delete'] = [$field, $condition];
  1274. }
  1275. return $this;
  1276. }
  1277. /**
  1278. * 指定Exp查询条件
  1279. * @access public
  1280. * @param mixed $field 查询字段
  1281. * @param string $where 查询条件
  1282. * @param array $bind 参数绑定
  1283. * @param string $logic 查询逻辑 and or xor
  1284. * @return $this
  1285. */
  1286. public function whereExp($field, $where, $bind = [], $logic = 'AND')
  1287. {
  1288. if ($bind) {
  1289. $this->bindParams($where, $bind);
  1290. }
  1291. $this->options['where'][$logic][] = [$field, 'EXP', $this->raw($where)];
  1292. return $this;
  1293. }
  1294. /**
  1295. * 指定表达式查询条件
  1296. * @access public
  1297. * @param string $where 查询条件
  1298. * @param array $bind 参数绑定
  1299. * @param string $logic 查询逻辑 and or xor
  1300. * @return $this
  1301. */
  1302. public function whereRaw($where, $bind = [], $logic = 'AND')
  1303. {
  1304. if ($bind) {
  1305. $this->bindParams($where, $bind);
  1306. }
  1307. $this->options['where'][$logic][] = $this->raw($where);
  1308. return $this;
  1309. }
  1310. /**
  1311. * 参数绑定
  1312. * @access public
  1313. * @param string $sql 绑定的sql表达式
  1314. * @param array $bind 参数绑定
  1315. * @return void
  1316. */
  1317. protected function bindParams(&$sql, array $bind = [])
  1318. {
  1319. foreach ($bind as $key => $value) {
  1320. if (is_array($value)) {
  1321. $name = $this->bind($value[0], $value[1], isset($value[2]) ? $value[2] : null);
  1322. } else {
  1323. $name = $this->bind($value);
  1324. }
  1325. if (is_numeric($key)) {
  1326. $sql = substr_replace($sql, ':' . $name, strpos($sql, '?'), 1);
  1327. } else {
  1328. $sql = str_replace(':' . $key, ':' . $name, $sql);
  1329. }
  1330. }
  1331. }
  1332. /**
  1333. * 指定表达式查询条件 OR
  1334. * @access public
  1335. * @param string $where 查询条件
  1336. * @param array $bind 参数绑定
  1337. * @return $this
  1338. */
  1339. public function whereOrRaw($where, $bind = [])
  1340. {
  1341. return $this->whereRaw($where, $bind, 'OR');
  1342. }
  1343. /**
  1344. * 分析查询表达式
  1345. * @access protected
  1346. * @param string $logic 查询逻辑 and or xor
  1347. * @param mixed $field 查询字段
  1348. * @param mixed $op 查询表达式
  1349. * @param mixed $condition 查询条件
  1350. * @param array $param 查询参数
  1351. * @param bool $strict 严格模式
  1352. * @return $this
  1353. */
  1354. protected function parseWhereExp($logic, $field, $op, $condition, array $param = [], $strict = false)
  1355. {
  1356. if ($field instanceof $this) {
  1357. $this->options['where'] = $field->getOptions('where');
  1358. $this->bind($field->getBind(false));
  1359. return $this;
  1360. }
  1361. $logic = strtoupper($logic);
  1362. if ($field instanceof Where) {
  1363. $this->options['where'][$logic] = $field->parse();
  1364. return $this;
  1365. }
  1366. if (is_string($field) && !empty($this->options['via']) && false === strpos($field, '.')) {
  1367. $field = $this->options['via'] . '.' . $field;
  1368. }
  1369. if ($field instanceof Expression) {
  1370. return $this->whereRaw($field, is_array($op) ? $op : [], $logic);
  1371. } elseif ($strict) {
  1372. // 使用严格模式查询
  1373. $where = [$field, $op, $condition, $logic];
  1374. } elseif (is_array($field)) {
  1375. // 解析数组批量查询
  1376. return $this->parseArrayWhereItems($field, $logic);
  1377. } elseif ($field instanceof \Closure) {
  1378. $where = $field;
  1379. } elseif (is_string($field)) {
  1380. if (preg_match('/[,=\<\'\"\(\s]/', $field)) {
  1381. return $this->whereRaw($field, $op, $logic);
  1382. } elseif (is_string($op) && strtolower($op) == 'exp') {
  1383. $bind = isset($param[2]) && is_array($param[2]) ? $param[2] : null;
  1384. return $this->whereExp($field, $condition, $bind, $logic);
  1385. }
  1386. $where = $this->parseWhereItem($logic, $field, $op, $condition, $param);
  1387. }
  1388. if (!empty($where)) {
  1389. $this->options['where'][$logic][] = $where;
  1390. }
  1391. return $this;
  1392. }
  1393. /**
  1394. * 分析查询表达式
  1395. * @access protected
  1396. * @param string $logic 查询逻辑 and or xor
  1397. * @param mixed $field 查询字段
  1398. * @param mixed $op 查询表达式
  1399. * @param mixed $condition 查询条件
  1400. * @param array $param 查询参数
  1401. * @return mixed
  1402. */
  1403. protected function parseWhereItem($logic, $field, $op, $condition, $param = [])
  1404. {
  1405. if (is_array($op)) {
  1406. // 同一字段多条件查询
  1407. array_unshift($param, $field);
  1408. $where = $param;
  1409. } elseif ($field && is_null($condition)) {
  1410. if (in_array(strtoupper($op), ['NULL', 'NOTNULL', 'NOT NULL'], true)) {
  1411. // null查询
  1412. $where = [$field, $op, ''];
  1413. } elseif (in_array($op, ['=', 'eq', 'EQ', null], true)) {
  1414. $where = [$field, 'NULL', ''];
  1415. } elseif (in_array($op, ['<>', 'neq', 'NEQ'], true)) {
  1416. $where = [$field, 'NOTNULL', ''];
  1417. } else {
  1418. // 字段相等查询
  1419. $where = [$field, '=', $op];
  1420. }
  1421. } elseif (in_array(strtoupper($op), ['EXISTS', 'NOT EXISTS', 'NOTEXISTS'], true)) {
  1422. $where = [$field, $op, is_string($condition) ? $this->raw($condition) : $condition];
  1423. } else {
  1424. $where = $field ? [$field, $op, $condition, isset($param[2]) ? $param[2] : null] : null;
  1425. }
  1426. return $where;
  1427. }
  1428. /**
  1429. * 数组批量查询
  1430. * @access protected
  1431. * @param array $field 批量查询
  1432. * @param string $logic 查询逻辑 and or xor
  1433. * @return $this
  1434. */
  1435. protected function parseArrayWhereItems($field, $logic)
  1436. {
  1437. if (key($field) !== 0) {
  1438. $where = [];
  1439. foreach ($field as $key => $val) {
  1440. if ($val instanceof Expression) {
  1441. $where[] = [$key, 'exp', $val];
  1442. } elseif (is_null($val)) {
  1443. $where[] = [$key, 'NULL', ''];
  1444. } else {
  1445. $where[] = [$key, is_array($val) ? 'IN' : '=', $val];
  1446. }
  1447. }
  1448. } else {
  1449. // 数组批量查询
  1450. $where = $field;
  1451. }
  1452. if (!empty($where)) {
  1453. $this->options['where'][$logic] = isset($this->options['where'][$logic]) ? array_merge($this->options['where'][$logic], $where) : $where;
  1454. }
  1455. return $this;
  1456. }
  1457. /**
  1458. * 去除某个查询条件
  1459. * @access public
  1460. * @param string $field 查询字段
  1461. * @param string $logic 查询逻辑 and or xor
  1462. * @return $this
  1463. */
  1464. public function removeWhereField($field, $logic = 'AND')
  1465. {
  1466. $logic = strtoupper($logic);
  1467. if (isset($this->options['where'][$logic])) {
  1468. foreach ($this->options['where'][$logic] as $key => $val) {
  1469. if (is_array($val) && $val[0] == $field) {
  1470. unset($this->options['where'][$logic][$key]);
  1471. }
  1472. }
  1473. }
  1474. return $this;
  1475. }
  1476. /**
  1477. * 去除查询参数
  1478. * @access public
  1479. * @param string|bool $option 参数名 true 表示去除所有参数
  1480. * @return $this
  1481. */
  1482. public function removeOption($option = true)
  1483. {
  1484. if (true === $option) {
  1485. $this->options = [];
  1486. $this->bind = [];
  1487. } elseif (is_string($option) && isset($this->options[$option])) {
  1488. unset($this->options[$option]);
  1489. }
  1490. return $this;
  1491. }
  1492. /**
  1493. * 条件查询
  1494. * @access public
  1495. * @param mixed $condition 满足条件(支持闭包)
  1496. * @param \Closure|array $query 满足条件后执行的查询表达式(闭包或数组)
  1497. * @param \Closure|array $otherwise 不满足条件后执行
  1498. * @return $this
  1499. */
  1500. public function when($condition, $query, $otherwise = null)
  1501. {
  1502. if ($condition instanceof \Closure) {
  1503. $condition = $condition($this);
  1504. }
  1505. if ($condition) {
  1506. if ($query instanceof \Closure) {
  1507. $query($this, $condition);
  1508. } elseif (is_array($query)) {
  1509. $this->where($query);
  1510. }
  1511. } elseif ($otherwise) {
  1512. if ($otherwise instanceof \Closure) {
  1513. $otherwise($this, $condition);
  1514. } elseif (is_array($otherwise)) {
  1515. $this->where($otherwise);
  1516. }
  1517. }
  1518. return $this;
  1519. }
  1520. /**
  1521. * 指定查询数量
  1522. * @access public
  1523. * @param mixed $offset 起始位置
  1524. * @param mixed $length 查询数量
  1525. * @return $this
  1526. */
  1527. public function limit($offset, $length = null)
  1528. {
  1529. if (is_null($length) && strpos($offset, ',')) {
  1530. list($offset, $length) = explode(',', $offset);
  1531. }
  1532. $this->options['limit'] = intval($offset) . ($length ? ',' . intval($length) : '');
  1533. return $this;
  1534. }
  1535. /**
  1536. * 指定分页
  1537. * @access public
  1538. * @param mixed $page 页数
  1539. * @param mixed $listRows 每页数量
  1540. * @return $this
  1541. */
  1542. public function page($page, $listRows = null)
  1543. {
  1544. if (is_null($listRows) && strpos($page, ',')) {
  1545. list($page, $listRows) = explode(',', $page);
  1546. }
  1547. $this->options['page'] = [intval($page), intval($listRows)];
  1548. return $this;
  1549. }
  1550. /**
  1551. * 分页查询
  1552. * @access public
  1553. * @param int|array $listRows 每页数量 数组表示配置参数
  1554. * @param int|bool $simple 是否简洁模式或者总记录数
  1555. * @param array $config 配置参数
  1556. * page:当前页,
  1557. * path:url路径,
  1558. * query:url额外参数,
  1559. * fragment:url锚点,
  1560. * var_page:分页变量,
  1561. * list_rows:每页数量
  1562. * type:分页类名
  1563. * @return $this[]|\think\Paginator
  1564. * @throws DbException
  1565. */
  1566. public function paginate($listRows = null, $simple = false, $config = [])
  1567. {
  1568. if (is_int($simple)) {
  1569. $total = $simple;
  1570. $simple = false;
  1571. }
  1572. $paginate = Container::get('config')->pull('paginate');
  1573. if (is_array($listRows)) {
  1574. $config = array_merge($paginate, $listRows);
  1575. $listRows = $config['list_rows'];
  1576. } else {
  1577. $config = array_merge($paginate, $config);
  1578. $listRows = $listRows ?: $config['list_rows'];
  1579. }
  1580. /** @var Paginator $class */
  1581. $class = false !== strpos($config['type'], '\\') ? $config['type'] : '\\think\\paginator\\driver\\' . ucwords($config['type']);
  1582. $page = isset($config['page']) ? (int) $config['page'] : call_user_func([
  1583. $class,
  1584. 'getCurrentPage',
  1585. ], $config['var_page']);
  1586. $page = $page < 1 ? 1 : $page;
  1587. $config['path'] = isset($config['path']) ? $config['path'] : call_user_func([$class, 'getCurrentPath']);
  1588. if (!isset($total) && !$simple) {
  1589. $options = $this->getOptions();
  1590. unset($this->options['order'], $this->options['limit'], $this->options['page'], $this->options['field']);
  1591. $bind = $this->bind;
  1592. $total = $this->count();
  1593. $results = $this->options($options)->bind($bind)->page($page, $listRows)->select();
  1594. } elseif ($simple) {
  1595. $results = $this->limit(($page - 1) * $listRows, $listRows + 1)->select();
  1596. $total = null;
  1597. } else {
  1598. $results = $this->page($page, $listRows)->select();
  1599. }
  1600. $this->removeOption('limit');
  1601. $this->removeOption('page');
  1602. return $class::make($results, $listRows, $page, $total, $simple, $config);
  1603. }
  1604. /**
  1605. * 指定当前操作的数据表
  1606. * @access public
  1607. * @param mixed $table 表名
  1608. * @return $this
  1609. */
  1610. public function table($table)
  1611. {
  1612. if (is_string($table)) {
  1613. if (strpos($table, ')')) {
  1614. // 子查询
  1615. } elseif (strpos($table, ',')) {
  1616. $tables = explode(',', $table);
  1617. $table = [];
  1618. foreach ($tables as $item) {
  1619. list($item, $alias) = explode(' ', trim($item));
  1620. if ($alias) {
  1621. $this->alias([$item => $alias]);
  1622. $table[$item] = $alias;
  1623. } else {
  1624. $table[] = $item;
  1625. }
  1626. }
  1627. } elseif (strpos($table, ' ')) {
  1628. list($table, $alias) = explode(' ', $table);
  1629. $table = [$table => $alias];
  1630. $this->alias($table);
  1631. }
  1632. } else {
  1633. $tables = $table;
  1634. $table = [];
  1635. foreach ($tables as $key => $val) {
  1636. if (is_numeric($key)) {
  1637. $table[] = $val;
  1638. } else {
  1639. $this->alias([$key => $val]);
  1640. $table[$key] = $val;
  1641. }
  1642. }
  1643. }
  1644. $this->options['table'] = $table;
  1645. return $this;
  1646. }
  1647. /**
  1648. * USING支持 用于多表删除
  1649. * @access public
  1650. * @param mixed $using
  1651. * @return $this
  1652. */
  1653. public function using($using)
  1654. {
  1655. $this->options['using'] = $using;
  1656. return $this;
  1657. }
  1658. /**
  1659. * 指定排序 order('id','desc') 或者 order(['id'=>'desc','create_time'=>'desc'])
  1660. * @access public
  1661. * @param string|array $field 排序字段
  1662. * @param string $order 排序
  1663. * @return $this
  1664. */
  1665. public function order($field, $order = null)
  1666. {
  1667. if (empty($field)) {
  1668. return $this;
  1669. } elseif ($field instanceof Expression) {
  1670. $this->options['order'][] = $field;
  1671. return $this;
  1672. }
  1673. if (is_string($field)) {
  1674. if (!empty($this->options['via'])) {
  1675. $field = $this->options['via'] . '.' . $field;
  1676. }
  1677. if (strpos($field, ',')) {
  1678. $field = array_map('trim', explode(',', $field));
  1679. } else {
  1680. $field = empty($order) ? $field : [$field => $order];
  1681. }
  1682. } elseif (!empty($this->options['via'])) {
  1683. foreach ($field as $key => $val) {
  1684. if (is_numeric($key)) {
  1685. $field[$key] = $this->options['via'] . '.' . $val;
  1686. } else {
  1687. $field[$this->options['via'] . '.' . $key] = $val;
  1688. unset($field[$key]);
  1689. }
  1690. }
  1691. }
  1692. if (!isset($this->options['order'])) {
  1693. $this->options['order'] = [];
  1694. }
  1695. if (is_array($field)) {
  1696. $this->options['order'] = array_merge($this->options['order'], $field);
  1697. } else {
  1698. $this->options['order'][] = $field;
  1699. }
  1700. return $this;
  1701. }
  1702. /**
  1703. * 表达式方式指定Field排序
  1704. * @access public
  1705. * @param string $field 排序字段
  1706. * @param array $bind 参数绑定
  1707. * @return $this
  1708. */
  1709. public function orderRaw($field, $bind = [])
  1710. {
  1711. if ($bind) {
  1712. $this->bindParams($field, $bind);
  1713. }
  1714. $this->options['order'][] = $this->raw($field);
  1715. return $this;
  1716. }
  1717. /**
  1718. * 指定Field排序 order('id',[1,2,3],'desc')
  1719. * @access public
  1720. * @param string|array $field 排序字段
  1721. * @param array $values 排序值
  1722. * @param string $order
  1723. * @return $this
  1724. */
  1725. public function orderField($field, array $values, $order = '')
  1726. {
  1727. if (!empty($values)) {
  1728. $values['sort'] = $order;
  1729. $this->options['order'][$field] = $values;
  1730. }
  1731. return $this;
  1732. }
  1733. /**
  1734. * 随机排序
  1735. * @access public
  1736. * @return $this
  1737. */
  1738. public function orderRand()
  1739. {
  1740. $this->options['order'][] = '[rand]';
  1741. return $this;
  1742. }
  1743. /**
  1744. * 查询缓存
  1745. * @access public
  1746. * @param mixed $key 缓存key
  1747. * @param integer|\DateTime $expire 缓存有效期
  1748. * @param string $tag 缓存标签
  1749. * @return $this
  1750. */
  1751. public function cache($key = true, $expire = null, $tag = null)
  1752. {
  1753. // 增加快捷调用方式 cache(10) 等同于 cache(true, 10)
  1754. if ($key instanceof \DateTime || (is_numeric($key) && is_null($expire))) {
  1755. $expire = $key;
  1756. $key = true;
  1757. }
  1758. if (false !== $key) {
  1759. $this->options['cache'] = ['key' => $key, 'expire' => $expire, 'tag' => $tag];
  1760. }
  1761. return $this;
  1762. }
  1763. /**
  1764. * 指定group查询
  1765. * @access public
  1766. * @param string|array $group GROUP
  1767. * @return $this
  1768. */
  1769. public function group($group)
  1770. {
  1771. $this->options['group'] = $group;
  1772. return $this;
  1773. }
  1774. /**
  1775. * 指定having查询
  1776. * @access public
  1777. * @param string $having having
  1778. * @return $this
  1779. */
  1780. public function having($having)
  1781. {
  1782. $this->options['having'] = $having;
  1783. return $this;
  1784. }
  1785. /**
  1786. * 指定查询lock
  1787. * @access public
  1788. * @param bool|string $lock 是否lock
  1789. * @return $this
  1790. */
  1791. public function lock($lock = false)
  1792. {
  1793. $this->options['lock'] = $lock;
  1794. $this->options['master'] = true;
  1795. return $this;
  1796. }
  1797. /**
  1798. * 指定distinct查询
  1799. * @access public
  1800. * @param string $distinct 是否唯一
  1801. * @return $this
  1802. */
  1803. public function distinct($distinct)
  1804. {
  1805. $this->options['distinct'] = $distinct;
  1806. return $this;
  1807. }
  1808. /**
  1809. * 指定数据表别名
  1810. * @access public
  1811. * @param array|string $alias 数据表别名
  1812. * @return $this
  1813. */
  1814. public function alias($alias)
  1815. {
  1816. if (is_array($alias)) {
  1817. foreach ($alias as $key => $val) {
  1818. if (false !== strpos($key, '__')) {
  1819. $table = $this->connection->parseSqlTable($key);
  1820. } else {
  1821. $table = $key;
  1822. }
  1823. $this->options['alias'][$table] = $val;
  1824. }
  1825. } else {
  1826. if (isset($this->options['table'])) {
  1827. $table = is_array($this->options['table']) ? key($this->options['table']) : $this->options['table'];
  1828. if (false !== strpos($table, '__')) {
  1829. $table = $this->connection->parseSqlTable($table);
  1830. }
  1831. } else {
  1832. $table = $this->getTable();
  1833. }
  1834. $this->options['alias'][$table] = $alias;
  1835. }
  1836. return $this;
  1837. }
  1838. /**
  1839. * 指定强制索引
  1840. * @access public
  1841. * @param string $force 索引名称
  1842. * @return $this
  1843. */
  1844. public function force($force)
  1845. {
  1846. $this->options['force'] = $force;
  1847. return $this;
  1848. }
  1849. /**
  1850. * 查询注释
  1851. * @access public
  1852. * @param string $comment 注释
  1853. * @return $this
  1854. */
  1855. public function comment($comment)
  1856. {
  1857. $this->options['comment'] = $comment;
  1858. return $this;
  1859. }
  1860. /**
  1861. * 获取执行的SQL语句
  1862. * @access public
  1863. * @param boolean $fetch 是否返回sql
  1864. * @return $this
  1865. */
  1866. public function fetchSql($fetch = true)
  1867. {
  1868. $this->options['fetch_sql'] = $fetch;
  1869. return $this;
  1870. }
  1871. /**
  1872. * 不主动获取数据集
  1873. * @access public
  1874. * @param bool $pdo 是否返回 PDOStatement 对象
  1875. * @return $this
  1876. */
  1877. public function fetchPdo($pdo = true)
  1878. {
  1879. $this->options['fetch_pdo'] = $pdo;
  1880. return $this;
  1881. }
  1882. /**
  1883. * 设置是否返回数据集对象(支持设置数据集对象类名)
  1884. * @access public
  1885. * @param bool|string $collection 是否返回数据集对象
  1886. * @return $this
  1887. */
  1888. public function fetchCollection($collection = true)
  1889. {
  1890. $this->options['collection'] = $collection;
  1891. return $this;
  1892. }
  1893. /**
  1894. * 设置从主服务器读取数据
  1895. * @access public
  1896. * @return $this
  1897. */
  1898. public function master()
  1899. {
  1900. $this->options['master'] = true;
  1901. return $this;
  1902. }
  1903. /**
  1904. * 设置是否严格检查字段名
  1905. * @access public
  1906. * @param bool $strict 是否严格检查字段
  1907. * @return $this
  1908. */
  1909. public function strict($strict = true)
  1910. {
  1911. $this->options['strict'] = $strict;
  1912. return $this;
  1913. }
  1914. /**
  1915. * 设置查询数据不存在是否抛出异常
  1916. * @access public
  1917. * @param bool $fail 数据不存在是否抛出异常
  1918. * @return $this
  1919. */
  1920. public function failException($fail = true)
  1921. {
  1922. $this->options['fail'] = $fail;
  1923. return $this;
  1924. }
  1925. /**
  1926. * 设置自增序列名
  1927. * @access public
  1928. * @param string $sequence 自增序列名
  1929. * @return $this
  1930. */
  1931. public function sequence($sequence = null)
  1932. {
  1933. $this->options['sequence'] = $sequence;
  1934. return $this;
  1935. }
  1936. /**
  1937. * 设置需要隐藏的输出属性
  1938. * @access public
  1939. * @param mixed $hidden 需要隐藏的字段名
  1940. * @return $this
  1941. */
  1942. public function hidden($hidden)
  1943. {
  1944. if ($this->model) {
  1945. $this->options['hidden'] = $hidden;
  1946. return $this;
  1947. }
  1948. return $this->field($hidden, true);
  1949. }
  1950. /**
  1951. * 设置需要输出的属性
  1952. * @access public
  1953. * @param array $visible 需要输出的属性
  1954. * @return $this
  1955. */
  1956. public function visible(array $visible)
  1957. {
  1958. $this->options['visible'] = $visible;
  1959. return $this;
  1960. }
  1961. /**
  1962. * 设置需要附加的输出属性
  1963. * @access public
  1964. * @param array $append 属性列表
  1965. * @return $this
  1966. */
  1967. public function append(array $append = [])
  1968. {
  1969. $this->options['append'] = $append;
  1970. return $this;
  1971. }
  1972. /**
  1973. * 设置数据字段获取器
  1974. * @access public
  1975. * @param string|array $name 字段名
  1976. * @param callable $callback 闭包获取器
  1977. * @return $this
  1978. */
  1979. public function withAttr($name, $callback = null)
  1980. {
  1981. if (is_array($name)) {
  1982. $this->options['with_attr'] = $name;
  1983. } else {
  1984. $this->options['with_attr'][$name] = $callback;
  1985. }
  1986. return $this;
  1987. }
  1988. /**
  1989. * 设置JSON字段信息
  1990. * @access public
  1991. * @param array $json JSON字段
  1992. * @param bool $assoc 是否取出数组
  1993. * @return $this
  1994. */
  1995. public function json(array $json = [], $assoc = false)
  1996. {
  1997. $this->options['json'] = $json;
  1998. $this->options['json_assoc'] = $assoc;
  1999. return $this;
  2000. }
  2001. /**
  2002. * 设置字段类型信息
  2003. * @access public
  2004. * @param array $type 字段类型信息
  2005. * @return $this
  2006. */
  2007. public function setJsonFieldType(array $type)
  2008. {
  2009. $this->options['field_type'] = $type;
  2010. return $this;
  2011. }
  2012. /**
  2013. * 获取字段类型信息
  2014. * @access public
  2015. * @param string $field 字段名
  2016. * @return string|null
  2017. */
  2018. public function getJsonFieldType($field)
  2019. {
  2020. return isset($this->options['field_type'][$field]) ? $this->options['field_type'][$field] : null;
  2021. }
  2022. /**
  2023. * 是否允许返回空数据(或空模型)
  2024. * @access public
  2025. * @param bool $allowEmpty 是否允许为空
  2026. * @return $this
  2027. */
  2028. public function allowEmpty($allowEmpty = true)
  2029. {
  2030. $this->options['allow_empty'] = $allowEmpty;
  2031. return $this;
  2032. }
  2033. /**
  2034. * 添加查询范围
  2035. * @access public
  2036. * @param array|string|\Closure $scope 查询范围定义
  2037. * @param array $args 参数
  2038. * @return $this
  2039. */
  2040. public function scope($scope, ...$args)
  2041. {
  2042. // 查询范围的第一个参数始终是当前查询对象
  2043. array_unshift($args, $this);
  2044. if ($scope instanceof \Closure) {
  2045. call_user_func_array($scope, $args);
  2046. return $this;
  2047. }
  2048. if (is_string($scope)) {
  2049. $scope = explode(',', $scope);
  2050. }
  2051. if ($this->model) {
  2052. // 检查模型类的查询范围方法
  2053. foreach ($scope as $name) {
  2054. $method = 'scope' . trim($name);
  2055. if (method_exists($this->model, $method)) {
  2056. call_user_func_array([$this->model, $method], $args);
  2057. }
  2058. }
  2059. }
  2060. return $this;
  2061. }
  2062. /**
  2063. * 使用搜索器条件搜索字段
  2064. * @access public
  2065. * @param array $fields 搜索字段
  2066. * @param array $data 搜索数据
  2067. * @param string $prefix 字段前缀标识
  2068. * @return $this
  2069. */
  2070. public function withSearch(array $fields, array $data = [], $prefix = '')
  2071. {
  2072. foreach ($fields as $key => $field) {
  2073. if ($field instanceof \Closure) {
  2074. $field($this, isset($data[$key]) ? $data[$key] : null, $data, $prefix);
  2075. } elseif ($this->model) {
  2076. // 检测搜索器
  2077. $fieldName = is_numeric($key) ? $field : $key;
  2078. $method = 'search' . Loader::parseName($fieldName, 1) . 'Attr';
  2079. if (method_exists($this->model, $method)) {
  2080. $this->model->$method($this, isset($data[$field]) ? $data[$field] : null, $data, $prefix);
  2081. }
  2082. }
  2083. }
  2084. return $this;
  2085. }
  2086. /**
  2087. * 指定数据表主键
  2088. * @access public
  2089. * @param string $pk 主键
  2090. * @return $this
  2091. */
  2092. public function pk($pk)
  2093. {
  2094. $this->pk = $pk;
  2095. return $this;
  2096. }
  2097. /**
  2098. * 查询日期或者时间
  2099. * @access public
  2100. * @param string $name 时间表达式
  2101. * @param string|array $rule 时间范围
  2102. * @return $this
  2103. */
  2104. public function timeRule($name, $rule)
  2105. {
  2106. $this->timeRule[$name] = $rule;
  2107. return $this;
  2108. }
  2109. /**
  2110. * 查询日期或者时间
  2111. * @access public
  2112. * @param string $field 日期字段名
  2113. * @param string|array $op 比较运算符或者表达式
  2114. * @param string|array $range 比较范围
  2115. * @param string $logic AND OR
  2116. * @return $this
  2117. */
  2118. public function whereTime($field, $op, $range = null, $logic = 'AND')
  2119. {
  2120. if (is_null($range)) {
  2121. if (is_array($op)) {
  2122. $range = $op;
  2123. } else {
  2124. if (isset($this->timeExp[strtolower($op)])) {
  2125. $op = $this->timeExp[strtolower($op)];
  2126. }
  2127. if (isset($this->timeRule[strtolower($op)])) {
  2128. $range = $this->timeRule[strtolower($op)];
  2129. } else {
  2130. $range = $op;
  2131. }
  2132. }
  2133. $op = is_array($range) ? 'between' : '>=';
  2134. }
  2135. return $this->parseWhereExp($logic, $field, strtolower($op) . ' time', $range, [], true);
  2136. }
  2137. /**
  2138. * 查询当前时间在两个时间字段范围
  2139. * @access public
  2140. * @param string $startField 开始时间字段
  2141. * @param string $endField 结束时间字段
  2142. * @return $this
  2143. */
  2144. public function whereBetweenTimeField($startField, $endField)
  2145. {
  2146. return $this->whereTime($startField, '<=', time())
  2147. ->whereTime($endField, '>=', time());
  2148. }
  2149. /**
  2150. * 查询当前时间不在两个时间字段范围
  2151. * @access public
  2152. * @param string $startField 开始时间字段
  2153. * @param string $endField 结束时间字段
  2154. * @return $this
  2155. */
  2156. public function whereNotBetweenTimeField($startField, $endField)
  2157. {
  2158. return $this->whereTime($startField, '>', time())
  2159. ->whereTime($endField, '<', time(), 'OR');
  2160. }
  2161. /**
  2162. * 查询日期或者时间范围
  2163. * @access public
  2164. * @param string $field 日期字段名
  2165. * @param string $startTime 开始时间
  2166. * @param string $endTime 结束时间
  2167. * @param string $logic AND OR
  2168. * @return $this
  2169. */
  2170. public function whereBetweenTime($field, $startTime, $endTime = null, $logic = 'AND')
  2171. {
  2172. if (is_null($endTime)) {
  2173. $time = is_string($startTime) ? strtotime($startTime) : $startTime;
  2174. $endTime = strtotime('+1 day', $time);
  2175. }
  2176. return $this->parseWhereExp($logic, $field, 'between time', [$startTime, $endTime], [], true);
  2177. }
  2178. /**
  2179. * 获取当前数据表的主键
  2180. * @access public
  2181. * @param string|array $options 数据表名或者查询参数
  2182. * @return string|array
  2183. */
  2184. public function getPk($options = '')
  2185. {
  2186. if (!empty($this->pk)) {
  2187. $pk = $this->pk;
  2188. } else {
  2189. $pk = $this->connection->getPk(is_array($options) && isset($options['table']) ? $options['table'] : $this->getTable());
  2190. }
  2191. return $pk;
  2192. }
  2193. /**
  2194. * 参数绑定
  2195. * @access public
  2196. * @param mixed $value 绑定变量值
  2197. * @param integer $type 绑定类型
  2198. * @param string $name 绑定名称
  2199. * @return $this|string
  2200. */
  2201. public function bind($value, $type = PDO::PARAM_STR, $name = null)
  2202. {
  2203. if (is_array($value)) {
  2204. $this->bind = array_merge($this->bind, $value);
  2205. } else {
  2206. $name = $name ?: 'ThinkBind_' . (count($this->bind) + 1) . '_' . mt_rand() . '_';
  2207. $this->bind[$name] = [$value, $type];
  2208. return $name;
  2209. }
  2210. return $this;
  2211. }
  2212. /**
  2213. * 检测参数是否已经绑定
  2214. * @access public
  2215. * @param string $key 参数名
  2216. * @return bool
  2217. */
  2218. public function isBind($key)
  2219. {
  2220. return isset($this->bind[$key]);
  2221. }
  2222. /**
  2223. * 查询参数赋值
  2224. * @access public
  2225. * @param string $name 参数名
  2226. * @param mixed $value 值
  2227. * @return $this
  2228. */
  2229. public function option($name, $value)
  2230. {
  2231. $this->options[$name] = $value;
  2232. return $this;
  2233. }
  2234. /**
  2235. * 查询参数赋值
  2236. * @access protected
  2237. * @param array $options 表达式参数
  2238. * @return $this
  2239. */
  2240. protected function options(array $options)
  2241. {
  2242. $this->options = $options;
  2243. return $this;
  2244. }
  2245. /**
  2246. * 获取当前的查询参数
  2247. * @access public
  2248. * @param string $name 参数名
  2249. * @return mixed
  2250. */
  2251. public function getOptions($name = '')
  2252. {
  2253. if ('' === $name) {
  2254. return $this->options;
  2255. }
  2256. return isset($this->options[$name]) ? $this->options[$name] : null;
  2257. }
  2258. /**
  2259. * 设置当前的查询参数
  2260. * @access public
  2261. * @param string $option 参数名
  2262. * @param mixed $value 参数值
  2263. * @return $this
  2264. */
  2265. public function setOption($option, $value)
  2266. {
  2267. $this->options[$option] = $value;
  2268. return $this;
  2269. }
  2270. /**
  2271. * 设置关联查询JOIN预查询
  2272. * @access public
  2273. * @param string|array $with 关联方法名称
  2274. * @return $this
  2275. */
  2276. public function with($with)
  2277. {
  2278. if (empty($with)) {
  2279. return $this;
  2280. }
  2281. if (is_string($with)) {
  2282. $with = explode(',', $with);
  2283. }
  2284. $first = true;
  2285. /** @var Model $class */
  2286. $class = $this->model;
  2287. foreach ($with as $key => $relation) {
  2288. $closure = null;
  2289. if ($relation instanceof \Closure) {
  2290. // 支持闭包查询过滤关联条件
  2291. $closure = $relation;
  2292. $relation = $key;
  2293. } elseif (is_array($relation)) {
  2294. $relation = $key;
  2295. } elseif (is_string($relation) && strpos($relation, '.')) {
  2296. list($relation, $subRelation) = explode('.', $relation, 2);
  2297. }
  2298. /** @var Relation $model */
  2299. $relation = Loader::parseName($relation, 1, false);
  2300. $model = $class->$relation();
  2301. if ($model instanceof OneToOne && 0 == $model->getEagerlyType()) {
  2302. $table = $model->getTable();
  2303. $model->removeOption()
  2304. ->table($table)
  2305. ->eagerly($this, $relation, true, '', $closure, $first);
  2306. $first = false;
  2307. }
  2308. }
  2309. $this->via();
  2310. $this->options['with'] = $with;
  2311. return $this;
  2312. }
  2313. /**
  2314. * 关联预载入 JOIN方式(不支持嵌套)
  2315. * @access protected
  2316. * @param string|array $with 关联方法名
  2317. * @param string $joinType JOIN方式
  2318. * @return $this
  2319. */
  2320. public function withJoin($with, $joinType = '')
  2321. {
  2322. if (empty($with)) {
  2323. return $this;
  2324. }
  2325. if (is_string($with)) {
  2326. $with = explode(',', $with);
  2327. }
  2328. $first = true;
  2329. /** @var Model $class */
  2330. $class = $this->model;
  2331. foreach ($with as $key => $relation) {
  2332. $closure = null;
  2333. $field = true;
  2334. if ($relation instanceof \Closure) {
  2335. // 支持闭包查询过滤关联条件
  2336. $closure = $relation;
  2337. $relation = $key;
  2338. } elseif (is_array($relation)) {
  2339. $field = $relation;
  2340. $relation = $key;
  2341. } elseif (is_string($relation) && strpos($relation, '.')) {
  2342. list($relation, $subRelation) = explode('.', $relation, 2);
  2343. }
  2344. /** @var Relation $model */
  2345. $relation = Loader::parseName($relation, 1, false);
  2346. $model = $class->$relation();
  2347. if ($model instanceof OneToOne) {
  2348. $model->eagerly($this, $relation, $field, $joinType, $closure, $first);
  2349. $first = false;
  2350. } else {
  2351. // 不支持其它关联
  2352. unset($with[$key]);
  2353. }
  2354. }
  2355. $this->via();
  2356. $this->options['with_join'] = $with;
  2357. return $this;
  2358. }
  2359. /**
  2360. * 关联统计
  2361. * @access protected
  2362. * @param string|array $relation 关联方法名
  2363. * @param string $aggregate 聚合查询方法
  2364. * @param string $field 字段
  2365. * @param bool $subQuery 是否使用子查询
  2366. * @return $this
  2367. */
  2368. protected function withAggregate($relation, $aggregate = 'count', $field = '*', $subQuery = true)
  2369. {
  2370. $relations = is_string($relation) ? explode(',', $relation) : $relation;
  2371. if (!$subQuery) {
  2372. $this->options['with_count'][] = [$relations, $aggregate, $field];
  2373. } else {
  2374. if (!isset($this->options['field'])) {
  2375. $this->field('*');
  2376. }
  2377. foreach ($relations as $key => $relation) {
  2378. $closure = $aggregateField = null;
  2379. if ($relation instanceof \Closure) {
  2380. $closure = $relation;
  2381. $relation = $key;
  2382. } elseif (!is_int($key)) {
  2383. $aggregateField = $relation;
  2384. $relation = $key;
  2385. }
  2386. $relation = Loader::parseName($relation, 1, false);
  2387. $count = $this->model->$relation()->getRelationCountQuery($closure, $aggregate, $field, $aggregateField);
  2388. if (empty($aggregateField)) {
  2389. $aggregateField = Loader::parseName($relation) . '_' . $aggregate;
  2390. }
  2391. $this->field(['(' . $count . ')' => $aggregateField]);
  2392. }
  2393. }
  2394. return $this;
  2395. }
  2396. /**
  2397. * 关联统计
  2398. * @access public
  2399. * @param string|array $relation 关联方法名
  2400. * @param bool $subQuery 是否使用子查询
  2401. * @return $this
  2402. */
  2403. public function withCount($relation, $subQuery = true)
  2404. {
  2405. return $this->withAggregate($relation, 'count', '*', $subQuery);
  2406. }
  2407. /**
  2408. * 关联统计Sum
  2409. * @access public
  2410. * @param string|array $relation 关联方法名
  2411. * @param string $field 字段
  2412. * @param bool $subQuery 是否使用子查询
  2413. * @return $this
  2414. */
  2415. public function withSum($relation, $field, $subQuery = true)
  2416. {
  2417. return $this->withAggregate($relation, 'sum', $field, $subQuery);
  2418. }
  2419. /**
  2420. * 关联统计Max
  2421. * @access public
  2422. * @param string|array $relation 关联方法名
  2423. * @param string $field 字段
  2424. * @param bool $subQuery 是否使用子查询
  2425. * @return $this
  2426. */
  2427. public function withMax($relation, $field, $subQuery = true)
  2428. {
  2429. return $this->withAggregate($relation, 'max', $field, $subQuery);
  2430. }
  2431. /**
  2432. * 关联统计Min
  2433. * @access public
  2434. * @param string|array $relation 关联方法名
  2435. * @param string $field 字段
  2436. * @param bool $subQuery 是否使用子查询
  2437. * @return $this
  2438. */
  2439. public function withMin($relation, $field, $subQuery = true)
  2440. {
  2441. return $this->withAggregate($relation, 'min', $field, $subQuery);
  2442. }
  2443. /**
  2444. * 关联统计Avg
  2445. * @access public
  2446. * @param string|array $relation 关联方法名
  2447. * @param string $field 字段
  2448. * @param bool $subQuery 是否使用子查询
  2449. * @return $this
  2450. */
  2451. public function withAvg($relation, $field, $subQuery = true)
  2452. {
  2453. return $this->withAggregate($relation, 'avg', $field, $subQuery);
  2454. }
  2455. /**
  2456. * 关联预加载中 获取关联指定字段值
  2457. * example:
  2458. * Model::with(['relation' => function($query){
  2459. * $query->withField("id,name");
  2460. * }])
  2461. *
  2462. * @access public
  2463. * @param string | array $field 指定获取的字段
  2464. * @return $this
  2465. */
  2466. public function withField($field)
  2467. {
  2468. $this->options['with_field'] = $field;
  2469. return $this;
  2470. }
  2471. /**
  2472. * 设置当前字段添加的表别名
  2473. * @access public
  2474. * @param string $via
  2475. * @return $this
  2476. */
  2477. public function via($via = '')
  2478. {
  2479. $this->options['via'] = $via;
  2480. return $this;
  2481. }
  2482. /**
  2483. * 设置关联查询
  2484. * @access public
  2485. * @param string|array $relation 关联名称
  2486. * @return $this
  2487. */
  2488. public function relation($relation)
  2489. {
  2490. if (empty($relation)) {
  2491. return $this;
  2492. }
  2493. if (is_string($relation)) {
  2494. $relation = explode(',', $relation);
  2495. }
  2496. if (isset($this->options['relation'])) {
  2497. $this->options['relation'] = array_merge($this->options['relation'], $relation);
  2498. } else {
  2499. $this->options['relation'] = $relation;
  2500. }
  2501. return $this;
  2502. }
  2503. /**
  2504. * 插入记录
  2505. * @access public
  2506. * @param array $data 数据
  2507. * @param boolean $replace 是否replace
  2508. * @param boolean $getLastInsID 返回自增主键
  2509. * @param string $sequence 自增序列名
  2510. * @return integer|string
  2511. */
  2512. public function insert(array $data = [], $replace = false, $getLastInsID = false, $sequence = null)
  2513. {
  2514. $this->parseOptions();
  2515. $this->options['data'] = array_merge($this->options['data'], $data);
  2516. return $this->connection->insert($this, $replace, $getLastInsID, $sequence);
  2517. }
  2518. /**
  2519. * 插入记录并获取自增ID
  2520. * @access public
  2521. * @param array $data 数据
  2522. * @param boolean $replace 是否replace
  2523. * @param string $sequence 自增序列名
  2524. * @return integer|string
  2525. */
  2526. public function insertGetId(array $data, $replace = false, $sequence = null)
  2527. {
  2528. return $this->insert($data, $replace, true, $sequence);
  2529. }
  2530. /**
  2531. * 批量插入记录
  2532. * @access public
  2533. * @param array $dataSet 数据集
  2534. * @param boolean $replace 是否replace
  2535. * @param integer $limit 每次写入数据限制
  2536. * @return integer|string
  2537. */
  2538. public function insertAll(array $dataSet = [], $replace = false, $limit = null)
  2539. {
  2540. $this->parseOptions();
  2541. if (empty($dataSet)) {
  2542. $dataSet = $this->options['data'];
  2543. }
  2544. if (empty($limit) && !empty($this->options['limit'])) {
  2545. $limit = $this->options['limit'];
  2546. }
  2547. return $this->connection->insertAll($this, $dataSet, $replace, $limit);
  2548. }
  2549. /**
  2550. * 通过Select方式插入记录
  2551. * @access public
  2552. * @param string $fields 要插入的数据表字段名
  2553. * @param string $table 要插入的数据表名
  2554. * @return integer|string
  2555. * @throws PDOException
  2556. */
  2557. public function selectInsert($fields, $table)
  2558. {
  2559. $this->parseOptions();
  2560. return $this->connection->selectInsert($this, $fields, $table);
  2561. }
  2562. /**
  2563. * 更新记录
  2564. * @access public
  2565. * @param mixed $data 数据
  2566. * @return integer|string
  2567. * @throws Exception
  2568. * @throws PDOException
  2569. */
  2570. public function update(array $data = [])
  2571. {
  2572. $this->parseOptions();
  2573. $this->options['data'] = array_merge($this->options['data'], $data);
  2574. return $this->connection->update($this);
  2575. }
  2576. /**
  2577. * 删除记录
  2578. * @access public
  2579. * @param mixed $data 表达式 true 表示强制删除
  2580. * @return int
  2581. * @throws Exception
  2582. * @throws PDOException
  2583. */
  2584. public function delete($data = null)
  2585. {
  2586. $this->parseOptions();
  2587. if (!is_null($data) && true !== $data) {
  2588. // AR模式分析主键条件
  2589. $this->parsePkWhere($data);
  2590. }
  2591. if (!empty($this->options['soft_delete'])) {
  2592. // 软删除
  2593. list($field, $condition) = $this->options['soft_delete'];
  2594. if ($condition) {
  2595. unset($this->options['soft_delete']);
  2596. $this->options['data'] = [$field => $condition];
  2597. return $this->connection->update($this);
  2598. }
  2599. }
  2600. $this->options['data'] = $data;
  2601. return $this->connection->delete($this);
  2602. }
  2603. /**
  2604. * 执行查询但只返回PDOStatement对象
  2605. * @access public
  2606. * @return \PDOStatement|string
  2607. */
  2608. public function getPdo()
  2609. {
  2610. $this->parseOptions();
  2611. return $this->connection->pdo($this);
  2612. }
  2613. /**
  2614. * 使用游标查找记录
  2615. * @access public
  2616. * @param array|string|Query|\Closure $data
  2617. * @return \Generator
  2618. */
  2619. public function cursor($data = null)
  2620. {
  2621. if ($data instanceof \Closure) {
  2622. $data($this);
  2623. $data = null;
  2624. }
  2625. $this->parseOptions();
  2626. if (!is_null($data)) {
  2627. // 主键条件分析
  2628. $this->parsePkWhere($data);
  2629. }
  2630. $this->options['data'] = $data;
  2631. $connection = clone $this->connection;
  2632. return $connection->cursor($this);
  2633. }
  2634. /**
  2635. * 查找记录
  2636. * @access public
  2637. * @param array|string|Query|\Closure $data
  2638. * @return Collection|array|\PDOStatement|string
  2639. * @throws DbException
  2640. * @throws ModelNotFoundException
  2641. * @throws DataNotFoundException
  2642. */
  2643. public function select($data = null)
  2644. {
  2645. if ($data instanceof Query) {
  2646. return $data->select();
  2647. } elseif ($data instanceof \Closure) {
  2648. $data($this);
  2649. $data = null;
  2650. }
  2651. $this->parseOptions();
  2652. if (false === $data) {
  2653. // 用于子查询 不查询只返回SQL
  2654. $this->options['fetch_sql'] = true;
  2655. } elseif (!is_null($data)) {
  2656. // 主键条件分析
  2657. $this->parsePkWhere($data);
  2658. }
  2659. $this->options['data'] = $data;
  2660. $resultSet = $this->connection->select($this);
  2661. if ($this->options['fetch_sql']) {
  2662. return $resultSet;
  2663. }
  2664. // 返回结果处理
  2665. if (!empty($this->options['fail']) && count($resultSet) == 0) {
  2666. $this->throwNotFound($this->options);
  2667. }
  2668. // 数据列表读取后的处理
  2669. if (!empty($this->model)) {
  2670. // 生成模型对象
  2671. $resultSet = $this->resultSetToModelCollection($resultSet);
  2672. } else {
  2673. $this->resultSet($resultSet);
  2674. }
  2675. return $resultSet;
  2676. }
  2677. /**
  2678. * 查询数据转换为模型数据集对象
  2679. * @access protected
  2680. * @param array $resultSet 数据集
  2681. * @return ModelCollection
  2682. */
  2683. protected function resultSetToModelCollection(array $resultSet)
  2684. {
  2685. if (!empty($this->options['collection']) && is_string($this->options['collection'])) {
  2686. $collection = $this->options['collection'];
  2687. }
  2688. if (empty($resultSet)) {
  2689. return $this->model->toCollection([], isset($collection) ? $collection : null);
  2690. }
  2691. // 检查动态获取器
  2692. if (!empty($this->options['with_attr'])) {
  2693. foreach ($this->options['with_attr'] as $name => $val) {
  2694. if (strpos($name, '.')) {
  2695. list($relation, $field) = explode('.', $name);
  2696. $withRelationAttr[$relation][$field] = $val;
  2697. unset($this->options['with_attr'][$name]);
  2698. }
  2699. }
  2700. }
  2701. $withRelationAttr = isset($withRelationAttr) ? $withRelationAttr : [];
  2702. foreach ($resultSet as $key => &$result) {
  2703. // 数据转换为模型对象
  2704. $this->resultToModel($result, $this->options, true, $withRelationAttr);
  2705. }
  2706. if (!empty($this->options['with'])) {
  2707. // 预载入
  2708. $result->eagerlyResultSet($resultSet, $this->options['with'], $withRelationAttr);
  2709. }
  2710. if (!empty($this->options['with_join'])) {
  2711. // JOIN预载入
  2712. $result->eagerlyResultSet($resultSet, $this->options['with_join'], $withRelationAttr, true);
  2713. }
  2714. // 模型数据集转换
  2715. return $result->toCollection($resultSet, isset($collection) ? $collection : null);
  2716. }
  2717. /**
  2718. * 处理数据集
  2719. * @access public
  2720. * @param array $resultSet
  2721. * @return void
  2722. */
  2723. protected function resultSet(&$resultSet)
  2724. {
  2725. if (!empty($this->options['json'])) {
  2726. foreach ($resultSet as &$result) {
  2727. $this->jsonResult($result, $this->options['json'], true);
  2728. }
  2729. }
  2730. if (!empty($this->options['with_attr'])) {
  2731. foreach ($resultSet as &$result) {
  2732. $this->getResultAttr($result, $this->options['with_attr']);
  2733. }
  2734. }
  2735. if (!empty($this->options['collection']) || 'collection' == $this->connection->getConfig('resultset_type')) {
  2736. // 返回Collection对象
  2737. $resultSet = new Collection($resultSet);
  2738. }
  2739. }
  2740. /**
  2741. * 查找单条记录
  2742. * @access public
  2743. * @param array|string|Query|\Closure $data
  2744. * @return array|null|\PDOStatement|string|Model
  2745. * @throws DbException
  2746. * @throws ModelNotFoundException
  2747. * @throws DataNotFoundException
  2748. */
  2749. public function find($data = null)
  2750. {
  2751. if ($data instanceof Query) {
  2752. return $data->find();
  2753. } elseif ($data instanceof \Closure) {
  2754. $data($this);
  2755. $data = null;
  2756. }
  2757. $this->parseOptions();
  2758. if (!is_null($data)) {
  2759. // AR模式分析主键条件
  2760. $this->parsePkWhere($data);
  2761. }
  2762. $this->options['data'] = $data;
  2763. $result = $this->connection->find($this);
  2764. if ($this->options['fetch_sql']) {
  2765. return $result;
  2766. }
  2767. // 数据处理
  2768. if (empty($result)) {
  2769. return $this->resultToEmpty();
  2770. }
  2771. if (!empty($this->model)) {
  2772. // 返回模型对象
  2773. $this->resultToModel($result, $this->options);
  2774. } else {
  2775. $this->result($result);
  2776. }
  2777. return $result;
  2778. }
  2779. /**
  2780. * 处理空数据
  2781. * @access protected
  2782. * @return array|Model|null
  2783. * @throws DbException
  2784. * @throws ModelNotFoundException
  2785. * @throws DataNotFoundException
  2786. */
  2787. protected function resultToEmpty()
  2788. {
  2789. if (!empty($this->options['allow_empty'])) {
  2790. return !empty($this->model) ? $this->model->newInstance([], $this->getModelUpdateCondition($this->options)) : [];
  2791. } elseif (!empty($this->options['fail'])) {
  2792. $this->throwNotFound($this->options);
  2793. }
  2794. }
  2795. /**
  2796. * 查找单条记录
  2797. * @access public
  2798. * @param mixed $data 主键值或者查询条件(闭包)
  2799. * @param mixed $with 关联预查询
  2800. * @param bool $cache 是否缓存
  2801. * @param bool $failException 是否抛出异常
  2802. * @return static|null
  2803. * @throws exception\DbException
  2804. */
  2805. public function get($data, $with = [], $cache = false, $failException = false)
  2806. {
  2807. if (is_null($data)) {
  2808. return;
  2809. }
  2810. if (true === $with || is_int($with)) {
  2811. $cache = $with;
  2812. $with = [];
  2813. }
  2814. return $this->parseQuery($data, $with, $cache)
  2815. ->failException($failException)
  2816. ->find($data);
  2817. }
  2818. /**
  2819. * 查找单条记录 如果不存在直接抛出异常
  2820. * @access public
  2821. * @param mixed $data 主键值或者查询条件(闭包)
  2822. * @param mixed $with 关联预查询
  2823. * @param bool $cache 是否缓存
  2824. * @return static|null
  2825. * @throws exception\DbException
  2826. */
  2827. public function getOrFail($data, $with = [], $cache = false)
  2828. {
  2829. return $this->get($data, $with, $cache, true);
  2830. }
  2831. /**
  2832. * 查找所有记录
  2833. * @access public
  2834. * @param mixed $data 主键列表或者查询条件(闭包)
  2835. * @param array|string $with 关联预查询
  2836. * @param bool $cache 是否缓存
  2837. * @return static[]|false
  2838. * @throws exception\DbException
  2839. */
  2840. public function all($data = null, $with = [], $cache = false)
  2841. {
  2842. if (true === $with || is_int($with)) {
  2843. $cache = $with;
  2844. $with = [];
  2845. }
  2846. return $this->parseQuery($data, $with, $cache)->select($data);
  2847. }
  2848. /**
  2849. * 分析查询表达式
  2850. * @access public
  2851. * @param mixed $data 主键列表或者查询条件(闭包)
  2852. * @param string $with 关联预查询
  2853. * @param bool $cache 是否缓存
  2854. * @return Query
  2855. */
  2856. protected function parseQuery(&$data, $with, $cache)
  2857. {
  2858. $result = $this->with($with)->cache($cache);
  2859. if ((is_array($data) && key($data) !== 0) || $data instanceof Where) {
  2860. $result = $result->where($data);
  2861. $data = null;
  2862. } elseif ($data instanceof \Closure) {
  2863. $data($result);
  2864. $data = null;
  2865. } elseif ($data instanceof Query) {
  2866. $result = $data->with($with)->cache($cache);
  2867. $data = null;
  2868. }
  2869. return $result;
  2870. }
  2871. /**
  2872. * 处理数据
  2873. * @access protected
  2874. * @param array $result 查询数据
  2875. * @return void
  2876. */
  2877. protected function result(&$result)
  2878. {
  2879. if (!empty($this->options['json'])) {
  2880. $this->jsonResult($result, $this->options['json'], true);
  2881. }
  2882. if (!empty($this->options['with_attr'])) {
  2883. $this->getResultAttr($result, $this->options['with_attr']);
  2884. }
  2885. }
  2886. /**
  2887. * 使用获取器处理数据
  2888. * @access protected
  2889. * @param array $result 查询数据
  2890. * @param array $withAttr 字段获取器
  2891. * @return void
  2892. */
  2893. protected function getResultAttr(&$result, $withAttr = [])
  2894. {
  2895. foreach ($withAttr as $name => $closure) {
  2896. $name = Loader::parseName($name);
  2897. if (strpos($name, '.')) {
  2898. // 支持JSON字段 获取器定义
  2899. list($key, $field) = explode('.', $name);
  2900. if (isset($result[$key])) {
  2901. $result[$key][$field] = $closure(isset($result[$key][$field]) ? $result[$key][$field] : null, $result[$key]);
  2902. }
  2903. } else {
  2904. $result[$name] = $closure(isset($result[$name]) ? $result[$name] : null, $result);
  2905. }
  2906. }
  2907. }
  2908. /**
  2909. * JSON字段数据转换
  2910. * @access protected
  2911. * @param array $result 查询数据
  2912. * @param array $json JSON字段
  2913. * @param bool $assoc 是否转换为数组
  2914. * @param array $withRelationAttr 关联获取器
  2915. * @return void
  2916. */
  2917. protected function jsonResult(&$result, $json = [], $assoc = false, $withRelationAttr = [])
  2918. {
  2919. foreach ($json as $name) {
  2920. if (isset($result[$name])) {
  2921. $result[$name] = json_decode($result[$name], $assoc);
  2922. if (isset($withRelationAttr[$name])) {
  2923. foreach ($withRelationAttr[$name] as $key => $closure) {
  2924. $data = get_object_vars($result[$name]);
  2925. $result[$name]->$key = $closure(isset($result[$name]->$key) ? $result[$name]->$key : null, $data);
  2926. }
  2927. }
  2928. }
  2929. }
  2930. }
  2931. /**
  2932. * 查询数据转换为模型对象
  2933. * @access protected
  2934. * @param array $result 查询数据
  2935. * @param array $options 查询参数
  2936. * @param bool $resultSet 是否为数据集查询
  2937. * @param array $withRelationAttr 关联字段获取器
  2938. * @return void
  2939. */
  2940. protected function resultToModel(&$result, $options = [], $resultSet = false, $withRelationAttr = [])
  2941. {
  2942. // 动态获取器
  2943. if (!empty($options['with_attr']) && empty($withRelationAttr)) {
  2944. foreach ($options['with_attr'] as $name => $val) {
  2945. if (strpos($name, '.')) {
  2946. list($relation, $field) = explode('.', $name);
  2947. $withRelationAttr[$relation][$field] = $val;
  2948. unset($options['with_attr'][$name]);
  2949. }
  2950. }
  2951. }
  2952. // JSON 数据处理
  2953. if (!empty($options['json'])) {
  2954. $this->jsonResult($result, $options['json'], $options['json_assoc'], $withRelationAttr);
  2955. }
  2956. $result = $this->model->newInstance($result, $resultSet ? null : $this->getModelUpdateCondition($options));
  2957. // 动态获取器
  2958. if (!empty($options['with_attr'])) {
  2959. $result->withAttribute($options['with_attr']);
  2960. }
  2961. // 输出属性控制
  2962. if (!empty($options['visible'])) {
  2963. $result->visible($options['visible'], true);
  2964. } elseif (!empty($options['hidden'])) {
  2965. $result->hidden($options['hidden'], true);
  2966. }
  2967. if (!empty($options['append'])) {
  2968. $result->append($options['append'], true);
  2969. }
  2970. // 关联查询
  2971. if (!empty($options['relation'])) {
  2972. $result->relationQuery($options['relation'], $withRelationAttr);
  2973. }
  2974. // 预载入查询
  2975. if (!$resultSet && !empty($options['with'])) {
  2976. $result->eagerlyResult($result, $options['with'], $withRelationAttr);
  2977. }
  2978. // JOIN预载入查询
  2979. if (!$resultSet && !empty($options['with_join'])) {
  2980. $result->eagerlyResult($result, $options['with_join'], $withRelationAttr, true);
  2981. }
  2982. // 关联统计
  2983. if (!empty($options['with_count'])) {
  2984. foreach ($options['with_count'] as $val) {
  2985. $result->relationCount($result, $val[0], $val[1], $val[2]);
  2986. }
  2987. }
  2988. }
  2989. /**
  2990. * 获取模型的更新条件
  2991. * @access protected
  2992. * @param array $options 查询参数
  2993. */
  2994. protected function getModelUpdateCondition(array $options)
  2995. {
  2996. return isset($options['where']['AND']) ? $options['where']['AND'] : null;
  2997. }
  2998. /**
  2999. * 查询失败 抛出异常
  3000. * @access protected
  3001. * @param array $options 查询参数
  3002. * @throws ModelNotFoundException
  3003. * @throws DataNotFoundException
  3004. */
  3005. protected function throwNotFound($options = [])
  3006. {
  3007. if (!empty($this->model)) {
  3008. $class = get_class($this->model);
  3009. throw new ModelNotFoundException('model data Not Found:' . $class, $class, $options);
  3010. }
  3011. $table = is_array($options['table']) ? key($options['table']) : $options['table'];
  3012. throw new DataNotFoundException('table data not Found:' . $table, $table, $options);
  3013. }
  3014. /**
  3015. * 查找多条记录 如果不存在则抛出异常
  3016. * @access public
  3017. * @param array|string|Query|\Closure $data
  3018. * @return array|\PDOStatement|string|Model
  3019. * @throws DbException
  3020. * @throws ModelNotFoundException
  3021. * @throws DataNotFoundException
  3022. */
  3023. public function selectOrFail($data = null)
  3024. {
  3025. return $this->failException(true)->select($data);
  3026. }
  3027. /**
  3028. * 查找单条记录 如果不存在则抛出异常
  3029. * @access public
  3030. * @param array|string|Query|\Closure $data
  3031. * @return array|\PDOStatement|string|Model
  3032. * @throws DbException
  3033. * @throws ModelNotFoundException
  3034. * @throws DataNotFoundException
  3035. */
  3036. public function findOrFail($data = null)
  3037. {
  3038. return $this->failException(true)->find($data);
  3039. }
  3040. /**
  3041. * 查找单条记录 不存在则返回空模型
  3042. * @access public
  3043. * @param array|string|Query|\Closure $data
  3044. * @return array|\PDOStatement|string|Model
  3045. * @throws DbException
  3046. * @throws ModelNotFoundException
  3047. * @throws DataNotFoundException
  3048. */
  3049. public function findOrEmpty($data = null)
  3050. {
  3051. return $this->allowEmpty(true)->find($data);
  3052. }
  3053. /**
  3054. * 分批数据返回处理
  3055. * @access public
  3056. * @param integer $count 每次处理的数据数量
  3057. * @param callable $callback 处理回调方法
  3058. * @param string|array $column 分批处理的字段名
  3059. * @param string $order 字段排序
  3060. * @return boolean
  3061. * @throws DbException
  3062. */
  3063. public function chunk($count, $callback, $column = null, $order = 'asc')
  3064. {
  3065. $options = $this->getOptions();
  3066. $column = $column ?: $this->getPk($options);
  3067. if (isset($options['order'])) {
  3068. if (Container::get('app')->isDebug()) {
  3069. throw new DbException('chunk not support call order');
  3070. }
  3071. unset($options['order']);
  3072. }
  3073. $bind = $this->bind;
  3074. if (is_array($column)) {
  3075. $times = 1;
  3076. $query = $this->options($options)->page($times, $count);
  3077. } else {
  3078. $query = $this->options($options)->limit($count);
  3079. if (strpos($column, '.')) {
  3080. list($alias, $key) = explode('.', $column);
  3081. } else {
  3082. $key = $column;
  3083. }
  3084. }
  3085. $resultSet = $query->order($column, $order)->select();
  3086. while (count($resultSet) > 0) {
  3087. if ($resultSet instanceof Collection) {
  3088. $resultSet = $resultSet->all();
  3089. }
  3090. if (false === call_user_func($callback, $resultSet)) {
  3091. return false;
  3092. }
  3093. if (isset($times)) {
  3094. $times++;
  3095. $query = $this->options($options)->page($times, $count);
  3096. } else {
  3097. $end = end($resultSet);
  3098. $lastId = is_array($end) ? $end[$key] : $end->getData($key);
  3099. $query = $this->options($options)
  3100. ->limit($count)
  3101. ->where($column, 'asc' == strtolower($order) ? '>' : '<', $lastId);
  3102. }
  3103. $resultSet = $query->bind($bind)->order($column, $order)->select();
  3104. }
  3105. return true;
  3106. }
  3107. /**
  3108. * 获取绑定的参数 并清空
  3109. * @access public
  3110. * @param bool $clear
  3111. * @return array
  3112. */
  3113. public function getBind($clear = true)
  3114. {
  3115. $bind = $this->bind;
  3116. if ($clear) {
  3117. $this->bind = [];
  3118. }
  3119. return $bind;
  3120. }
  3121. /**
  3122. * 创建子查询SQL
  3123. * @access public
  3124. * @param bool $sub
  3125. * @return string
  3126. * @throws DbException
  3127. */
  3128. public function buildSql($sub = true)
  3129. {
  3130. return $sub ? '( ' . $this->select(false) . ' )' : $this->select(false);
  3131. }
  3132. /**
  3133. * 视图查询处理
  3134. * @access protected
  3135. * @param array $options 查询参数
  3136. * @return void
  3137. */
  3138. protected function parseView(&$options)
  3139. {
  3140. if (!isset($options['map'])) {
  3141. return;
  3142. }
  3143. foreach (['AND', 'OR'] as $logic) {
  3144. if (isset($options['where'][$logic])) {
  3145. foreach ($options['where'][$logic] as $key => $val) {
  3146. if (array_key_exists($key, $options['map'])) {
  3147. array_shift($val);
  3148. array_unshift($val, $options['map'][$key]);
  3149. $options['where'][$logic][$options['map'][$key]] = $val;
  3150. unset($options['where'][$logic][$key]);
  3151. }
  3152. }
  3153. }
  3154. }
  3155. if (isset($options['order'])) {
  3156. // 视图查询排序处理
  3157. if (is_string($options['order'])) {
  3158. $options['order'] = explode(',', $options['order']);
  3159. }
  3160. foreach ($options['order'] as $key => $val) {
  3161. if (is_numeric($key) && is_string($val)) {
  3162. if (strpos($val, ' ')) {
  3163. list($field, $sort) = explode(' ', $val);
  3164. if (array_key_exists($field, $options['map'])) {
  3165. $options['order'][$options['map'][$field]] = $sort;
  3166. unset($options['order'][$key]);
  3167. }
  3168. } elseif (array_key_exists($val, $options['map'])) {
  3169. $options['order'][$options['map'][$val]] = 'asc';
  3170. unset($options['order'][$key]);
  3171. }
  3172. } elseif (array_key_exists($key, $options['map'])) {
  3173. $options['order'][$options['map'][$key]] = $val;
  3174. unset($options['order'][$key]);
  3175. }
  3176. }
  3177. }
  3178. }
  3179. /**
  3180. * 把主键值转换为查询条件 支持复合主键
  3181. * @access public
  3182. * @param array|string $data 主键数据
  3183. * @return void
  3184. * @throws Exception
  3185. */
  3186. public function parsePkWhere($data)
  3187. {
  3188. $pk = $this->getPk($this->options);
  3189. // 获取当前数据表
  3190. $table = is_array($this->options['table']) ? key($this->options['table']) : $this->options['table'];
  3191. if (!empty($this->options['alias'][$table])) {
  3192. $alias = $this->options['alias'][$table];
  3193. }
  3194. if (is_string($pk)) {
  3195. $key = isset($alias) ? $alias . '.' . $pk : $pk;
  3196. // 根据主键查询
  3197. if (is_array($data)) {
  3198. $where[$pk] = isset($data[$pk]) ? [$key, '=', $data[$pk]] : [$key, 'in', $data];
  3199. } else {
  3200. $where[$pk] = strpos($data, ',') ? [$key, 'IN', $data] : [$key, '=', $data];
  3201. }
  3202. } elseif (is_array($pk) && is_array($data) && !empty($data)) {
  3203. // 根据复合主键查询
  3204. foreach ($pk as $key) {
  3205. if (isset($data[$key])) {
  3206. $attr = isset($alias) ? $alias . '.' . $key : $key;
  3207. $where[$key] = [$attr, '=', $data[$key]];
  3208. } else {
  3209. throw new Exception('miss complex primary data');
  3210. }
  3211. }
  3212. }
  3213. if (!empty($where)) {
  3214. if (isset($this->options['where']['AND'])) {
  3215. $this->options['where']['AND'] = array_merge($this->options['where']['AND'], $where);
  3216. } else {
  3217. $this->options['where']['AND'] = $where;
  3218. }
  3219. }
  3220. return;
  3221. }
  3222. /**
  3223. * 分析表达式(可用于查询或者写入操作)
  3224. * @access protected
  3225. * @return array
  3226. */
  3227. protected function parseOptions()
  3228. {
  3229. $options = $this->getOptions();
  3230. // 获取数据表
  3231. if (empty($options['table'])) {
  3232. $options['table'] = $this->getTable();
  3233. }
  3234. if (!isset($options['where'])) {
  3235. $options['where'] = [];
  3236. } elseif (isset($options['view'])) {
  3237. // 视图查询条件处理
  3238. $this->parseView($options);
  3239. }
  3240. if (!isset($options['field'])) {
  3241. $options['field'] = '*';
  3242. }
  3243. foreach (['data', 'order', 'join', 'union'] as $name) {
  3244. if (!isset($options[$name])) {
  3245. $options[$name] = [];
  3246. }
  3247. }
  3248. if (!isset($options['strict'])) {
  3249. $options['strict'] = $this->getConfig('fields_strict');
  3250. }
  3251. foreach (['master', 'lock', 'fetch_pdo', 'fetch_sql', 'distinct'] as $name) {
  3252. if (!isset($options[$name])) {
  3253. $options[$name] = false;
  3254. }
  3255. }
  3256. if (isset(static::$readMaster['*']) || (is_string($options['table']) && isset(static::$readMaster[$options['table']]))) {
  3257. $options['master'] = true;
  3258. }
  3259. foreach (['group', 'having', 'limit', 'force', 'comment'] as $name) {
  3260. if (!isset($options[$name])) {
  3261. $options[$name] = '';
  3262. }
  3263. }
  3264. if (isset($options['page'])) {
  3265. // 根据页数计算limit
  3266. list($page, $listRows) = $options['page'];
  3267. $page = $page > 0 ? $page : 1;
  3268. $listRows = $listRows > 0 ? $listRows : (is_numeric($options['limit']) ? $options['limit'] : 20);
  3269. $offset = $listRows * ($page - 1);
  3270. $options['limit'] = $offset . ',' . $listRows;
  3271. }
  3272. $this->options = $options;
  3273. return $options;
  3274. }
  3275. /**
  3276. * 注册回调方法
  3277. * @access public
  3278. * @param string $event 事件名
  3279. * @param callable $callback 回调方法
  3280. * @return void
  3281. */
  3282. public static function event($event, $callback)
  3283. {
  3284. self::$event[$event] = $callback;
  3285. }
  3286. /**
  3287. * 触发事件
  3288. * @access public
  3289. * @param string $event 事件名
  3290. * @return bool
  3291. */
  3292. public function trigger($event)
  3293. {
  3294. $result = false;
  3295. if (isset(self::$event[$event])) {
  3296. $result = Container::getInstance()->invoke(self::$event[$event], [$this]);
  3297. }
  3298. return $result;
  3299. }
  3300. }