{"_id":"576c19b1808cf02b00d373f6","parentDoc":null,"project":"56e5982f9191742000ef204a","user":"543466ea0e8e2b0e00341818","version":{"_id":"57531435be31940e0035ad96","project":"56e5982f9191742000ef204a","__v":3,"createdAt":"2016-06-04T17:47:33.623Z","releaseDate":"2016-06-04T17:47:33.623Z","categories":["57531435be31940e0035ad97","57531435be31940e0035ad98","57531652d8fe070e00258a51","576c609fba92910e0099ad3f"],"is_deprecated":false,"is_hidden":false,"is_beta":true,"is_stable":true,"codename":"","version_clean":"0.2.0","version":"0.2"},"githubsync":"","category":{"_id":"57531652d8fe070e00258a51","__v":0,"project":"56e5982f9191742000ef204a","version":"57531435be31940e0035ad96","sync":{"url":"","isSync":false},"reference":false,"createdAt":"2016-06-04T17:56:34.972Z","from_sync":false,"order":2,"slug":"expression-api","title":"Expression API"},"__v":27,"updates":[],"next":{"pages":[],"description":""},"createdAt":"2016-06-23T17:17:37.311Z","link_external":false,"link_url":"","sync_unique":"","hidden":false,"api":{"results":{"codes":[]},"settings":"","auth":"required","params":[],"url":""},"isReference":false,"order":2,"body":"There are various ways of selecting data from a db in qb. The following examples assume that you have defined the following tables using the table api;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"package main\\n\\nimport (\\n\\t\\\"fmt\\\"\\n\\t\\\"github.com/aacanakin/qb\\\"\\n)\\n\\nfunc main() {\\n\\tdb, _ := qb.New(\\\"mysql\\\", \\\"root::::at:::tcp(localhost:3306)/qb_test?charset=utf8\\\")\\n\\tdefer db.Close()\\n\\n\\tusers := qb.Table(\\n\\t\\t\\\"user\\\",\\n\\t\\tqb.Column(\\\"id\\\", qb.Varchar().Size(36)),\\n\\t\\tqb.Column(\\\"email\\\", qb.Varchar().Unique().NotNull()),\\n\\t\\tqb.Column(\\\"password\\\", qb.Varchar().NotNull()),\\n\\t\\tqb.Column(\\\"name\\\", qb.Varchar().NotNull()),\\n\\t\\tqb.PrimaryKey(\\\"id\\\"),\\n\\t)\\n\\n\\tsessions := qb.Table(\\n\\t\\t\\\"session\\\",\\n\\t\\tqb.Column(\\\"id\\\", qb.Varchar().Size(36)),\\n\\t\\tqb.Column(\\\"user_id\\\", qb.Varchar().Size(36)),\\n\\t\\tqb.Column(\\\"auth_token\\\", qb.Varchar().Size(36)),\\n\\t\\tqb.Column(\\\"created_at\\\", qb.Timestamp().NotNull()),\\n\\t\\tqb.Column(\\\"expires_at\\\", qb.Timestamp().Null()),\\n\\t\\tqb.ForeignKey().Ref(\\\"user_id\\\", \\\"users\\\", \\\"id\\\"),\\n\\t)\\n\\n  fmt.Println(users.Create(db.Dialect()))\\n\\tfmt.Println(sessions.Create(db.Dialect()))\\n}\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\n\n[block:parameters]\n{\n  \"data\": {},\n  \"cols\": 3,\n  \"rows\": 1\n}\n[/block]\nA simple select query example would be the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"sel := users.\\n\\t\\tSelect(users.C(\\\"id\\\"), users.C(\\\"email\\\")).\\n\\t\\tWhere(users.C(\\\"name\\\").Eq(\\\"Al Pacino\\\")).\\n\\t\\tBuild(db.Dialect())\\n\\nfmt.Println(sel.SQL())\\nfmt.Println(sel.Bindings())\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThe output would be the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"SELECT id, email\\nFROM user\\nWHERE user.name = ?;\\n[Al Pacino]\",\n      \"language\": \"sql\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Selecting by And/Or Combiners\"\n}\n[/block]\nA more complex example using multiple where clauses with And combiner would be the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"sel := users.\\n\\t\\tSelect(users.C(\\\"id\\\"), users.C(\\\"email\\\")).\\n\\t\\tWhere(\\n\\t\\t\\tqb.And(\\n\\t\\t\\t\\tusers.C(\\\"name\\\").Eq(\\\"Al Pacino\\\"),\\n\\t\\t\\t\\tusers.C(\\\"email\\\").NotEq(\\\"robert@downey.com\\\"),\\n\\t\\t\\t)).\\n\\t\\tBuild(db.Dialect())\\n\\nfmt.Println(sel.SQL())\\nfmt.Println(sel.Bindings())\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThe output would be the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"SELECT id, email\\nFROM user\\nWHERE (user.name = ? AND user.email != ?);\\n[Al Pacino robert@downey.com]\",\n      \"language\": \"sql\"\n    }\n  ]\n}\n[/block]\nThe OR combiner can also be used in the same way;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"sel := users.\\n\\t\\tSelect(users.C(\\\"id\\\"), users.C(\\\"email\\\")).\\n\\t\\tWhere(\\n\\t\\t\\tqb.Or(\\n\\t\\t\\t\\tusers.C(\\\"name\\\").Eq(\\\"Al Pacino\\\"),\\n\\t\\t\\t\\tusers.C(\\\"email\\\").NotEq(\\\"robert@downey.com\\\"),\\n\\t\\t\\t)).\\n\\t\\tBuild(db.Dialect())\\n\\nfmt.Println(sel.SQL())\\nfmt.Println(sel.Bindings())\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nIt will output the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"SELECT id, email\\nFROM user\\nWHERE (user.name = ? OR user.email != ?);\\n[Al Pacino robert@downey.com]\",\n      \"language\": \"sql\"\n    }\n  ]\n}\n[/block]\nMoreover, And & Or functions can be used recursively;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"sel := users.\\n\\t\\tSelect(users.C(\\\"id\\\"), users.C(\\\"email\\\")).\\n\\t\\tWhere(\\n\\t\\t\\tqb.And(\\n\\t\\t\\t\\tusers.C(\\\"name\\\").Eq(\\\"Al Pacino\\\"),\\n\\t\\t\\t\\tqb.Or(\\n\\t\\t\\t\\t\\tusers.C(\\\"email\\\").Eq(\\\"al@pacino.com\\\"),\\n\\t\\t\\t\\t\\tusers.C(\\\"email\\\").Eq(\\\"pacino@al.com\\\"),\\n\\t\\t\\t\\t),\\n\\t\\t\\t)).\\n\\t\\tBuild(db.Dialect())\\n\\nfmt.Println(sel.SQL())\\nfmt.Println(sel.Bindings())\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThe output would be the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"SELECT id, email\\nFROM user\\nWHERE (user.name = ? AND (user.email = ? OR user.email = ?));\\n[Al Pacino al@pacino.com pacino@al.com]\",\n      \"language\": \"sql\"\n    }\n  ]\n}\n[/block]\n\n[block:api-header]\n{\n  \"type\": \"basic\",\n  \"title\": \"Using Joins\"\n}\n[/block]\nLet's retrieve user sessions by using inner join;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"sel := qb.\\n\\t\\tSelect(\\n\\t\\t\\tsessions.C(\\\"id\\\"),\\n\\t\\t\\tsessions.C(\\\"user_id\\\"),\\n\\t\\t\\tsessions.C(\\\"created_at\\\"),\\n\\t\\t\\tsessions.C(\\\"expires_at\\\"),\\n\\t\\t).\\n\\t\\tFrom(sessions).\\n\\t\\tInnerJoin(users, sessions.C(\\\"user_id\\\"), users.C(\\\"id\\\")).\\n\\t\\tWhere(sessions.C(\\\"user_id\\\").Eq(\\\"3af82cdc-4d21-473b-a175-cbc3f9119eda\\\")).\\n\\t\\tBuild(db.Dialect())\\n\\nfmt.Println(sel.SQL())\\nfmt.Println(sel.Bindings())\",\n      \"language\": \"go\"\n    }\n  ]\n}\n[/block]\nThe output would be the following;\n[block:code]\n{\n  \"codes\": [\n    {\n      \"code\": \"SELECT session.id, session.user_id, session.created_at, session.expires_at\\nFROM session\\nINNER JOIN user ON session.user_id = user.id\\nWHERE session.user_id = ?;\\n[3af82cdc-4d21-473b-a175-cbc3f9119eda]\",\n      \"language\": \"sql\"\n    }\n  ]\n}\n[/block]\nThere are several types of joins that can be made in qb. Here's the following join table;\n[block:parameters]\n{\n  \"data\": {\n    \"h-0\": \"Join\",\n    \"h-1\": \"Description\",\n    \"0-0\": \"InnerJoin(table TableElem, fromCol ColumnElem, col ColumnElem)\",\n    \"0-1\": \"Performs an INNER JOIN\",\n    \"1-0\": \"LeftJoin(table TableElem, fromCol ColumnElem, col ColumnElem)\",\n    \"2-0\": \"RightJoin(table TableElem, fromCol ColumnElem, col ColumnElem)\",\n    \"3-0\": \"CrossJoin(table TableElem)\",\n    \"1-1\": \"Performs a LEFT JOIN\",\n    \"2-1\": \"Performs a RIGHT JOIN\",\n    \"3-1\": \"Performs a CROSS JOIN\"\n  },\n  \"cols\": 2,\n  \"rows\": 4\n}\n[/block]\n\n[block:callout]\n{\n  \"type\": \"warning\",\n  \"title\": \"Beware about dialects' join supports\",\n  \"body\": \"These join functions doesn't know about dialect's driver(sqlite, mysql, postgres). Therefore, for instance, you need to know if sqlite have right join support. This issue will be addressed in further releases of qb.\"\n}\n[/block]\nAs you might notice, CrossJoin (Cartesian Product) does not require column joining. Therefore, it has only the table parameter.","excerpt":"","slug":"deletes","type":"basic","title":"Selecting"}
There are various ways of selecting data from a db in qb. The following examples assume that you have defined the following tables using the table api; [block:code] { "codes": [ { "code": "package main\n\nimport (\n\t\"fmt\"\n\t\"github.com/aacanakin/qb\"\n)\n\nfunc main() {\n\tdb, _ := qb.New(\"mysql\", \"root:@tcp(localhost:3306)/qb_test?charset=utf8\")\n\tdefer db.Close()\n\n\tusers := qb.Table(\n\t\t\"user\",\n\t\tqb.Column(\"id\", qb.Varchar().Size(36)),\n\t\tqb.Column(\"email\", qb.Varchar().Unique().NotNull()),\n\t\tqb.Column(\"password\", qb.Varchar().NotNull()),\n\t\tqb.Column(\"name\", qb.Varchar().NotNull()),\n\t\tqb.PrimaryKey(\"id\"),\n\t)\n\n\tsessions := qb.Table(\n\t\t\"session\",\n\t\tqb.Column(\"id\", qb.Varchar().Size(36)),\n\t\tqb.Column(\"user_id\", qb.Varchar().Size(36)),\n\t\tqb.Column(\"auth_token\", qb.Varchar().Size(36)),\n\t\tqb.Column(\"created_at\", qb.Timestamp().NotNull()),\n\t\tqb.Column(\"expires_at\", qb.Timestamp().Null()),\n\t\tqb.ForeignKey().Ref(\"user_id\", \"users\", \"id\"),\n\t)\n\n fmt.Println(users.Create(db.Dialect()))\n\tfmt.Println(sessions.Create(db.Dialect()))\n}", "language": "go" } ] } [/block] [block:parameters] { "data": {}, "cols": 3, "rows": 1 } [/block] A simple select query example would be the following; [block:code] { "codes": [ { "code": "sel := users.\n\t\tSelect(users.C(\"id\"), users.C(\"email\")).\n\t\tWhere(users.C(\"name\").Eq(\"Al Pacino\")).\n\t\tBuild(db.Dialect())\n\nfmt.Println(sel.SQL())\nfmt.Println(sel.Bindings())", "language": "go" } ] } [/block] The output would be the following; [block:code] { "codes": [ { "code": "SELECT id, email\nFROM user\nWHERE user.name = ?;\n[Al Pacino]", "language": "sql" } ] } [/block] [block:api-header] { "type": "basic", "title": "Selecting by And/Or Combiners" } [/block] A more complex example using multiple where clauses with And combiner would be the following; [block:code] { "codes": [ { "code": "sel := users.\n\t\tSelect(users.C(\"id\"), users.C(\"email\")).\n\t\tWhere(\n\t\t\tqb.And(\n\t\t\t\tusers.C(\"name\").Eq(\"Al Pacino\"),\n\t\t\t\tusers.C(\"email\").NotEq(\"robert@downey.com\"),\n\t\t\t)).\n\t\tBuild(db.Dialect())\n\nfmt.Println(sel.SQL())\nfmt.Println(sel.Bindings())", "language": "go" } ] } [/block] The output would be the following; [block:code] { "codes": [ { "code": "SELECT id, email\nFROM user\nWHERE (user.name = ? AND user.email != ?);\n[Al Pacino robert@downey.com]", "language": "sql" } ] } [/block] The OR combiner can also be used in the same way; [block:code] { "codes": [ { "code": "sel := users.\n\t\tSelect(users.C(\"id\"), users.C(\"email\")).\n\t\tWhere(\n\t\t\tqb.Or(\n\t\t\t\tusers.C(\"name\").Eq(\"Al Pacino\"),\n\t\t\t\tusers.C(\"email\").NotEq(\"robert@downey.com\"),\n\t\t\t)).\n\t\tBuild(db.Dialect())\n\nfmt.Println(sel.SQL())\nfmt.Println(sel.Bindings())", "language": "go" } ] } [/block] It will output the following; [block:code] { "codes": [ { "code": "SELECT id, email\nFROM user\nWHERE (user.name = ? OR user.email != ?);\n[Al Pacino robert@downey.com]", "language": "sql" } ] } [/block] Moreover, And & Or functions can be used recursively; [block:code] { "codes": [ { "code": "sel := users.\n\t\tSelect(users.C(\"id\"), users.C(\"email\")).\n\t\tWhere(\n\t\t\tqb.And(\n\t\t\t\tusers.C(\"name\").Eq(\"Al Pacino\"),\n\t\t\t\tqb.Or(\n\t\t\t\t\tusers.C(\"email\").Eq(\"al@pacino.com\"),\n\t\t\t\t\tusers.C(\"email\").Eq(\"pacino@al.com\"),\n\t\t\t\t),\n\t\t\t)).\n\t\tBuild(db.Dialect())\n\nfmt.Println(sel.SQL())\nfmt.Println(sel.Bindings())", "language": "go" } ] } [/block] The output would be the following; [block:code] { "codes": [ { "code": "SELECT id, email\nFROM user\nWHERE (user.name = ? AND (user.email = ? OR user.email = ?));\n[Al Pacino al@pacino.com pacino@al.com]", "language": "sql" } ] } [/block] [block:api-header] { "type": "basic", "title": "Using Joins" } [/block] Let's retrieve user sessions by using inner join; [block:code] { "codes": [ { "code": "sel := qb.\n\t\tSelect(\n\t\t\tsessions.C(\"id\"),\n\t\t\tsessions.C(\"user_id\"),\n\t\t\tsessions.C(\"created_at\"),\n\t\t\tsessions.C(\"expires_at\"),\n\t\t).\n\t\tFrom(sessions).\n\t\tInnerJoin(users, sessions.C(\"user_id\"), users.C(\"id\")).\n\t\tWhere(sessions.C(\"user_id\").Eq(\"3af82cdc-4d21-473b-a175-cbc3f9119eda\")).\n\t\tBuild(db.Dialect())\n\nfmt.Println(sel.SQL())\nfmt.Println(sel.Bindings())", "language": "go" } ] } [/block] The output would be the following; [block:code] { "codes": [ { "code": "SELECT session.id, session.user_id, session.created_at, session.expires_at\nFROM session\nINNER JOIN user ON session.user_id = user.id\nWHERE session.user_id = ?;\n[3af82cdc-4d21-473b-a175-cbc3f9119eda]", "language": "sql" } ] } [/block] There are several types of joins that can be made in qb. Here's the following join table; [block:parameters] { "data": { "h-0": "Join", "h-1": "Description", "0-0": "InnerJoin(table TableElem, fromCol ColumnElem, col ColumnElem)", "0-1": "Performs an INNER JOIN", "1-0": "LeftJoin(table TableElem, fromCol ColumnElem, col ColumnElem)", "2-0": "RightJoin(table TableElem, fromCol ColumnElem, col ColumnElem)", "3-0": "CrossJoin(table TableElem)", "1-1": "Performs a LEFT JOIN", "2-1": "Performs a RIGHT JOIN", "3-1": "Performs a CROSS JOIN" }, "cols": 2, "rows": 4 } [/block] [block:callout] { "type": "warning", "title": "Beware about dialects' join supports", "body": "These join functions doesn't know about dialect's driver(sqlite, mysql, postgres). Therefore, for instance, you need to know if sqlite have right join support. This issue will be addressed in further releases of qb." } [/block] As you might notice, CrossJoin (Cartesian Product) does not require column joining. Therefore, it has only the table parameter.