Be part of JetBrains PHPverse 2026 on June 9 – a free online event bringing PHP devs worldwide together.

princeoo7's avatar

Search across relational table with a custom query builder giving null

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.

0 likes
3 replies
LaryAI's avatar
Level 58

You can use the whereHas method to search across multiple columns. This method allows you to define a relationship and then search for a specific value within that relationship. For example, if you wanted to search for a value in both the po_no and name columns, you could do something like this:

$query->whereHas('purchase_company_details', function ($q) use ($filter) {
    $q->where('po_no', 'like', '%' . $filter['query_1'] . '%')
      ->orWhere('name', 'like', '%' . $filter['query_1'] . '%');
});

This will search for the value in both the po_no and name columns in the purchase_company_details table. You can also use the whereHas method to search across multiple tables.

princeoo7's avatar

@LaryAI Being a AI you tried your best. but this answer is not that close to what I am looking at. As the answer is for a single table but my requirement is of eloquent relation based tables, and its column.

Please or to participate in this conversation.