package org.tests.query.other;
import io.ebean.BaseTestCase;
import io.ebean.Ebean;
import io.ebean.Query;
import org.tests.model.basic.Contact;
import org.tests.model.basic.Customer;
import org.tests.model.basic.ResetBasicData;
import org.tests.inherit.ChildA;
import org.tests.inherit.Data;
import org.tests.inherit.EUncle;
import org.junit.Ignore;
import org.junit.Test;
import java.sql.Date;
import java.util.List;
import static org.assertj.core.api.Assertions.assertThat;
public class TestQuerySingleAttribute extends BaseTestCase {
@Test
public void exampleUsage() {
ResetBasicData.reset();
List<String> names =
Ebean.find(Customer.class)
.setDistinct(true)
.select("name")
.where().eq("status", Customer.Status.NEW)
.orderBy().asc("name")
.setMaxRows(100)
.findSingleAttributeList();
assertThat(names).isNotNull();
}
@Test
public void exampleUsage_otherType() {
ResetBasicData.reset();
List<Date> dates =
Ebean.find(Customer.class)
.setDistinct(true)
.select("anniversary")
.where().isNotNull("anniversary")
.orderBy().asc("anniversary")
.findSingleAttributeList();
assertThat(dates).isNotNull();
}
@Test
public void withOrderBy() {
Query<Customer> query =
Ebean.find(Customer.class)
.setDistinct(true)
.select("name")
.where().eq("status", Customer.Status.NEW)
.orderBy().asc("name")
.setMaxRows(100);
query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t0.name from o_customer t0 where t0.status = ? order by t0.name ");
}
@Test
public void basic() {
ResetBasicData.reset();
Query<Customer> query = Ebean.find(Customer.class).select("name");
List<String> names = query.findSingleAttributeList();//String.class);
assertThat(sqlOf(query)).contains("select t0.name from o_customer t0");
assertThat(names).isNotNull();
}
@Test
public void distinctAndWhere() {
ResetBasicData.reset();
Query<Customer> query = Ebean.find(Customer.class)
.setDistinct(true)
.select("name")
.where().eq("status", Customer.Status.NEW)
.query();
List<String> names = query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t0.name from o_customer t0 where t0.status = ? ");
assertThat(names).isNotNull();
}
@Test
public void distinctWhereWithJoin() {
ResetBasicData.reset();
Query<Customer> query = Ebean.find(Customer.class)
.setDistinct(true)
.select("name")
.where().eq("status", Customer.Status.NEW)
.istartsWith("billingAddress.city", "auck")
.query();
List<String> names = query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t0.name from o_customer t0 left join o_address t1 on t1.id = t0.billing_address_id where t0.status = ? and lower(t1.city) like ?");
assertThat(names).isNotNull();
}
@Test
public void queryPlan_expect_differentPlans() {
ResetBasicData.reset();
Query<Customer> query = Ebean.find(Customer.class).select("name");
query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select t0.name from o_customer t0");
Query<Customer> query2 = Ebean.find(Customer.class).select("name");
query2.findList();
assertThat(sqlOf(query2, 1)).contains("select t0.id, t0.name from o_customer t0");
}
@Test
public void distinctOnIdProperty(){
Query<Customer> query = Ebean.find(Customer.class)
.setDistinct(true)
.select("id")
.setMaxRows(100);
List<String> ids = query.findSingleAttributeList();
if (isSqlServer()) {
assertThat(sqlOf(query)).contains("select distinct top 100 t0.id from o_customer t0");
} else {
assertThat(sqlOf(query)).contains("select distinct t0.id from o_customer t0 limit 100");
}
assertThat(ids).isNotEmpty();
}
@Test
public void distinctWithFetch() {
ResetBasicData.reset();
Query<Customer> query = Ebean.find(Customer.class)
.setDistinct(true)
.fetch("billingAddress","city")
.setMaxRows(100);
List<String> cities = query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t1.city from o_customer t0 left join o_address t1 on t1.id = t0.billing_address_id");
assertThat(cities).contains("Auckland").containsNull();
}
@Test
public void distinctWithCascadedFetch() {
ResetBasicData.reset();
Query<Contact> query = Ebean.find(Contact.class)
.setDistinct(true)
.fetch("customer.billingAddress","city");
List<String> cities = query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t2.city from contact t0 join o_customer t1 on t1.id = t0.customer_id left join o_address t2 on t2.id = t1.billing_address_id");
assertThat(cities).contains("Auckland").containsNull();
}
@Test
public void distinctSelectOnInheritedBean() {
ResetBasicData.reset();
Query<ChildA> query = Ebean.find(ChildA.class)
.setDistinct(true)
.select("more")
.setMaxRows(100);
query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t0.more from rawinherit_parent t0 where t0.type = 'A' limit 100");
}
@Test
public void distinctFetchManyToOneInheritedBean() {
ResetBasicData.reset();
Query<EUncle> query = Ebean.find(EUncle.class)
.setDistinct(true)
.fetch("parent","more")
.setMaxRows(100);
query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t1.more from rawinherit_uncle t0 join rawinherit_parent t1 on t1.id = t0.parent_id and t1.type in ('A','B')");
}
// hmm - same problem when not using distinct
@Test
public void findSingleOnIdProperty(){
Query<Customer> query = Ebean.find(Customer.class)
.select("id")
.setMaxRows(100);
List<String> ids = query.findSingleAttributeList();
if (isSqlServer()) {
assertThat(sqlOf(query)).contains("select top 100 t0.id from o_customer t0");
} else {
assertThat(sqlOf(query)).contains("select t0.id from o_customer t0 limit 100");
}
assertThat(ids).isNotEmpty();
}
@Test
public void findSingleWithFetch() {
ResetBasicData.reset();
Query<Customer> query = Ebean.find(Customer.class)
.fetch("billingAddress","city")
.setMaxRows(100);
List<String> cities = query.findSingleAttributeList();
assertThat(cities).contains("Auckland").containsNull();
assertThat(sqlOf(query)).contains("select t1.city from o_customer t0 left join o_address t1 on t1.id = t0.billing_address_id");
}
@Test
public void findSingleSelectOnInheritedBean() {
ResetBasicData.reset();
Query<ChildA> query = Ebean.find(ChildA.class)
.select("more")
.setMaxRows(100);
query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select t0.more from rawinherit_parent t0 where t0.type = 'A' limit 100");
}
@Test
public void findSingleFetchManyToOneInheritedBean() {
ResetBasicData.reset();
Query<EUncle> query = Ebean.find(EUncle.class)
.fetch("parent","more")
.setMaxRows(100);
query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select t1.more from rawinherit_uncle t0 join rawinherit_parent t1 on t1.id = t0.parent_id and t1.type in ('A','B')");
}
@Test
public void findSingleFetchManyToOneInheritedBean_viaEbeanServer() {
ResetBasicData.reset();
Query<EUncle> query = Ebean.find(EUncle.class)
.fetch("parent","more")
.setMaxRows(100);
Ebean.getDefaultServer().findSingleAttributeList(query, null);
assertThat(sqlOf(query)).contains("select t1.more from rawinherit_uncle t0 join rawinherit_parent t1 on t1.id = t0.parent_id and t1.type in ('A','B')");
}
@Test
@Ignore //don't know if ebean can handle this on many to many, as this means that the cartesian product is generated
public void distinctFetchManyToManyInheritedBean() {
ResetBasicData.reset();
Query<Data> query = Ebean.find(Data.class)
.setDistinct(true)
.fetch("parents","more")
.setMaxRows(100);
query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t0.more from rawinherit_data t0 "
+ "join rawinherit_parent_rawinherit_data t1 on t0.id = t1.rawinherit_data_id "
+ "join parent t2 on t1.rawinherit_parent_id = t2.id");
}
@Test
public void distinctWithOrderByPk() {
ResetBasicData.reset();
Query<Contact> query = Ebean.find(Contact.class)
.setDistinct(true)
.select("customer")
.orderBy().desc("customer");
query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t0.customer_id from contact t0 order by t0.customer_id desc");
}
@Test
public void distinctWithCascadedFetchOrderByPk() {
ResetBasicData.reset();
Query<Contact> query = Ebean.find(Contact.class)
.setDistinct(true)
.fetch("customer","billingAddress")
.orderBy().desc("customer.billingAddress");
query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t1.billing_address_id from contact t0 join o_customer t1 on t1.id = t0.customer_id order by t1.billing_address_id desc");
}
@Test
public void distinctWithOrderByPkAndQuery() {
ResetBasicData.reset();
Query<Contact> query = Ebean.find(Contact.class)
.setDistinct(true)
.fetch("customer","billingAddress")
.where().eq("customer.billingAddress.city", "Auckland")
.orderBy().desc("customer.billingAddress.id");
List<Integer> ids = query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t1.billing_address_id from contact t0 "
+ "join o_customer t1 on t1.id = t0.customer_id " // two spaces!
+ "left join o_address t2 on t2.id = t1.billing_address_id "
+ "where t2.city = ? "
+ "order by t1.billing_address_id desc");
}
@Test
public void distinctWithCascadedFetchOrderByPkAndQuery() {
ResetBasicData.reset();
Query<Contact> query = Ebean.find(Contact.class)
.setDistinct(true)
.fetch("customer","billingAddress")
.where().eq("customer.billingAddress.city", "Auckland")
.orderBy().desc("customer.billingAddress.id");
List<Short> ids = query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t1.billing_address_id from contact t0 "
+ "join o_customer t1 on t1.id = t0.customer_id "
+ "left join o_address t2 on t2.id = t1.billing_address_id "
+ "where t2.city = ? "
+ "order by t1.billing_address_id desc");
}
@Test
public void distinctWithCascadedFetchOrderByPkAndQuery3() {
ResetBasicData.reset();
Query<Contact> query = Ebean.find(Contact.class)
.setDistinct(true)
.fetch("customer","billingAddress")
.where().eq("customer.billingAddress.city", "Auckland")
.orderBy().desc("customer.billingAddress.id");
List<Integer> ids = query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t1.billing_address_id from contact t0 "
+ "join o_customer t1 on t1.id = t0.customer_id "
+ "left join o_address t2 on t2.id = t1.billing_address_id "
+ "where t2.city = ? "
+ "order by t1.billing_address");
}
@Test
public void distinctWithCascadedFetchOrderByPkAndQuery2() {
ResetBasicData.reset();
Query<Contact> query = Ebean.find(Contact.class)
.setDistinct(true)
.fetch("customer","billingAddress")
.where().eq("customer.shippingAddress.city", "Auckland") // query on shippingAddress
.orderBy().desc("customer.billingAddress.id");
List<Short> ids = query.findSingleAttributeList();
assertThat(sqlOf(query)).contains("select distinct t1.billing_address_id from contact t0 "
+ "join o_customer t1 on t1.id = t0.customer_id "
+ "left join o_address t2 on t2.id = t1.shipping_address_id "
+ "where t2.city = ? "
+ "order by t1.billing_address_id desc");
}
}