Hi,
I am trying to write a query for the filter component for a custom search so I have to tables that are related Items and suppliers and what I want is when I search for an item I should get all related items from different suppliers. The supplier numbers are not registered in any table they are just strings to link with the main items.
Item:
id,
name,
supplier
…
supplierItems:
id,
item,
supplier
so in the search say for example I have the following data in my tables
items:
2321, item1, 546
5432, item2, 657
7677, item3, 898
6621, item4, 546
supplierItems:
1, 2321, 657
2, 5432, 546
3, 6621, 657
When I search for item 2321 the result should return me items: 2321, 5432, 6621 as all of them has a common supplier number either in the supplierItems table or in the property of the items table.
Kind regards,
Mohamed
Hi,
Is there a way to use the results of the filter as parameters in the query to get complex query results as explained above.
This is the query written in code was wondering if its possible to somehow use it as part of the filter to use its benefits by adding it as a custom method to be invoked by the filter in any way.
LoadContext<Item> loadContext = LoadContext.create(Item.class)
.setQuery(LoadContext
.createQuery("select e from cps$Item e left join e.suppliers s " +
"where lower(e.code) like lower(:code) " +
"or lower(e.supplier_number) like lower(:code) " +
"or lower(s.supplier) like lower(:code)")
.setParameter("code", "%"+code+"%"))
.setView("item-view");
List<Item> result = dataManager.loadList(loadContext);
Set<String> s = new HashSet<>();
for(Item i : result){
s.add(i.getSupplier_number());
}
for(String c : s){
try {
Item i = dataManager.load(Item.class).query("select e from cps$Item e where e.code = :code")
.parameter("code", c).view("item-view").one();
result.add(i);
for(ItemSupplierNumber supplier : i.getSuppliers()){
if(!supplier.getSupplier().equals("")) {
LoadContext<Item> loadContext2 = LoadContext.create(Item.class)
.setQuery(LoadContext
.createQuery("select e from cps$Item e left join e.suppliers s " +
"where lower(e.code) like lower(:code) " +
"or lower(e.supplier_number) like lower(:code) " +
"or lower(s.supplier) like lower(:code)")
.setParameter("code", "%" + supplier.getSupplier() + "%"))
.setView("item-view");
result.addAll(dataManager.loadList(loadContext2));
}
}
}catch (Exception e){
}
}
return result;