Question 418832 in Laravel Question posted in
The official Laravel Documentation can be found here.

Ambiguous Column Error When Filtering Leads in Laravel Query Body

I’m working on a Laravel application where I need to apply filters on leads based on attributes from the associated company_users table. I’ve attempted to implement a feature that filters leads by lead_owner based on text input that matches the start, end, contains, or doesn’t contain specific text in the company_users.name field.

I Get the all owner ids but when I apply it on query the query doesn’t giving result.

I try to see the sql code of this and I apply the code on MySQL and I get the result but I view I didn’t get the result.

<?php
if ($request->filled('lead_owner') && $request->filled('lead_owner_text')) {
    $filterType = $request->input('lead_owner');
    $ownerText = $request->input('lead_owner_text');

    if (is_array($filterType)) {
        $ownerIds = [];

        foreach ($filterType as $type) {
            if ($type === 'startWith' && !empty($ownerText)) {
                $ownerIds = array_merge($ownerIds, CompanyUsers::where('companyId', $user->companyId)
                    ->whereRaw("LEFT(name, ?) = ?", [strlen($ownerText), $ownerText])
                    ->pluck('id')->toArray());
            } elseif ($type === 'endWith' && !empty($ownerText)) {
                $ownerIds = array_merge($ownerIds, CompanyUsers::where('companyId', $user->companyId)
                    ->whereRaw("RIGHT(name, ?) = ?", [strlen($ownerText), $ownerText])
                    ->pluck('id')->toArray());
            } elseif ($type === 'contain' && !empty($ownerText)) {
                $ownerIds = array_merge($ownerIds, CompanyUsers::where('companyId', $user->companyId)
                    ->where('name', 'like', "%{$ownerText}%")
                    ->pluck('id')->toArray());
            } elseif ($type === 'doesntContain' && !empty($ownerText)) {
                $ownerIds = array_merge($ownerIds, CompanyUsers::where('companyId', $user->companyId)
                    ->where('name', 'not like', "%{$ownerText}%")
                    ->pluck('id')->toArray());
            }
        }

        if (!empty($ownerIds)) {
            $query->whereIn('lead_owner_id', $ownerIds);
        }
    }
}

this code doesn’t work :

$query->whereIn('lead_owner_id', $ownerIds);

I need to get the result and make the query store the result of leads and display it on the view.

Tags:

2

Answers


  1. 0

    Because there’s is no query instance declared above.So $query is an empty variable right?.
    So you can directly call the model and apply where condition.

    ModelName::whereIn('lead_owner_id', $ownerIds)->get();
    Login or Signup to reply.
  2. 0

    In the snippet you provided, a query instance is missing. you queried through the model instance directly but in the last block, you wanted to apply the condition in $query but there is no Query instance. Basically, you have two options. one is to apply the last filter with a model instance or declare a query instance at the beginning of the code.
    Here I am providing a version that shows how the query works and avoids repetition.

    if ($request->filled('lead_owner') && $request->filled('lead_owner_text')) {
    $filterTypes = $request->input('lead_owner');
    $ownerText = $request->input('lead_owner_text');

    if (is_array($filterTypes) && !empty($ownerText)) {
        $query = CompanyUsers::where('companyId', $user->companyId);

        foreach ($filterTypes as $type) {
            if ($type === 'startWith') {
                $query->orWhereRaw("LEFT(name, ?) = ?", [strlen($ownerText), $ownerText]);
            } elseif ($type === 'endWith') {
                $query->orWhereRaw("RIGHT(name, ?) = ?", [strlen($ownerText), $ownerText]);
            } elseif ($type === 'contain') {
                $query->orWhere('name', 'like', "%{$ownerText}%");
            } elseif ($type === 'doesntContain') {
                $query->orWhere('name', 'not like', "%{$ownerText}%");
            }
        }

        $ownerIds = $query->pluck('id')->toArray();

        if (!empty($ownerIds)) {
            $query->whereIn('lead_owner_id', $ownerIds);
        }
    }
}
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search