Using rapid CRUD is not enough, we have to provide the ability to search within a model, within a model's custom fields and within a model's descendant relationships.
For these we provide RequestUriToSql or RequestUriToElastic. We understand we need to provide complex search for different types of systems and if we only focus on SQL, we would have to use joins, so we decided against it and require the developers to go a different route, using elasticsearch + elastic search sql plugin. This allows us to perform complex queries without using joins, it's all up to the developer to create a proper structure in elastic to use nested queries.
In order to search by query strings, you need to understand how we use it and its operators.
Parameters are passed in the format field operator value. Valid operators are
Note: We have some inconsistency between SQL and Elastic, we will fix this in version 0.5.3
Multiple fields can be searched by separating them with a
, . You can search a field by several values by separating said values with
| (equivalent to SQL's OR).
Let’s run over a few examples
Custom fields are discussed later on the topic but we allow you to search by them using the param
Models have relationships and if we want to access this data via the query string, just add
?relationship and then the alias of the relationship you want to access.
Any relationship defined on the model can also be queried using the param
_rq_. You can search for N number of relationships.
Note: You need to specify an Alias to the relationship in order to query it
Between: You can either search
¬with that specifies between or > <
- GET - /v1/model?cq=(field1¬value1|value2)
- GET - /v1/model?q=(field1>value1,field1<value2)
- GET - /v1/model?q=(field1:%value)
- GET - /v1/model?q=(field1:value%)
- GET - /v1/model?q=(field1:%value%)
Empty: You can tell the query parser to make sure a field is empty. In the case of integer properties, the query parser will ask the model if the default value for a property is 0. If it is, it will include 0 as an empty value.
- GET - /v1/model?q=(field1:%%)
Not Empty: This is the opposite of the above Empty.
- GET - /v1/model?q=(field1:$$)
Different : You can specify a search where the value is different than
- GET - /v1/model?q=(field1~value)
You can use all the above described feature together in one query.
Just remember to escape any special characters you want to send through a query string to avoid unwanted results.
, is for AND and
; is for OR operators in the query string
We allow you to apply filters directly in the controllers, so for example if you want to always display records where is_delete = 0, you don't have to pass it through the query string.
So on the controller, you can specify your own query string directly in the constructor
onConstruct, via additionalSearchFields.
Note: It doesn't matter if the user now sends ?q=(is_deleted:1), by specifying this search conditions on the construct, you are always overwriting those conditions. But you can still add additional conditions on the query string.
Same as with the query string search, you can filter the results by relationship directly in the controller.
- Specify the key of the array as the name of the Model with its namespace
- Create an array where each filter is another array similar to additional search
In this example you can see when we have to specify multiple conditions on the same Model.
Note: Please try to avoid this as much as possible, but if you don't have a choice, here you go ;)
We can also allow to add joins and custom conditions straight as SQL but using these properties: customTableJoins and customCoditions.
As you can tell, in customTableJoins, you will need to add
, since this string is concatenated to the main SQL we generate, so BE CAREFUL.
For customConditions, we just add any conditions we want, as you can tell, we start with an AND since we are also just adding this to the main generated SQL .
Again, and we can't stress this enough, be careful, you can break your controller SQL if you don't know what you are doing. Also, DO NOT PASS USER INFO TO THESE QUERIES, since we are not escaping this part of the query.
Just as in the query, you can define the custom fields filter in the controller by using additionalCustomSearchFields
You can also overwrite the sort and limit that we get from the query string using
Like we mentioned before, elasticsearch parser works the same way as normal SQL so any of the previous queries will work. What you won't get are searches for relationships and custom fields.
Why? Simple. We can build simpler searches with nested properties instead of relying on DB relationships.
Example of the same query for the previous example now using elastic:
For more information on how to use nested properties read here, and we also provide an elastic search library that will help you build this complex structure by using normal PHP array or your module structure.
The normal response format of our API is the same structure as your model. If you want a list you will get an array of that same model.
We know there are different standards in our industry
- JSON Api
Our current drivers don't support it, but we do plan on adding them in the future. The current format we support is specifically designed to work with our list table (vuetable) component.
- List of elements are inside the data property
- You have the pagination information
If you don't specify format, the structure will be
If we need to limit the amount of records the sent back we just need to add &limit=value to the URL. By default, we will always limit it to 200 and at the moment won't allow you to exceed that total number of records for a specific call, trying to avoid taking down the site or server due to a large request.
If you need pagination, just add &limit=x and &page=y .
response data is simple, just add sort=field|direction
- field → Any of the table fields you want to sort by
- direction → asc (from first to last) or desc (from last to first)
Example: &sort=id|desc or ?sort=id|asc
We allow you to specify the columns.