No documentation on how to use "or" condition or exists in condition in where clause

[Sinha-Ujjawal]

In the following example, I want to write a query that fetches CS and Math dept employees and dept details. There is no clear documentation to do that. Can anybody help me with that?

typeside Ty = literal {
    external_types
        String -> "java.lang.String"
        Int -> "java.lang.Integer"
        Double -> "java.lang.Double"
    external_parsers
        String -> "x => x"
        Int -> "parseInt"
        Double -> "parseDouble"
}

schema C = literal : Ty {
    entities
        Emp Dept
    foreign_keys
        manager : Emp -> Emp
        worksIn : Emp -> Dept
        secretary : Dept -> Emp
    path_equations
        Emp.worksIn = Emp.manager.worksIn
        Dept.secretary.worksIn = Dept
    attributes
        name : Emp -> String
        name : Dept -> String
    # observation_equations
}

query q = literal : C -> C {
    entity
        Emp -> {
            from e: Emp
            where e.worksIn.name = Math # I wan't to write something like e.worksIn.name IN (CS, Math)
            attributes name -> e.name
            foreign_keys
                manager -> {e -> e.manager}
                worksIn -> {d -> e.worksIn}
        }
    entity
        Dept -> {
            from d: Dept
            where d.name = Math
            attributes name -> d.name
            foreign_keys secretary -> {e -> d.secretary}
        }
}

instance I = literal : C {
    generators
        e1 e2 e3 e4: Emp
        d1 d2 d3: Dept      
    equations
        name(d1) = "CS"
        secretary(d1) = e4

        name(d2) = "Math"
        secretary(d2) = e1

        name(d3) = "Biology"
        secretary(d3) = e3

        name(e1) = "e1"
        manager(e1) = e1
        worksIn(e1) = d2

        name(e2) = "e2"
        manager(e2) = e1
        worksIn(e2) = d2

        name(e3) = "e3"
        manager(e3) = e3
        worksIn(e3) = d3

        name(e4) = "e4"
        manager(e4) = e4
        worksIn(e4) = d1
}

instance J = eval q I

[Sinha-Ujjawal]

Never mind, I defined some external functions to do what I wanted-

typeside Ty = literal {
    external_types
        String -> "java.lang.String"
        Int -> "java.lang.Integer"
        Double -> "java.lang.Double"
        Bool -> "java.lang.Boolean"
    external_parsers
        String -> "x => x"
        Int -> "parseInt"
        Double -> "parseDouble"
        Bool -> "java.lang.Boolean.parseBoolean"
    external_functions
        isEq : String, String -> Bool = "(x, y) => x === y"
        or : Bool, Bool -> Bool = "(x, y) => x || y"
        and : Bool, Bool -> Bool = "(x, y) => x && y"
        isCsOrMath: String -> Bool = "x => x === 'CS' || x === 'Math'"
}

schema C = literal : Ty {
    entities
        Emp Dept
    foreign_keys
        manager : Emp -> Emp
        worksIn : Emp -> Dept
        secretary : Dept -> Emp
    path_equations
        Emp.worksIn = Emp.manager.worksIn
        Dept.secretary.worksIn = Dept
    attributes
        name : Emp -> String
        name : Dept -> String
    # observation_equations
}

query q = literal : C -> C {
    entity
        Emp -> {
            from e: Emp
            where isCsOrMath(e.worksIn.name) = true
            attributes name -> e.name
            foreign_keys
                manager -> {e -> e.manager}
                worksIn -> {d -> e.worksIn}
        }
    entity
        Dept -> {
            from d: Dept
            where isCsOrMath(d.name) = true
            attributes name -> d.name
            foreign_keys secretary -> {e -> d.secretary}
        }
}

instance I = literal : C {
    generators
        e1 e2 e3 e4: Emp
        d1 d2 d3: Dept      
    equations
        name(d1) = "CS"
        secretary(d1) = e4

        name(d2) = "Math"
        secretary(d2) = e1

        name(d3) = "Biology"
        secretary(d3) = e3

        name(e1) = "e1"
        manager(e1) = e1
        worksIn(e1) = d2

        name(e2) = "e2"
        manager(e2) = e1
        worksIn(e2) = d2

        name(e3) = "e3"
        manager(e3) = e3
        worksIn(e3) = d3

        name(e4) = "e4"
        manager(e4) = e4
        worksIn(e4) = d1
}

instance J = eval q I

Are there any better collection functions that I can use in CQL?

[wisnesky]

You are indeed using CQL as intended! CQL’s notion of query is fundamentally conjunctive; however, because of the typesides, it is possible to obtain disjunctive behavior. There’s no standard library (for automated theorem proving reasons, user-defined functions are ideally minimal), but it is possible to use any graalVM language (provided graalVM is hooked up to the JVM and various CQL options are set) for external functions.

On Aug 7, 2021, at 7:43 AM, Sinha, Ujjawal @.***> wrote:

Never mind, I defined some external functions to do what I wanted-

typeside Ty = literal { external_types String -> "java.lang.String" Int -> "java.lang.Integer" Double -> "java.lang.Double" Bool -> "java.lang.Boolean" external_parsers String -> "x => x" Int -> "parseInt" Double -> "parseDouble" Bool -> "java.lang.Boolean.parseBoolean" external_functions isEq : String, String -> Bool = "(x, y) => x === y" or : Bool, Bool -> Bool = "(x, y) => x || y" and : Bool, Bool -> Bool = "(x, y) => x && y" isCsOrMath: String -> Bool = "x => x === 'CS' || x === 'Math'" }

schema C = literal : Ty { entities Emp Dept foreign_keys manager : Emp -> Emp worksIn : Emp -> Dept secretary : Dept -> Emp path_equations Emp.worksIn = Emp.manager.worksIn Dept.secretary.worksIn = Dept attributes name : Emp -> String name : Dept -> String

observation_equations

}

query q = literal : C -> C { entity Emp -> { from e: Emp where isCsOrMath(e.worksIn.name) = true attributes name -> e.name foreign_keys manager -> {e -> e.manager} worksIn -> {d -> e.worksIn} } entity Dept -> { from d: Dept where isCsOrMath(d.name) = true attributes name -> d.name foreign_keys secretary -> {e -> d.secretary} } }

instance I = literal : C { generators e1 e2 e3 e4: Emp d1 d2 d3: Dept
equations name(d1) = "CS" secretary(d1) = e4

  name(d2) = "Math"
  secretary(d2) = e1

  name(d3) = "Biology"
  secretary(d3) = e3

  name(e1) = "e1"
  manager(e1) = e1
  worksIn(e1) = d2

  name(e2) = "e2"
  manager(e2) = e1
  worksIn(e2) = d2

  name(e3) = "e3"
  manager(e3) = e3
  worksIn(e3) = d3

  name(e4) = "e4"
  manager(e4) = e4
  worksIn(e4) = d1

}

instance J = eval q I Are there any better collection functions that I can use in CQL?

— You are receiving this because you are subscribed to this thread. Reply to this email directly, view it on GitHub https://github.com/CategoricalData/CQL/issues/70#issuecomment-894663433, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA2QKN6GVT7P4OVU4XIKJSDT3VA7VANCNFSM5BXN2DYQ. Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email.

[Sinha-Ujjawal]

One more question, can I create external types for more higher level data types like Sets, Array, Map etc.?

[wisnesky]

Absolutely- any java type can be used, including collection types.

If your goal is to aggregate across rows, you should check out the built-in Aggregation example, which sums up employee costs per department.

On Aug 7, 2021, at 8:03 PM, Sinha, Ujjawal @.***> wrote:

One more question, can I create external types for more higher level data types like Sets, Array, Map etc.?

— You are receiving this because you commented. Reply to this email directly, view it on GitHub https://github.com/CategoricalData/CQL/issues/70#issuecomment-894735398, or unsubscribe https://github.com/notifications/unsubscribe-auth/AA2QKN6PTWJXZWJRGBRIMYTT3XXY5ANCNFSM5BXN2DYQ. Triage notifications on the go with GitHub Mobile for iOS https://apps.apple.com/app/apple-store/id1477376905?ct=notification-email&mt=8&pt=524675 or Android https://play.google.com/store/apps/details?id=com.github.android&utm_campaign=notification-email.

[Sinha-Ujjawal]

Awesome 😎 thx for your answer, highly appreciated 👍