{"action":"opened","issue":{"url":"https://api.github.com/repos/tres-framework/Trestle/issues/10","labels_url":"https://api.github.com/repos/tres-framework/Trestle/issues/10/labels{/name}","comments_url":"https://api.github.com/repos/tres-framework/Trestle/issues/10/comments","events_url":"https://api.github.com/repos/tres-framework/Trestle/issues/10/events","html_url":"https://github.com/tres-framework/Trestle/issues/10","id":59186959,"number":10,"title":"Total JOIN Support","user":{"login":"FireDart","id":58045,"avatar_url":"https://avatars.githubusercontent.com/u/58045?v=3","gravatar_id":"","url":"https://api.github.com/users/FireDart","html_url":"https://github.com/FireDart","followers_url":"https://api.github.com/users/FireDart/followers","following_url":"https://api.github.com/users/FireDart/following{/other_user}","gists_url":"https://api.github.com/users/FireDart/gists{/gist_id}","starred_url":"https://api.github.com/users/FireDart/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/FireDart/subscriptions","organizations_url":"https://api.github.com/users/FireDart/orgs","repos_url":"https://api.github.com/users/FireDart/repos","events_url":"https://api.github.com/users/FireDart/events{/privacy}","received_events_url":"https://api.github.com/users/FireDart/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":0,"created_at":"2015-02-27T04:10:35Z","updated_at":"2015-02-27T04:10:35Z","closed_at":null,"body":"This expands of issue #9.\r\n\r\n# Goals\r\n- [x] `INNER JOIN` (Essentially the same as `join`; added with commit 2efe4b76a83252bcdea012742b4a2efcc4d66194)\r\n- [ ] `LEFT JOIN`\r\n- [ ] `RIGHT JOIN`\r\n- [ ] `FULL OUTER JOIN`\r\n\r\n\r\n# Syntax (& output)\r\n## JOIN (INNER JOIN)\r\n```php\r\n$query = $db->get(['users', 'articles'])\r\n            ->exec();\r\n\r\n// Generates\r\n// SELECT * FROM `users`, `articles`\r\n\r\n$query = $db->get(['articles.id', 'articles.title', 'users.username'])\r\n            ->where('articles.author', '=', 'users.id', true)\r\n            ->exec();\r\n\r\n// Generates\r\n// SELECT `users`.`id`, `users`.`username`, `articles`.`id`, `articles`.`title` FROM `users`, `articles`\r\n\r\n$query = $db->get(['articles', 'users'], ['articles.id', 'articles.title', 'users.username'])\r\n            ->where('articles.author', '=', 'users.id', true)\r\n            ->exec();\r\n\r\n// Generates\r\n// SELECT `users`.`id`, `users`.`username`, `articles`.`id`, `articles`.`title` FROM `users`, `articles`\r\n\r\n$query = $db->get(['articles.id', 'articles.title', 'users.username'])\r\n            ->join('articles.author', '=', 'users.id')\r\n            ->exec();\r\n\r\n// Generates\r\n// SELECT `users`.`id`, `users`.`username`, `articles`.`id`, `articles`.`title` FROM `articles` JOIN `users` ON `articles`.`author` = `users`.`id`\r\n\r\n$query = $db->get(['articles', 'users'], ['articles.id', 'articles.title', 'users.username'])\r\n            ->join('articles.author', '=', 'users.id')\r\n            ->exec();\r\n\r\n// Generates\r\n// SELECT `users`.`id`, `users`.`username`, `articles`.`id`, `articles`.`title` FROM `articles` JOIN `users` ON `articles`.`author` = `users`.`id`\r\n```\r\n\r\n## LEFT JOIN\r\n```php\r\n$query = $db->get(['articles.id', 'articles.title', 'users.username'])\r\n            ->leftJoin('articles.author', '=', 'users.id')\r\n            ->exec();\r\n\r\n// Generates\r\n// SELECT `users`.`id`, `users`.`username`, `articles`.`id`, `articles`.`title` FROM `articles` LEFT JOIN `users` ON `articles`.`author` = `users`.`id`\r\n```\r\n\r\n## RIGHT JOIN\r\n```php\r\n$query = $db->get(['articles.id', 'articles.title', 'users.username'])\r\n            ->rightJoin('articles.author', '=', 'users.id')\r\n            ->exec();\r\n\r\n// Generates\r\n// SELECT `users`.`id`, `users`.`username`, `articles`.`id`, `articles`.`title` FROM `articles` RIGHT JOIN `users` ON `articles`.`author` = `users`.`id`\r\n```\r\n\r\n\r\n## FULL OUTER JOIN\r\n```php\r\n$query = $db->get(['articles.id', 'articles.title', 'users.username'])\r\n            ->fullOuterJoin('articles.author', '=', 'users.id')\r\n            ->exec();\r\n\r\n// Generates\r\n// SELECT `users`.`id`, `users`.`username`, `articles`.`id`, `articles`.`title` FROM `articles` FULL OUTER JOIN `users` ON `articles`.`author` = `users`.`id`\r\n```\r\n\r\n@pedzed Two questions\r\n1. Should `join()` use `INNER JOIN` instead of `JOIN` syntax in the query since they are same syntax and apparently `INNER JOIN` is \"easier to read\"? The Trestle syntax would still be `->join(x, y, z)`\r\n2. Should it be `fullOuterJoin` or `OuterJoin`?\r\n\r\n[JOIN Reference cheat sheet](https://upload.wikimedia.org/wikipedia/commons/9/9d/SQL_Joins.svg)"}}