How to add dynamic where clause in laravel using php lighthouse for graphql processing

Question

I have implemented schema for fetching activities from the system and is working perfortly on fetching the whole list. I want to start passing user_id so as to filter based on the user or to filter by top 10 last week activities. My schema is;

The schema for activity.graphql

type ActivityActivity {
 id: ID!
 activity: String!
 user: User @belongsTo
 created_at: String!
 updated_at: String
 deleted_at: String
 createdBy: User @belongsTo
 updatedBy: User @belongsTo
 deletedBy: User @belongsTo
}

extend type Mutation {

createActivityActivity(
    id: ID 
    activity: String!
    user_id: Int!
    source_type: String!
    source_id: Int!
): ActivityActivity @create(model: "Modules\\Activity\\Entities\\Activity")

updateActivityActivity(
        id: ID!  
        activity: String
        user_id: Int 
        source_type: String
        source_id: Int
): ActivityActivity @update(model: "Modules\\Activity\\Entities\\Activity")

deleteActivityActivity(id: [ID!]!): [ActivityActivity!]! @delete(model: "Modules\\Activity\\Entities\\Activity")
}

extend type Query {
    activity_activity: [ActivityActivity!]! @paginate(model: "Modules\\Activity\\Entities\\Activity")
    find_activity_activity(id: Int! @eq): ActivityActivity @find(model: "Modules\\Activity\\Entities\\Activity")
}

extend type User {
activity: [ActivityActivity!]! @hasMany
}

how can I add fetch statements query that have filter or have where clause eg

query {
  activity_activity(count: 10) {
     filters{user_id : 10, created_at:2019-01-01}
     data{
       activity
       created_at,
       user{name,email,created_at}
     }  

  }
}

or add a where clause to the query eg

query {
  activity_activity(count: 10) {
     where{'user_id = 10 AND created_at=2019-01-01'}
     data{
       activity
       created_at,
       user{name,email,created_at}
     }  

  }
}

UPDATE

Suggested Solution is the filter field as follows

extend type Query {
  activity_activity(
    activity: String @where(operator: "like"),
    user_id: Int @eq 
): [ActivityActivity!]! @paginate(model: "Modules\\Activity\\Entities\\Activity")
find_activity_activity(id: Int! @eq): ActivityActivity @find(model: "Modules\\Activity\\Entities\\Activity")

}

I am implementing an ERP and as per system design users should have advanced search that allows them to perform custom filtering eg Not equal(!=), Not in(!), Greater than(>) etc. In current implementation I have no option but to write different filter parameter for each advanced search. Is there a different option?

Answer 1

How I see it is that you have a couple of options.

The easiest option is to shift to a full text search using Laravel Scout, in Lighthouse this can be used with the search directive found in the docs here.

Another option which is also fairly use the whereConstraints directive. This directive basically gives you access to define where part of your query. What that method you can do nested wheres with different AND and OR statements like so

{
  people(
    filter: {
      where: [
        {
          AND: [
            { column: "age", operator: GT value: 37 }
            { column: "type", value: "Actor" }
            {
              OR: [
                { column: "haircolour", value: "red" }
                { column: "height", operator: GTE, value: 150 }
              ]
            }
          ]
        }
      ]
    }
  ) {
    name
  }

So to take your example and build it up using this directive would look something like this

extend type Query {
    activity_activity(
        where: WhereConstraints @whereConstraints
    ): [ActivityActivity!]! @paginate(model: "Modules\\Activity\\Entities\\Activity")
}

input WhereConstraints {
    column: String
    operator: String = EQ
    value: Mixed
    AND: [WhereConstraints!]
    OR: [WhereConstraints!]
    NOT: [WhereConstraints!]
}

scalar Mixed @scalar(class: "MLL\\GraphQLScalars\\Mixed")

You might want to create some enums for column and operator to limit what operations your users can use and what columns they can filter by.