public void SelectWithJoinNestedSubquery()
{
var subq1 = Builders <MyModel> .Select(x => x.Id)
.Where(x => x.Id == 1)
.AsSubquery();
var subq = BuildersJoin.FromMany <MyModel, MyModel2>((m, m2) => m.Id == m2.Id)
.Map((m, m2) => new {
M = m,
M2 = m2,
})
.Select(x => x.M.Name)
.Where(x => x.M2.Id == 1 && x.M.Id.PgIn(subq1))
.AsSubquery();
var q = Builders <MyModel2> .Select(x => x.Name1)
.Where(x => x.Name1.PgIn(subq));
Utils.AssertRawQuery(q, @"
SELECT name1
FROM ""public"".model2
WHERE (name1) IN (
SELECT t0.""name""
FROM model t0
LEFT JOIN ""public"".model2 t1 ON ((t0.""id"") = (t1.""id""))
WHERE ((t1.""id"") = (1)) AND ((t0.""id"") IN (SELECT ""id"" FROM model WHERE (""id"") = (1)))
)");
}
public void SelectMultiple3Tables()
{
var q = BuildersJoin.FromMany <MyModel, MyModel2, MyModel3>(
(a, b) => a.Id == b.ModelId,
(a, b, c) => a.Id == c.ModelId)
.Map((a, b, c) => new {
T0 = a,
T1 = b,
T2 = c,
})
.Select(x => new {
x.T0.Id,
x.T1.Name1,
});
Utils.AssertRawQueryWithAliases(q, @"SELECT t0.""id"",t1.name1 FROM model t0 LEFT JOIN ""public"".model2 t1 ON ((t0.""id"") = (t1.model_id)) LEFT JOIN ""public"".model3 t2 ON ((t0.""id"") = (t2.model_id))");
var q2 = BuildersJoin.FromMany <MyModel, MyModel2, MyModel3>(
(a, b) => a.Id == b.ModelId,
(a, b, c) => a.Id == c.ModelId)
.Map((a, b, c) => new {
T0 = b,
T1 = a,
T2 = c,
})
.Select(x => new {
x.T0.Id,
x.T1.Name,
});
Utils.AssertRawQueryWithAliases(q2, @"SELECT t1.""id"",t0.""name"" FROM model t0 LEFT JOIN ""public"".model2 t1 ON ((t0.""id"") = (t1.model_id)) LEFT JOIN ""public"".model3 t2 ON ((t0.""id"") = (t2.model_id))");
}
public void SelectMultipleReturnScalar()
{
var q1 = BuildersJoin.FromMany <MyModel, MyModel2>((a, b) => a.Id == b.Id)
.Map((a, b) => new {
M1 = a,
M2 = b,
})
.Select(x => x.M1.Id);
Utils.AssertRawQueryWithAliases(q1, @"
SELECT
t0.""id""
FROM
model t0 LEFT JOIN ""public"".model2 t1 ON ((t0.""id"") = (t1.""id""))");
var q2 = BuildersJoin.FromMany <MyModel, MyModel2, MyModel3>(
(a, b) => a.Id == b.Id,
(a, b, c) => a.Id == c.Id)
.Map((a, b, c) => new {
M1 = a,
})
.Select(x => x.M1.Id);
Utils.AssertRawQueryWithAliases(q2, @"
SELECT
t0.""id""
FROM
model t0
LEFT JOIN ""public"".model2 t1 ON ((t0.""id"") = (t1.""id""))
LEFT JOIN ""public"".model3 t2 ON ((t0.""id"") = (t2.""id""))");
var q3 = BuildersJoin.FromMany <MyModel, MyModel2, MyModel3, MyModel2>(
(a, b) => a.Id == b.Id,
(a, b, c) => a.Id == c.Id,
(a, b, c, d) => a.Id == d.Id)
.Map((a, b, c, d) => new {
M1 = a,
})
.Select(x => x.M1.Id);
Utils.AssertRawQueryWithAliases(q3, @"
SELECT
t0.""id""
FROM
model t0
LEFT JOIN ""public"".model2 t1 ON ((t0.""id"") = (t1.""id""))
LEFT JOIN ""public"".model3 t2 ON ((t0.""id"") = (t2.""id""))
LEFT JOIN ""public"".model2 t3 ON ((t0.""id"") = (t3.""id""))");
}
public void SelectMultipleReturnSingleTable()
{
var q = BuildersJoin.FromMany <MyModel, MyModel>((a, b) => a.Id == b.Id)
.Map((a, b) => new {
M1 = a,
M2 = b,
})
.Select(x => x.M1);
Utils.AssertRawQueryWithAliases(q, @"
SELECT
t0 IS NULL,t0.""id"",t0.""name"",t0.list_string,t0.list_string2,(t0.""enum"")::text,(t0.list_enum)::text[],(t0.enum2)::text,t0.enum_text,t0.""date"",t0.""time"",t0.datetime,t0.json_object1,t0.json_model,t0.json_array1,t0.bool,t0.""binary"",t0.private_int,t0.val_f32,t0.val_f64
FROM
model t0 LEFT JOIN model t1 ON ((t0.""id"") = (t1.""id""))");
}
public void SelectMultipleNewClass()
{
var q = BuildersJoin.FromMany <MyModel, MyModel2>((a, b) => a.Id == b.ModelId)
.Map((a, b) => new JoinClass {
M1 = a,
M2 = b,
})
.Select(x => new {
M1_name = x.M1.Name,
M2_name = x.M2.Name1,
});
Utils.AssertRawQueryWithAliases(q, @"
SELECT
t0.""name"",t1.name1
FROM
model t0 LEFT JOIN ""public"".model2 t1 ON ((t0.""id"") = (t1.model_id))");
}
public void SelectMultipleAll()
{
var q = BuildersJoin.FromMany <MyModel, MyModel2>((a, b) => a.Id == b.ModelId)
.Map((a, b) => new {
M1 = a,
M2 = b,
})
.Select()
.Where(x => x.M2 != null && x.M1.Name == "Q");
Utils.AssertRawQueryWithAliases(q, @"
SELECT
t0 IS NULL,t0.""id"",t0.""name"",t0.list_string,t0.list_string2,(t0.""enum"")::text,(t0.list_enum)::text[],(t0.enum2)::text,t0.enum_text,t0.""date"",t0.""time"",t0.datetime,t0.json_object1,t0.json_model,t0.json_array1,t0.bool,t0.""binary"",t0.private_int,t0.val_f32,t0.val_f64,t1 IS NULL,t1.""id"",t1.name1,t1.model_id
FROM
model t0 LEFT JOIN ""public"".model2 t1 ON ((t0.""id"") = (t1.model_id))
WHERE
(NOT((t1) IS NULL)) AND ((t0.""name"") = ('Q'))");
}
public void SelectMultipleNew()
{
var q = BuildersJoin.FromMany <MyModel, MyModel2>((a, b) => a.Id == b.ModelId)
.Map((a, b) => new {
M1 = a,
M2 = b,
})
.Select(x => new {
M1 = x.M1,
M2_name = x.M2.Name1,
M3_calc = x.M2.Id * 2,
});
Utils.AssertRawQueryWithAliases(q, @"
SELECT
t0 IS NULL,t0.""id"",t0.""name"",t0.list_string,t0.list_string2,(t0.""enum"")::text,(t0.list_enum)::text[],(t0.enum2)::text,t0.enum_text,t0.""date"",t0.""time"",t0.datetime,t0.json_object1,t0.json_model,t0.json_array1,t0.bool,t0.""binary"",t0.private_int,t0.val_f32,t0.val_f64,t1.name1,(t1.""id"") * (2)
FROM
model t0 LEFT JOIN ""public"".model2 t1 ON ((t0.""id"") = (t1.model_id))");
}
public void SelectMultipleSelfJoin()
{
var q = BuildersJoin.FromMany <MyModel, MyModel>((a, b) => a.Id == b.Id)
.Map((a, b) => new {
M1 = b,
M2 = a,
})
.Select(x => new {
A = x.M1,
B = x.M2,
})
.Where(x => x.M1 != null && x.M2 != null);
Utils.AssertRawQueryWithAliases(q, @"
SELECT
t1 IS NULL,t1.""id"",t1.""name"",t1.list_string,t1.list_string2,(t1.""enum"")::text,(t1.list_enum)::text[],(t1.enum2)::text,t1.enum_text,t1.""date"",t1.""time"",t1.datetime,t1.json_object1,t1.json_model,t1.json_array1,t1.bool,t1.""binary"",t1.private_int,t1.val_f32,t1.val_f64,t0 IS NULL,t0.""id"",t0.""name"",t0.list_string,t0.list_string2,(t0.""enum"")::text,(t0.list_enum)::text[],(t0.enum2)::text,t0.enum_text,t0.""date"",t0.""time"",t0.datetime,t0.json_object1,t0.json_model,t0.json_array1,t0.bool,t0.""binary"",t0.private_int,t0.val_f32,t0.val_f64
FROM
model t0 LEFT JOIN model t1 ON ((t0.""id"") = (t1.""id""))
WHERE
(NOT((t1) IS NULL)) AND (NOT((t0) IS NULL))");
}
public void SelectMultiple4Tables()
{
var q = BuildersJoin.FromMany <MyModel, MyModel2, MyModel3, MyModel3>(
(a, b) => a.Id == b.ModelId,
(a, b, c) => a.Id == c.ModelId,
(a, b, c, d) => a.Id == d.ModelId)
.Map((a, b, c, d) => new {
T0 = a,
T1 = b,
T2 = c,
T3 = d,
})
.Select(x => 0);
Utils.AssertRawQueryWithAliases(q, @"
SELECT 0
FROM model t0
LEFT JOIN ""public"".model2 t1 ON ((t0.""id"") = (t1.model_id))
LEFT JOIN ""public"".model3 t2 ON ((t0.""id"") = (t2.model_id))
LEFT JOIN ""public"".model3 t3 ON ((t0.""id"") = (t3.model_id))
");
}
