How to do left join in korzh easy query builder?

Question

I have implemented korzh easy query builder in a web application using asp.net mvc and sql server, so customer can make their custom queries and do data analysis. I got stuck in scenario where I have some tables

Customer (CustId PK,Name,FriendId FK AddressBook.Id, BossId FK AddressBookId)
AddressBook (Id,Name)

the result I want to get with easy query builder is

select Cust.Name , frnd.Name as friend, bos.Name from Customer as Cust
left join AddressBook as Frnd on Cust.FriendId = frnd.Id
left join AddressBook as bos on Cust.BossId = bos.Id

I can get this result in sql server easily, but in Easy Query builder I don't have choice of join (left or right or inner), tables get inner join automatically if they have a foreign key relationship in between them. In this case I am not able to get the friends and boss together.

So my question are : 1. How to get data from table If the same table has multiple relationship the other table like Customer and AddressBook?

How to do left join in Easy Query builder?

Does it allow you to create a view such as Vw_AddressBook? so you can join the table and join the view in one query. — Wendy, Jun 14 '17 at 13:02
We can create view in sql database database, and it will show up in easy query builder, we can select column from view in query builder. But we can't select the combination of columns from table and views in one query with easy query builder because they don't have foreign key relationship in between them. Easy query join tables on the base of foreign key relationship automatically. If you select tow different tables or views which don't have any FK relation, easy query will throw an error that these tables are not related to each other. — R K Sharma, Jun 14 '17 at 13:46
I have no idea what is Korzh. Never used one. Is possible to define a view in Korzh? Or Is possible use same column when create a view, ID AS BossId, so adding view to query, it joins automatically. Or is possible to add condition where ID=BossId? You need to explore how to use Korzh more. — Wendy, Jun 14 '17 at 14:42

score 2 · Answer 1 · edited Aug 16 '18 at 17:33

I am posting this chat because it may can help someone :-

Here is communication with support team: Me: We are using this one http://demo.easyquerybuilder.com/asp-net-mvc/ And we are loading data from sql database, using this code:

var eqService = new EqServiceProviderDb();

eqService.Paging.Enabled = true;
eqService.StoreQueryInSession = true;
eqService.SessionGetter = key => Session[key];
eqService.SessionSetter = (key, value) => Session[key] = value;

Korzh.EasyQuery.DataGates.SqlClientGate sqlGate = new Korzh.EasyQuery.DataGates.SqlClientGate();
eqService.ModelLoader = (model, modelName) =>
{
    model.Clear();
    sqlGate.ConnectionString = ConfigurationManager.AppSettings["our connection string to sql db here"].ToString();
    sqlGate.Connected = true;
    model.FillByDbGate(sqlGate, FillModelOptions.Default);
};
//For T-SQL use these settings
//  var connection = context.Database.Connection;
eqService.Formats.SetDefaultFormats(FormatType.MsSqlServer);
eqService.Formats.UseSchema = false;  //schema is not used in SQL Server CE
eqService.Connection = sqlGate.Connection;
eqService.Connection.ConnectionString = ConfigurationManager.AppSettings["our connection string to sql db here"].ToString();
//eqService.Connection.Open();

As per our scenario we have AddressBook table and customer table. Customer table has two columns BossId and FriendId having FK relationship with "Addressbook > Id" column. We want all customer’s record with friends and boss regardless customer has friend or boss. We can get the required result by using left join in sql query as:

" Select cust.Name as CustomerName,frnd.Name as Friends,bos.Name as Boss from Customer as cust left join AddressBook as frnd on cust.FriendId=frnd.Id Left join AddressBook as Bos on cust.BossId=Bos.Id "

Support team: So, you just need to add the following piece of code right after

model.FillByDbGate:

var tbl1 = model.Tables.FindByName("AddressBook");
var tbl2 = model.Tables.FindByName("Customer");
var link = model.Links.FindByTables(tbl1, tbl2);
link.LnkType = TableLink.LinkType.Left;

Me: Thanks for you response, I understand this is how we can place left join in tow tables in c# code and now if I will try to get data from two tables it will apply left join on them, but how can I choose the join (left/right/inner) dynamically from the UI page? Does easy query provide any option to choose the type of join when we are trying to get data from two tables? For instance in my last scenario what if I want only get those customer's name and their boss's name who has Boss (here I have to apply inner join).

As in my previous sql query

Select cust.Name as CustomerName,frnd.Name as Friends,bos.Name as Boss from Customer as cust left join AddressBook as frnd on cust.FriendId=frnd.Id Left join AddressBook as Bos on cust.BossId=Bos.Id

I am applying left join tow times on the same table to get the frnd and Bos.

left join AddressBook as frnd on cust.FriendId=frnd.Id Left join AddressBook as Bos on cust.BossId=Bos.Id

is it possible to define the join condition dynamically from UI page in easy query?

Support team: Unfortunately, we don't have such UI controls/widgets now, so you will need to implement it by yourself.

We can schedule this functionality for future releases but I can't give the exact timeframe for implementation now.

Summary: so this feature is not there in this tool, bad luck for me.

How to do left join in korzh easy query builder?

1 Answers1