func TestSetFirstResult(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.SetFirstResult(10)
test.Fatal(t, qb.GetState(), db.Dirty)
test.Fatal(t, qb.GetFirstResult(), 10)
}
func TestDeleteWithoutAlias(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Delete("users")
test.Fatal(t, db.Delete, qb.GetType())
test.Fatal(t, "DELETE FROM users", qb.String())
}
func TestBuilderSelect(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("u.id").
From("users", "u")
test.Fatal(t, qb.String(), "SELECT u.id FROM users u")
}
func TestSelectAllWithoutTableAlias(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("*").
From("users")
test.Fatal(t, qb.String(), "SELECT * FROM users")
}
func TestUpdateWithoutAlias(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Update("users").
Set("foo", "?").
Set("bar", "?")
test.Fatal(t, qb.String(), "UPDATE users SET foo = ?, bar = ?")
}
func TestSelectOrderBy(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("u.*", "p.*").
From("users", "u").
OrderBy("u.name")
test.Fatal(t, qb.String(), "SELECT u.*, p.* FROM users u ORDER BY u.name ASC")
}
func TestUpdateWhere(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Update("users", "u").
Set("u.foo", "?").
Where("u.foo = ?")
test.Fatal(t, qb.String(), "UPDATE users u SET u.foo = ? WHERE u.foo = ?")
}
func TestDeleteWhere(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Delete("users", "u").
Where("u.foo = ?")
test.Fatal(t, "DELETE FROM users u WHERE u.foo = ?", qb.String())
}
func TestBuilderSelectWithJoin(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("u.*", "p.*").From("users", "u").
Join("u", "phones", "p", Eq("p.user_id", "u.id"))
test.Fatal(t, qb.String(), "SELECT u.*, p.* FROM users u JOIN phones p ON p.user_id = u.id")
}
func TestBuilderSelectWithWhere(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("u.id").
From("users", "u").
Where(And(Eq("u.nickname", "?")))
test.Fatal(t, qb.String(), "SELECT u.id FROM users u WHERE u.nickname = ?")
}
func TestSelectGroupBy(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("u.*", "p.*").
From("users", "u").
GroupBy("u.id")
test.Fatal(t, qb.String(), "SELECT u.*, p.* FROM users u GROUP BY u.id")
}
func TestSelectWithOrOrWhereConditions(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("u.*", "p.*").
From("users", "u").
OrWhere("u.username = ?").
OrWhere("u.name = ?")
test.Fatal(t, qb.String(), "SELECT u.*, p.* FROM users u WHERE (u.username = ?) OR (u.name = ?)")
}
func TestSelectMultipleFrom(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("u.*").
AddSelect("p.*").
From("users", "u").
From("phonenumbers", "p")
test.Fatal(t, qb.String(), "SELECT u.*, p.* FROM users u, phonenumbers p")
}
func TestGetState(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
test.Fatal(t, qb.GetState(), db.Clean)
qb.Select("u.*").From("users", "u")
test.Fatal(t, qb.GetState(), db.Dirty)
qb.String()
test.Fatal(t, qb.GetState(), db.Clean)
}
func TestUpdate(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Update("users", "u").
Set("u.foo", "?").
Set("u.bar", "?")
test.Fatal(t, qb.GetType(), db.Update)
test.Fatal(t, qb.String(), "UPDATE users u SET u.foo = ?, u.bar = ?")
}
func TestSelectOrHavingOrHaving(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("u.*", "p.*").
From("users", "u").
GroupBy("u.id").
OrHaving("u.name = ?").
OrHaving("u.username = ?")
test.Fatal(t, qb.String(), "SELECT u.*, p.* FROM users u GROUP BY u.id HAVING (u.name = ?) OR (u.username = ?)")
}
func TestInsertValuesSetValue(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Insert("users").
SetValue("foo", "?").
SetValue("bar", "?")
test.Fatal(t, db.Insert, qb.GetType())
test.Fatal(t, "INSERT INTO users (foo, bar) VALUES(?, ?)", qb.String())
}
func TestComplexSelectWithSomeTableAliases(t *testing.T) {
// t.Skip("Skipped until the order of joins satisfies the doctrine specification")
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("u.id").
From("users", "u").
From("articles").
InnerJoin("u", "permissions", "p", "p.user_id = u.id").
InnerJoin("articles", "comments", "c", "c.article_id = articles.id")
test.Fatal(t, qb.String(),
"SELECT u.id FROM users u INNER JOIN permissions p ON p.user_id = u.id, articles INNER JOIN comments c ON c.article_id = articles.id")
}
// qb := db.NewQueryBuilder(connection)
// qb.Select("id", "name")
// .From("users")
// .where("awesome=9001")
// test.Fatal(t,qb.String(),"SELECT id, name FROM users WHERE awesome=9001")
// }
func TestComplexSelectWithoutTableAliases(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("DISTINCT users.id").
From("users").
From("articles").
InnerJoin("articles", "comments", "c", "c.article_id = articles.id").
InnerJoin("users", "permissions", "p", "p.user_id = users.id").
Where("users.id = articles.user_id").
AndWhere("p.read = 1")
test.FatalWithDiff(t, qb.String(),
"SELECT DISTINCT users.id FROM users INNER JOIN permissions p ON p.user_id"+
" = users.id, articles INNER JOIN comments c ON c.article_id = articles.id "+
"WHERE (users.id = articles.user_id) AND (p.read = 1)")
}
/**
* @group DBAL-172
*/
// func TestReferenceJoinFromJoin (t *testing.T) {
// qb := db.NewQueryBuilder(connection)
// qb.Select("COUNT(DISTINCT news.id)")
// .From("cb_newspages", "news")
// .InnerJoin("news", "nodeversion", "nv", "nv.refId = news.id AND nv.refEntityname=\"News\'')
// .InnerJoin("invalid", "nodetranslation", "nt", "nv.nodetranslation = nt.id")
// .InnerJoin("nt", "node", "n", "nt.node = n.id")
// .where("nt.lang = :lang AND n.deleted != 1")
// this.setExpectedException("Doctrine\DBAL\Query\QueryException", "The given alias "invalid" is not part of any FROM or JOIN clause table. The currently registered aliases are: news, nv.")
// test.Fatal(t,qb.String(),'', qb.getSQL())
// }
// /**
// * @group DBAL-172
// */
// func TestSelectFromMasterWithWhereOnJoinedTables (t *testing.T) {
// qb := db.NewQueryBuilder(connection)
// qb.Select("COUNT(DISTINCT news.id)")
// .From("newspages", "news")
// .InnerJoin("news", "nodeversion", "nv", "nv.refId = news.id AND nv.refEntityname="Entity\\News"")
// .InnerJoin("nv", "nodetranslation", "nt", "nv.nodetranslation = nt.id")
// .InnerJoin("nt", "node", "n", "nt.node = n.id")
// .where("nt.lang = ?")
// .andWhere("n.deleted = 0")
// test.Fatal(t,qb.String(),"SELECT COUNT(DISTINCT news.id) FROM newspages news INNER JOIN nodeversion nv ON nv.refId = news.id AND nv.refEntityname="Entity\\News" INNER JOIN nodetranslation nt ON nv.nodetranslation = nt.id INNER JOIN node n ON nt.node = n.id WHERE (nt.lang = ?) AND (n.deleted = 0)", qb.getSQL())
// }
// /**
// * @group DBAL-442
// */
// func TestSelectWithMultipleFromAndJoins (t *testing.T) {
// qb := db.NewQueryBuilder(connection)
// qb.Select("DISTINCT u.id")
// .From("users", "u")
// .From("articles", "a")
// .InnerJoin("u", "permissions", "p", "p.user_id = u.id")
// .InnerJoin("a", "comments", "c", "c.article_id = a.id")
// .where("u.id = a.user_id")
// .andWhere("p.read = 1")
// test.Fatal(t,qb.String(),"SELECT DISTINCT u.id FROM users u INNER JOIN permissions p ON p.user_id = u.id, articles a INNER JOIN comments c ON c.article_id = a.id WHERE (u.id = a.user_id) AND (p.read = 1)", qb.getSQL())
// }
// /**
// * @group DBAL-774
// */
func TestSelectWithJoinsWithMultipleOnConditionsParseOrder(t *testing.T) {
t.Skip("join alias should also match alias in other joins if not found in from")
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
qb.Select("a.id").
From("table_a", "a").
Join("a", "table_b", "b", "a.fk_b = b.id").
Join("b", "table_c", "c", "c.fk_b = b.id AND b.language = ?").
Join("a", "table_d", "d", "a.fk_d = d.id").
Join("c", "table_e", "e", "e.fk_c = c.id AND e.fk_d = d.id")
test.FatalWithDiff(t, qb.String(),
"SELECT a.id "+
"FROM table_a a "+
"INNER JOIN table_b b ON a.fk_b = b.id "+
"INNER JOIN table_d d ON a.fk_d = d.id "+
"INNER JOIN table_c c ON c.fk_b = b.id AND b.language = ? "+
"INNER JOIN table_e e ON e.fk_c = c.id AND e.fk_d = d.id")
}
func TestEmptyAddSelect(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
test.Fatal(t, qb.GetType(), db.Select)
}
func TestGetConnection(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection)
test.Fatal(t, connection, qb.GetConnection())
}
func TestEmptyUpdate(t *testing.T) {
connection := NewTestConnection(t)
qb := db.NewQueryBuilder(connection).Update("")
test.Fatal(t, qb.GetType(), db.Update)
}