Btx/Query

Now API parameters can be used with the REST API to filter, sort, paginate results, select fields and filtering relation up to 3 deep relationships. Additionally, specific parameters related to optional features can be used.

NOTE: Only work with SQL database like MySQL, SQLServer, PostgreSQL, SQLite, MariaDB

The contain operator is translated to the dialect of the connection that will run the query, read from the builder itself: PostgreSQL keeps ILIKE (case-insensitive), every other driver gets LIKE. On MySQL, MariaDB and SQL Server LIKE is case-insensitive as long as the column uses a *_ci collation — a *_bin / *_cs column makes it case-sensitive. config/btx.php → database is only a fallback for when the connection cannot be resolved; leave it on auto.

Support Query Filter

Query	Required	Description	Example
_limit	required	limit data result	_limit: 10
_page	required	retrieve data page-n	_page: 1
_sort:[column]:[sort type]	optional	sorting data by column	_sort: id:desc or _sort: id:asc
[column]_is	optional	get data where column value is [value]	id_is: 5
[column]_contain	optional	get data where column value contain [value]	username_contain: admin
[column]_gte	optional	get data where column value greater than or equal with [value]	age_gte: 25
[column]_lte	optional	get data where column value less than or equal with [value]	age_lte: 25
[column]_gt	optional	get data where column value greater than [value]	age_gt: 25
[column]_lt	optional	get data where column value less than [value]	age_lt: 25
[column]_ne	optional	get data where column value not equal with [value]	gender_ne: male
[column]_between	optional	get data where column value between [values]	age_between: 20,30
[column]_null	optional	get data where column is null	address_null
[column]_notnull	optional	get data where column is not null	address_notnull
[column]_in	optional	get data where column value in [values]	age_in: 10,25,30
[column]_notin	optional	get data where column value not in [values]	age_notin: 10,25,30

Query Filter in Relationship Database

Even query can filter data in relationship table up to 3 deep table relationship. Example 2 deep relationship data

graph LR
A[User] -- user has one department --> B[Department]
B[Department] -- department has many division --> C[Division]

and your user model and department model must be:

//user model
class User extends Model
{
   ...
   public function department(){
      return $this->hasOne(Department::class,'id');
   }
   ...
}

//Department model
class Department extends Model
{
   ...
   public function division(){
      return $this->hasMany(Division::class);
   }
   ...
}

so, to filter data using department name or division name from user list you can using parameter with __ (double underscore) like:

Param Key	Description	Example
department__name_is	filter user using department name where department name is [value]	department__name_is:production
department__division__name_is	filter user using division name where division name is [value]	department__division__name_is: it

Example using trait

Import Trait From Btx Package in your controller with use Btx\Query\Traits\QueryFilter;
Implement the trait use QueryFilter
Before query executing use function on trait $this->filter(Model)

Example:

use Btx\Query\Traits\QueryFilter; 
class UserController extends Controller(){
   use QueryFilter;

   public function getUsers(Request $request){
        $users = User::with('department');
        $this->filter($users);
        $users->get();

        return response()->json(['data' => $users]);
   }
   
}

Example using extending model class

Import Btx\Query\Model From Btx Package in your model with override existing Model class
Before query executing use function filter()

Example:

//Model
use Btx\Query\Model;
class User extends Model
{
  public function department(){
    return $this->hasOne(Department::class,'id','department_id');
  }
  ...
}

//Controller
class UserController extends Controller(){

   public function getUsers(Request $request){
        //implement filter function
        $users = User::with('department')->filter()->get();
        return response()->json(['data' => $users]);
   }
   
}