I have a table purchases and another table purchase_company_details and I want to search for data in get record where either of them have the data. But I am getting null if I give a vary related data to either oi column.
e.g: I am searching data in po_no column in purchase table and name column in purchase_company_details. Now if I search for PO, I get data as something in name column have po value. but if I send PO- I get null data as PO- exist in 'po_nobut not inname ` column.'
below is the log of from DB::getQueryLog()
[2023-02-21 14:20:09] local.DEBUG: array (
0 =>
array (
'query' => 'select count(*) as aggregate from "purchases" where "po_no"::text like ? and exists (select * from "purchase_company_details" where "purchases"."id" = "purchase_company_details"."purchase_id" and "name"::text like ?)',
'bindings' =>
array (
0 => '%PO-07%',
1 => '%PO-07%',
),
'time' => 4.63,
),
)
Below is the query in the URL:
api/v1/purchase?page=1&order_column=id&order_direction=desc&filter_match=or&f[1][column]=po_no&f[1][operator]=contains&f[1][query_1]=PO-&f[2][column]=seller_details.name&f[2][operator]=contains&f[2][query_1]=PO
Query Builder Code:
...
class CustomQueryBuilder
{
protected $defaultFilterMatch = 'and';
public function apply($query, $data)
{
return $query->when(isset($data['f']), function ($query) use ($data) {
foreach ($data['f'] as $filter) {
$this->applyFilter($filter, $query, $data);
}
});
}
protected function applyFilter($filter, $query, $data)
{
$filter['match'] = $data['filter_match'] ?: $this->defaultFilterMatch;
if (strpos($filter['column'], '.') !== false) {
// nested column
list($relation, $relatedColumn) = explode('.', $filter['column']);
$filter['column'] = $relatedColumn;
$filter['match'] = 'and';
$query->whereHas($relation, function ($q) use ($filter) {
$this->{camel_case($filter['operator'])}($filter, $q);
});
} else {
// normal column
$this->{camel_case($filter['operator'])}($filter, $query);
}
}
...
// contains:q1
protected function contains($filter, $query)
{
return $query->where($filter['column'], 'like', '%' . $filter['query_1'] . '%', $filter['match']);
}
...
}
I think this is more than enough information to start with. If I am searching for one param then it works. I want the search field to be able to look in more than one column for a match.