SelectTest.php 16 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690
  1. <?php
  2. namespace Medoo\Tests;
  3. use Medoo\Medoo;
  4. use InvalidArgumentException;
  5. /**
  6. * @coversDefaultClass \Medoo\Medoo
  7. */
  8. class SelectTest extends MedooTestCase
  9. {
  10. /**
  11. * @covers ::select()
  12. * @covers ::selectContext()
  13. * @covers ::isJoin()
  14. * @covers ::columnMap()
  15. * @covers ::columnPush()
  16. * @dataProvider typesProvider
  17. */
  18. public function testSelectAll($type)
  19. {
  20. $this->setType($type);
  21. $this->database->select("account", "*");
  22. $this->assertQuery(
  23. <<<EOD
  24. SELECT * FROM "account"
  25. EOD,
  26. $this->database->queryString
  27. );
  28. }
  29. /**
  30. * @covers ::select()
  31. * @covers ::selectContext()
  32. * @dataProvider typesProvider
  33. */
  34. public function testSelectTableWithAlias($type)
  35. {
  36. $this->setType($type);
  37. $this->database->select("account (user)", "name");
  38. $this->assertQuery(
  39. <<<EOD
  40. SELECT "name"
  41. FROM "account" AS "user"
  42. EOD,
  43. $this->database->queryString
  44. );
  45. }
  46. /**
  47. * @covers ::columnMap()
  48. * @covers ::columnPush()
  49. * @dataProvider typesProvider
  50. */
  51. public function testSelectSingleColumn($type)
  52. {
  53. $this->setType($type);
  54. $this->database->select("account", "name");
  55. $this->assertQuery(
  56. <<<EOD
  57. SELECT "name"
  58. FROM "account"
  59. EOD,
  60. $this->database->queryString
  61. );
  62. }
  63. /**
  64. * @covers ::columnMap()
  65. * @covers ::columnPush()
  66. * @dataProvider typesProvider
  67. */
  68. public function testSelectColumns($type)
  69. {
  70. $this->setType($type);
  71. $this->database->select("account", ["name", "id"]);
  72. $this->assertQuery(
  73. <<<EOD
  74. SELECT "name","id"
  75. FROM "account"
  76. EOD,
  77. $this->database->queryString
  78. );
  79. }
  80. /**
  81. * @covers ::columnMap()
  82. * @covers ::columnPush()
  83. * @dataProvider typesProvider
  84. */
  85. public function testSelectColumnsWithAlias($type)
  86. {
  87. $this->setType($type);
  88. $this->database->select("account", ["name(nickname)", "id"]);
  89. $this->assertQuery(
  90. <<<EOD
  91. SELECT "name" AS "nickname","id"
  92. FROM "account"
  93. EOD,
  94. $this->database->queryString
  95. );
  96. }
  97. /**
  98. * @covers ::columnMap()
  99. * @covers ::columnPush()
  100. * @dataProvider typesProvider
  101. */
  102. public function testSelectColumnsWithType($type)
  103. {
  104. $this->setType($type);
  105. $this->database->select("account", ["name[String]", "data [JSON]"]);
  106. $this->assertQuery(
  107. <<<EOD
  108. SELECT "name","data"
  109. FROM "account"
  110. EOD,
  111. $this->database->queryString
  112. );
  113. }
  114. /**
  115. * @covers ::columnMap()
  116. * @covers ::columnPush()
  117. * @dataProvider typesProvider
  118. */
  119. public function testSelectColumnsWithAliasAndType($type)
  120. {
  121. $this->setType($type);
  122. $this->database->select("account", ["name (nickname) [String]", "data [JSON]"]);
  123. $this->assertQuery(
  124. <<<EOD
  125. SELECT "name" AS "nickname","data"
  126. FROM "account"
  127. EOD,
  128. $this->database->queryString
  129. );
  130. }
  131. /**
  132. * @covers ::columnMap()
  133. * @covers ::columnPush()
  134. * @dataProvider typesProvider
  135. */
  136. public function testSelectColumnsWithRaw($type)
  137. {
  138. $this->setType($type);
  139. $this->database->select("account", [
  140. "id [String]" => Medoo::raw("UUID()")
  141. ]);
  142. $this->assertQuery(
  143. <<<EOD
  144. SELECT UUID() AS "id"
  145. FROM "account"
  146. EOD,
  147. $this->database->queryString
  148. );
  149. }
  150. /**
  151. * @covers ::select()
  152. * @covers ::selectContext()
  153. * @covers ::isJoin()
  154. * @dataProvider typesProvider
  155. */
  156. public function testSelectWithWhere($type)
  157. {
  158. $this->setType($type);
  159. $this->database->select("account", [
  160. "name",
  161. "id"
  162. ], [
  163. "ORDER" => "age"
  164. ]);
  165. $this->assertQuery(
  166. <<<EOD
  167. SELECT "name","id"
  168. FROM "account"
  169. ORDER BY "age"
  170. EOD,
  171. $this->database->queryString
  172. );
  173. }
  174. /**
  175. * @covers ::select()
  176. * @covers ::selectContext()
  177. * @covers ::isJoin()
  178. * @covers ::buildJoin()
  179. * @dataProvider typesProvider
  180. */
  181. public function testSelectWithLeftJoin($type)
  182. {
  183. $this->setType($type);
  184. $this->database->select("account", [
  185. "[>]post" => "user_id"
  186. ], [
  187. "account.name",
  188. "post.title"
  189. ]);
  190. $this->assertQuery(
  191. <<<EOD
  192. SELECT "account"."name","post"."title"
  193. FROM "account"
  194. LEFT JOIN "post"
  195. USING ("user_id")
  196. EOD,
  197. $this->database->queryString
  198. );
  199. }
  200. /**
  201. * @covers ::isJoin()
  202. * @covers ::buildJoin()
  203. * @dataProvider typesProvider
  204. */
  205. public function testSelectWithRightJoin($type)
  206. {
  207. $this->setType($type);
  208. $this->database->select("account", [
  209. "[<]post" => "user_id"
  210. ], [
  211. "account.name",
  212. "post.title"
  213. ]);
  214. $this->assertQuery(
  215. <<<EOD
  216. SELECT "account"."name","post"."title"
  217. FROM "account"
  218. RIGHT JOIN "post"
  219. USING ("user_id")
  220. EOD,
  221. $this->database->queryString
  222. );
  223. }
  224. /**
  225. * @covers ::isJoin()
  226. * @covers ::buildJoin()
  227. * @dataProvider typesProvider
  228. */
  229. public function testSelectWithFullJoin($type)
  230. {
  231. $this->setType($type);
  232. $this->database->select("account", [
  233. "[<>]post" => "user_id"
  234. ], [
  235. "account.name",
  236. "post.title"
  237. ]);
  238. $this->assertQuery(
  239. <<<EOD
  240. SELECT "account"."name","post"."title"
  241. FROM "account"
  242. FULL JOIN "post"
  243. USING ("user_id")
  244. EOD,
  245. $this->database->queryString
  246. );
  247. }
  248. /**
  249. * @covers ::isJoin()
  250. * @covers ::buildJoin()
  251. * @dataProvider typesProvider
  252. */
  253. public function testSelectWithInnerJoin($type)
  254. {
  255. $this->setType($type);
  256. $this->database->select("account", [
  257. "[><]post" => "user_id"
  258. ], [
  259. "account.name",
  260. "post.title"
  261. ]);
  262. $this->assertQuery(
  263. <<<EOD
  264. SELECT "account"."name","post"."title"
  265. FROM "account"
  266. INNER JOIN "post"
  267. USING ("user_id")
  268. EOD,
  269. $this->database->queryString
  270. );
  271. }
  272. /**
  273. * @covers ::isJoin()
  274. * @covers ::buildJoin()
  275. * @dataProvider typesProvider
  276. */
  277. public function testSelectWithSameKeysJoin($type)
  278. {
  279. $this->setType($type);
  280. $this->database->select("account", [
  281. "[>]photo" => ["user_id", "avatar_id"],
  282. ], [
  283. "account.name",
  284. "photo.link"
  285. ]);
  286. $this->assertQuery(
  287. <<<EOD
  288. SELECT "account"."name","photo"."link"
  289. FROM "account"
  290. LEFT JOIN "photo"
  291. USING ("user_id", "avatar_id")
  292. EOD,
  293. $this->database->queryString
  294. );
  295. }
  296. /**
  297. * @covers ::isJoin()
  298. * @covers ::buildJoin()
  299. * @dataProvider typesProvider
  300. */
  301. public function testSelectWithKeyJoin($type)
  302. {
  303. $this->setType($type);
  304. $this->database->select("account", [
  305. "[>]post" => ["user_id" => "author_id"],
  306. ], [
  307. "account.name",
  308. "post.title"
  309. ]);
  310. $this->assertQuery(
  311. <<<EOD
  312. SELECT "account"."name","post"."title"
  313. FROM "account"
  314. LEFT JOIN "post"
  315. ON "account"."user_id" = "post"."author_id"
  316. EOD,
  317. $this->database->queryString
  318. );
  319. }
  320. /**
  321. * @covers ::isJoin()
  322. * @covers ::buildJoin()
  323. * @dataProvider typesProvider
  324. */
  325. public function testSelectWithAliasJoin($type)
  326. {
  327. $this->setType($type);
  328. $this->database->select("account", [
  329. "[>]post (main_post)" => ["user_id" => "author_id"],
  330. ], [
  331. "account.name",
  332. "main_post.title"
  333. ]);
  334. $this->assertQuery(
  335. <<<EOD
  336. SELECT "account"."name","main_post"."title"
  337. FROM "account"
  338. LEFT JOIN "post" AS "main_post"
  339. ON "account"."user_id" = "main_post"."author_id"
  340. EOD,
  341. $this->database->queryString
  342. );
  343. }
  344. /**
  345. * @covers ::isJoin()
  346. * @covers ::buildJoin()
  347. * @dataProvider typesProvider
  348. */
  349. public function testSelectWithReferJoin($type)
  350. {
  351. $this->setType($type);
  352. $this->database->select("account", [
  353. "[>]post" => ["user_id" => "author_id"],
  354. "[>]album" => ["post.author_id" => "user_id"],
  355. ], [
  356. "account.name",
  357. "post.title",
  358. "album.link"
  359. ]);
  360. $this->assertQuery(
  361. <<<EOD
  362. SELECT "account"."name","post"."title","album"."link"
  363. FROM "account"
  364. LEFT JOIN "post"
  365. ON "account"."user_id" = "post"."author_id"
  366. LEFT JOIN "album"
  367. ON "post"."author_id" = "album"."user_id"
  368. EOD,
  369. $this->database->queryString
  370. );
  371. }
  372. /**
  373. * @covers ::isJoin()
  374. * @covers ::buildJoin()
  375. * @dataProvider typesProvider
  376. */
  377. public function testSelectWithMultipleConditionJoin($type)
  378. {
  379. $this->setType($type);
  380. $this->database->select("account", [
  381. "[>]album" => ["author_id" => "user_id"],
  382. "[>]post" => [
  383. "user_id" => "author_id",
  384. "album.user_id" => "owner_id"
  385. ]
  386. ], [
  387. "account.name",
  388. "post.title",
  389. "album.link"
  390. ]);
  391. $this->assertQuery(
  392. <<<EOD
  393. SELECT "account"."name","post"."title","album"."link"
  394. FROM "account"
  395. LEFT JOIN "album"
  396. ON "account"."author_id" = "album"."user_id"
  397. LEFT JOIN "post"
  398. ON "account"."user_id" = "post"."author_id"
  399. AND "album"."user_id" = "post"."owner_id"
  400. EOD,
  401. $this->database->queryString
  402. );
  403. }
  404. /**
  405. * @covers ::isJoin()
  406. * @covers ::buildJoin()
  407. * @dataProvider typesProvider
  408. */
  409. public function testSelectWithAdditionalConditionJoin($type)
  410. {
  411. $this->setType($type);
  412. $this->database->select("account", [
  413. "[>]post" => [
  414. "user_id" => "author_id",
  415. "AND" => [
  416. "post.id[>]" => 10
  417. ]
  418. ]
  419. ], [
  420. "account.name",
  421. "post.title"
  422. ]);
  423. $this->assertQuery(
  424. <<<EOD
  425. SELECT "account"."name","post"."title"
  426. FROM "account"
  427. LEFT JOIN "post"
  428. ON "account"."user_id" = "post"."author_id"
  429. AND "post"."id" > 10
  430. EOD,
  431. $this->database->queryString
  432. );
  433. }
  434. /**
  435. * @covers ::isJoin()
  436. * @covers ::buildJoin()
  437. * @dataProvider typesProvider
  438. */
  439. public function testSelectRawJoin($type)
  440. {
  441. $this->setType($type);
  442. $this->database->select("account", [
  443. "[>]post" => Medoo::raw("ON <account.user_id> = <post.author_id>")
  444. ], [
  445. "account.name",
  446. "post.title"
  447. ]);
  448. $this->assertQuery(
  449. <<<EOD
  450. SELECT "account"."name","post"."title"
  451. FROM "account"
  452. LEFT JOIN "post"
  453. ON "account"."user_id" = "post"."author_id"
  454. EOD,
  455. $this->database->queryString
  456. );
  457. }
  458. /**
  459. * @covers ::columnMap()
  460. * @covers ::columnPush()
  461. * @dataProvider typesProvider
  462. */
  463. public function testSelectAllWithJoin($type)
  464. {
  465. $this->setType($type);
  466. $this->expectException(InvalidArgumentException::class);
  467. $this->database->select("account", [
  468. "[>]post" => "user_id"
  469. ], [
  470. "account.*"
  471. ]);
  472. }
  473. /**
  474. * @covers ::columnMap()
  475. * @covers ::columnPush()
  476. * @dataProvider typesProvider
  477. */
  478. public function testSelectWithDataMapping($type)
  479. {
  480. $this->setType($type);
  481. $this->database->select("post", [
  482. "[>]account" => ["user_id"]
  483. ], [
  484. "post.content",
  485. "userData" => [
  486. "account.user_id",
  487. "account.email",
  488. "meta" => [
  489. "account.location",
  490. "account.gender"
  491. ]
  492. ]
  493. ]);
  494. $this->assertQuery(
  495. <<<EOD
  496. SELECT "post"."content","account"."user_id","account"."email","account"."location","account"."gender"
  497. FROM "post"
  498. LEFT JOIN "account"
  499. USING ("user_id")
  500. EOD,
  501. $this->database->queryString
  502. );
  503. }
  504. /**
  505. * @covers ::columnMap()
  506. * @covers ::columnPush()
  507. * @dataProvider typesProvider
  508. */
  509. public function testSelectWithIndexMapping($type)
  510. {
  511. $this->setType($type);
  512. $this->database->select("account", [
  513. "user_id" => [
  514. "name (nickname)",
  515. "location"
  516. ]
  517. ]);
  518. $this->assertQuery(
  519. <<<EOD
  520. SELECT "user_id","name" AS "nickname","location"
  521. FROM "account"
  522. EOD,
  523. $this->database->queryString
  524. );
  525. }
  526. /**
  527. * @covers ::columnMap()
  528. * @covers ::columnPush()
  529. * @dataProvider typesProvider
  530. */
  531. public function testSelectWithDistinct($type)
  532. {
  533. $this->setType($type);
  534. $this->database->select("account", [
  535. "@location",
  536. "nickname"
  537. ]);
  538. $this->assertQuery(
  539. <<<EOD
  540. SELECT DISTINCT "location","nickname"
  541. FROM "account"
  542. EOD,
  543. $this->database->queryString
  544. );
  545. }
  546. /**
  547. * @covers ::columnMap()
  548. * @covers ::columnPush()
  549. * @dataProvider typesProvider
  550. */
  551. public function testSelectWithDistinctDiffOrder($type)
  552. {
  553. $this->setType($type);
  554. $this->database->select("account", [
  555. "location",
  556. "@nickname"
  557. ]);
  558. $this->assertQuery(
  559. <<<EOD
  560. SELECT DISTINCT "nickname","location"
  561. FROM "account"
  562. EOD,
  563. $this->database->queryString
  564. );
  565. }
  566. /**
  567. * @covers ::columnMap()
  568. * @covers ::columnPush()
  569. * @dataProvider typesProvider
  570. */
  571. public function testSelectWithUnicodeCharacter($type)
  572. {
  573. $this->setType($type);
  574. $this->database->select("considérer", [
  575. "name (名前)",
  576. "положение (ロケーション)"
  577. ]);
  578. $this->assertQuery(
  579. <<<EOD
  580. SELECT "name" AS "名前","положение" AS "ロケーション"
  581. FROM "considérer"
  582. EOD,
  583. $this->database->queryString
  584. );
  585. }
  586. /**
  587. * @covers ::columnMap()
  588. * @covers ::columnPush()
  589. * @dataProvider typesProvider
  590. */
  591. public function testSelectWithHyphenCharacter($type)
  592. {
  593. $this->setType($type);
  594. $this->database->select("account", [
  595. "nick-name"
  596. ]);
  597. $this->assertQuery(
  598. <<<EOD
  599. SELECT "nick-name"
  600. FROM "account"
  601. EOD,
  602. $this->database->queryString
  603. );
  604. }
  605. }