Can I use Exists as a condition in Editor .Net?

Mundaring

Hi,

I am trying to do something like this. The only part I am missing is the exists, also I am not sure if I can use the SQL function fncGetHREmployees.

How can I do the exists

select 
    sig.EvaluationId As ReviewId, eva.CreationDate, eva.DepartmentName, eva.PositionName, emp.FullName, eva.ManagerName, sig.ResponsibleId
from SignOff sig
    inner join  Evaluation eva
        on sig.EvaluationId = eva.Id
    inner join organization org 
        on eva.organizationid = org.id
    inner join employee emp 
        on org.Employeeid = emp.Id
where exists (select EmployeeId from fncGetHREmployees(10) fnc where sig.ResponsibleId = fnc.EmployeeId)    and 
    AdHoc = 0 and IsClosed = 0

At the moment I have got something like this.

 using (var dtDB = new DataTables.Database("sqlserver", conn))
            {
                var response = new Editor(dtDB, "Evaluation", "Id")
                    .Model<VMReviewsManagerHR>()    
                    .Field(new Field("Evaluation.DepartmentName")
                        .Validator(Validation.None())
                    )
                    .Field(new Field("Evaluation.PositionName")
                        .Validator(Validation.None())
                    )
                    .Field(new Field("Employee.FullName")
                        .Validator(Validation.None())
                    )
                    .Field(new Field("Evaluation.ManagerName")
                        .Validator(Validation.None())
                    )
                    .Field(new Field("SignOff.ResponsibleId")
                        .Validator(Validation.None())
                    )
                    .LeftJoin("Organization", "Evaluation.OrganizationId", "=", "Organization.Id")
                    .LeftJoin("Employee", "Organization.EmployeeId", "=", "Employee.Id")
                    .LeftJoin("SignOff", "Evaluation.Id", "=", "SignOff.EvaluationId")
                    .Where("Evaluation.AdHoc", "0")
                    .Where("Evaluation.IsClosed", "0")
                    .Where(q => q.Where(**EXISTS HERE**))
                    .Process(request)
                    .Data();

                return Json(response, JsonRequestBehavior.AllowGet);
            }

Thanks,

Wilson

allan

Hi Wilson,

This part of the documentation shows how this might be done. It isn't an EXISTS example, but the basic idea should still apply I think.

Something like this should work:

Where(q => q.Where("EXISTS", "select ...", "", false))

Let me know how you get on with that!

Allan

Mundaring

Hi Alla,

I did not work.

I finally set my controller like this. But I am getting the error message Incorrect syntax near the keyword 'select'.

 [AcceptVerbs(HttpVerbs.Get | HttpVerbs.Post)]
        public ActionResult DTReviewsManagerHR()
        {
            var request = HttpContext.Request.Form;
            //var x = HttpContext.Request.Params;

            using (var dtDB = new DataTables.Database("sqlserver", conn))
            {
                var response = new Editor(dtDB, "SignOff", "Id")//Application.
                    
                    .Model<VMReviewsManagerHR>()
                    .Field(new Field("Evaluation.DepartmentName")
                        .Validator(Validation.None())
                    )
                    .Field(new Field("Evaluation.PositionName")
                        .Validator(Validation.None())
                    )
                    .Field(new Field("Employee.FullName")
                        .Validator(Validation.None())
                    )
                    .Field(new Field("Evaluation.ManagerName")
                        .Validator(Validation.None())
                    )
                    .Field(new Field("SignOff.ResponsibleId")
                        .Validator(Validation.None())
                    ) 
                    .LeftJoin("Evaluation", "SignOff.EvaluationId", "=", "Evaluation.Id")
                    .LeftJoin("Organization", "Evaluation.OrganizationId", "=", "Organization.Id")
                    .LeftJoin("Employee", "Organization.EmployeeId", "=", "Employee.Id")                    
                    .Where("Evaluation.AdHoc", "0")
                    .Where("Evaluation.IsClosed", "0")
                    .Where(q => q.Where(" exists ", " select EmployeeId from fncGetHREmployees(10) fnc where SignOff.ResponsibleId = fnc.EmployeeId", "", false))
                    .Process(request)
                    .Data();

                return Json(response, JsonRequestBehavior.AllowGet);
            }

rf1234

You could write something like this and replace EXISTS with IN or NOT IN if appropriate

.Where(q => q.Where(" EmployeeId ", 
" select EmployeeId from fncGetHREmployees(10) fnc 
where SignOff.ResponsibleId = fnc.EmployeeId", "IN", false))

I did something like this in PHP and it works fine:

$r  ->where( 'user.id',  
    '( SELECT DISTINCT user.id  
        FROM user, govdept_has_user  
        WHERE govdept_has_user.govdept_id IN  
        ( SELECT DISTINCT govdept_id FROM govdept_has_user     
          WHERE user_id = :id AND role IN ("Administrator", "Principal" ) ) AND  
        govdept_has_user.user_id = user.id  
        ORDER BY user.id ASC  
        )', 'IN', false); 
$r  ->bind( ':id', $_SESSION['masterDataId'] );         

allan

Could you enable the debug mode by adding .Debug( true ) before the Process() call and then show me the JSON return from the server please?

I suspect that the issue is that you need to add parentheses around the select:

"( select ... )"

Allan

Mundaring

Hi,

Allan definitely I was missing the parentheses, I couldn't make the .debug() worked, even though I got a different error. Finally I changed the logic to make it using IN like rf1234 suggested and It worked,

Thanks a lot guys,

Wilson